100分请教using sqlconnection的问题

Question

using(SqlConnection conn=new SqlConnection(connectionString))
{
   //do something
}这样会调用conn.dispose()，但是有个问题，需要对连接字符串重新赋值才能再次使用conn.open()请问有没有比较好的使用方法，包括要考虑发生数据库操作异常的情况，最好有代码和说明，谢谢！

Answer 1

string SqlStr = ;
        SqlConnection ThisConn = new SqlConnection(SqlStr);
tyr
{
        ThisConn.Open();
}
cath
{
}
        SqlTransaction TheTran = ThisConn.BeginTransaction();
        SqlCommand cmd = new SqlCommand(sqlcom, ThisConn);
        cmd.Transaction = TheTran;
        try
        {
            cmd.CommandTimeout = 600;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            TheTran.Commit();
            return ds;
        }
        catch (SqlException e)
        {
            try
            {
                TheTran.Rollback();
            }
            catch (Exception ex)
            {
                String errStrx = ex.Message.ToString();                
//异常处理
                return null;
            }
            String errStr = e.Message.ToString().Replace("\n", "").Replace("\r", "");
            //在这里异常处理
            return null;
        }
        finally
        {
            if (ThisConn != null)
            {
                ThisConn.Close();
            }
        }
这是我自己的,应该没有问题,用过好多次了

Answer 2

sqlhelper讲解的就很详细，使用事务处理
错误捕捉可用log4net记录或用自定义异常处理类        private SqlConnection _m_conn;
        private SqlConnection m_conn
        {
            set
            {
                this._m_conn = value;
            }
            get
            {
                return this._m_conn;
            }
        }
        private SqlTransaction _m_trans;
        private SqlTransaction m_trans
        {
            set
            {
                this._m_trans = value;
            }
            get
            {
                return this._m_trans;
            }
        }
 public  DataSet ExecuteDataSet(string strSql)
        {
            DataSet ds= null;
            SqlCommand selectCommand = null;
            try
            {
                if ((this.m_trans != null) && (this.m_trans.Connection != null))
                {
                    selectCommand = new SqlCommand(strSql, this.m_conn, this.m_trans);
                }
                else
                {
                    selectCommand = new SqlCommand(strSql, this.m_conn);
                }
                if (this.m_conn.State != ConnectionState.Open)
                {
                    this.m_conn.Open();
                }
                using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommand))
                {
                    dataSet = new DataSet();
                    adapter.Fill(ds);
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if (selectCommand != null)
                {
                    selectCommand.Dispose();
                }
                
            }
            return set;
        }
  public  void ExecuteNonQuery(CommandType cmdType, string cmdText, string[] parmsName, ref object[] parmsValue, object[] parmsDirection)
        {
            try
            {
                using (SqlCommand command = this.GetSqlCommand(cmdType, cmdText, parmsName, parmsValue, parmsDirection))
                {
                    command.Transaction = this.m_trans;
                    command.ExecuteNonQuery();
                    for (int i = 0; i < command.Parameters.Count; i++)
                    {
                        SqlParameter parameter = command.Parameters[i];
                        if (parameter.Direction == ParameterDirection.Output)
                        {
                            parmsValue[i] = parameter.Value;
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }
参考

Answer 3

using 表示只要有就不创建，只要有其他地方要用就不销毁

Answer 4

用orm 配置文件把连接都写好 一个个调用