如何在sql连接中使用try / catch? [英] How can I use try/catch with sql connection?
本文介绍了如何在sql连接中使用try / catch?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果没有与服务器连接,如何使用try / catch来处理错误?
这是我的数据库类:
public static string connectionString = ConfigurationManager.ConnectionStrings [ AlimamTreeFinal]。ConnectionString;
public static SqlCommand Intialize( string 查询, params SqlParameter [] prmArray)
{
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(query,cn);
if (!query.Contains( ))
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (prmArray.Length > 0 )
cmd.Parameters.AddRange(prmArray);
cn.Open();
return cmd;
}
public int ExcuteNonQuery(< span class =code-keyword> string query, params SqlParameter [] prmArray)
{
try
{
SqlCommand cmd = Intialize(query,prmArray);
int affectedRows = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return affectedRows;
}
catch (SqlException ex)
{
return ex.Number;
}
}
public object ExcuteScalar( string query, params SqlParameter [] prmArray)
{
try
{
SqlCommand cmd = Intialize(query,prmArray);
object value = cmd.ExecuteScalar();
cmd.Connection.Close();
return value ;
}
catch (SqlException ex)
{
return ex.Number;
}
}
public SqlDataReader ExcuteReader( string query, params SqlParameter [] prmArray)
{
SqlCommand cmd = Intialize(query,prmArray);
SqlDataReader sqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sqlDataReader;
}
public DataTable ExcuteDataTable( string 查询, params SqlParameter [] prmArray)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query,connectionString);
if (!query.Contains( ))
sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (prmArray.Length > 0 )
sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);
DataTable dt = new DataTable();
sqlDataAdapter.Fill(dt);
return dt;
}
public DataSet ExcuteDataSet( string query, params SqlParameter [] prmArray)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter (query,connectionString);
if (!query.Contains( ))
sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (prmArray.Length > 0 )
sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds);
return ds;
}
}
}
解决方案
1.Your初始化
方法应该私有/受保护!
2.您应该在所有公共方法中使用try-catch-finally
块;
3.You必须注意一些可用的资源,比如SqlConnection
和SqlReader
,必须在<$ c $中处理/关闭c>最后阻止,不仅是成功的情况,还有异常的情况。以下是一些改进示例:
public void ExcuteReader( string query, params SqlParameter [] prmArray,UseReaderDelegate UseReader)
{
SQLConnection connection = null ;
SQLDataReader sqlDataReader = null ;
//
try {
SqlCommand cmd = Intialize(query,prmArray);
connection = cmd.Connection; // !!!
sqlDataReader = cmd.ExecuteReader();
UseReader(sqlDataReader);
}
catch (SqlException)
{
// 在此处输入用于管理SQL异常的代码
// ...
}
最后
{
// 处理可能使用的资源!
如果 (sqlDataReader!= null )
sqlDataReader.Close();
//
if (connection!= null )
connection.Close();
}
}
PS:有关异常管理的更多详细信息,您可以阅读我的下一篇文章(即使是示例来自ASP.NET MVC4应用程序规则是相同的):
MVC基本站点:第2步 - 例外管理 [ ^ ]
使用以下代码:
尝试
{
// < span class =code-comment> Code Here
}
catch (例外ee)
{
// 异常句柄块
}
How can I use try/catch with this to handle the error if there is no connection with server?
This is my database class:
public static string connectionString = ConfigurationManager.ConnectionStrings["AlimamTreeFinal"].ConnectionString ;
public static SqlCommand Intialize(string query, params SqlParameter[] prmArray)
{
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(query, cn);
if (!query.Contains(" "))
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (prmArray.Length > 0)
cmd.Parameters.AddRange(prmArray);
cn.Open();
return cmd;
}
public int ExcuteNonQuery(string query, params SqlParameter[] prmArray)
{
try
{
SqlCommand cmd = Intialize(query, prmArray);
int affectedRows = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return affectedRows;
}
catch (SqlException ex)
{
return ex.Number;
}
}
public object ExcuteScalar(string query, params SqlParameter[] prmArray)
{
try
{
SqlCommand cmd = Intialize(query, prmArray);
object value = cmd.ExecuteScalar();
cmd.Connection.Close();
return value;
}
catch (SqlException ex)
{
return ex.Number;
}
}
public SqlDataReader ExcuteReader(string query, params SqlParameter[] prmArray)
{
SqlCommand cmd = Intialize(query, prmArray);
SqlDataReader sqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sqlDataReader;
}
public DataTable ExcuteDataTable(string query, params SqlParameter[] prmArray)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, connectionString);
if (!query.Contains(" "))
sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (prmArray.Length > 0)
sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);
DataTable dt = new DataTable();
sqlDataAdapter.Fill(dt);
return dt;
}
public DataSet ExcuteDataSet(string query, params SqlParameter[] prmArray)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, connectionString);
if (!query.Contains(" "))
sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (prmArray.Length > 0)
sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds);
return ds;
}
}
}
解决方案
1.YourIntialize
method should be madeprivate/protected!
2.You should usetry-catch-finally
block in all your public methods ;
3.You must take care at some disposable resources, likeSqlConnection
andSqlReader
, that must be disposed/closed infinally
block, not only the the case of success but also in the case of exceptions. Here is an example of improvements:
public void ExcuteReader(string query, params SqlParameter[] prmArray, UseReaderDelegate UseReader) { SQLConnection connection = null; SQLDataReader sqlDataReader = null; // try{ SqlCommand cmd = Intialize(query, prmArray); connection = cmd.Connection; //!!! sqlDataReader = cmd.ExecuteReader(); UseReader(sqlDataReader); } catch(SqlException) { //Put here your code for managing the SQL exceptions //... } finally { //Dispose the possible used resources! if(sqlDataReader != null) sqlDataReader.Close(); // if( connection != null) connection.Close(); } }
PS: For more details about exception management you could read my next article (even the examples are from an ASP.NET MVC4 application the rules are the same):
MVC Basic Site: Step 2 - Exceptions Management[^]
Use the code as :
try { // Code Here } catch(Exception ee) { // Exception Handle block }
这篇关于如何在sql连接中使用try / catch?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文