MSSqlHelper

 

 public class MSSqlHelper : IDisposable
    {
        private SqlConnection _con;
        public void Dispose()
        {
            this._con.Dispose();
        }
        public MSSqlHelper(string connName)
        {
            _con = new SqlConnection(ConfigurationManager.ConnectionStrings[connName].ConnectionString);
        }

        #region 增删改查
        public int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            SqlCommand cmd = GetCommand(sql, parameters);
            return cmd.ExecuteNonQuery();
        }

        public object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            SqlCommand cmd = GetCommand(sql, parameters);
            return cmd.ExecuteScalar();
        }

        public DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = GetCommand(sql, parameters);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }

        public SqlDataReader GetSqlDataReader(string sql, params SqlParameter[] parameters)
        {
            SqlCommand cmd = GetCommand(sql, parameters);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        private SqlCommand GetCommand(string commandtext, SqlParameter[] parameters)
        {

            if (this._con.State == System.Data.ConnectionState.Closed)
            {
                this._con.Open();
            }
            else if (this._con.State == System.Data.ConnectionState.Broken)
            {
                this._con.Close();
                this._con.Open();
            }

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = commandtext;
            cmd.Connection = this._con;
            cmd.Transaction = this._tran;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return cmd;
        }
        #endregion

        #region 事务

        private SqlTransaction _tran;
        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTran()
        {
            _tran = this._con.BeginTransaction();
        }
        /// <summary>
        /// 提交事务 
        /// </summary>
        public void CommitTran()
        {
            this._tran.Commit();
        }
        /// <summary>
        /// 回滚事务 
        /// </summary>
        public void RollbackTran()
        {
            this._tran.Rollback();
        }
        #endregion 
    }

 

//调用

 string sql = @"INSERT INTO dbo.Person( Name, Age, gender, Tel )VALUES('lk','12','0','1111222')";
            string sql2 = @"INSERT INTO dbo.Person( Name, Age, gender, Tel )VALUES('xl','12','1','1111222')";
            using (MSSqlHelper db = new MSSqlHelper())
            {
                try
                { 
                    db.BeginTran();
                    db.ExecuteNonQuery(sql); 
                    db.ExecuteNonQuery(sql2);
                    db.CommitTran(); 
                }
                catch
                {
                    db.RollbackTran();
                }
            }