SQLHelper.cs

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;
namespace Operate_DB
{
    /// <summary>
    /// SQLHelper 的摘要说明。
    /// </summary>
    public class SQLHelper
    {
        //数据库连接串
        public static readonly string ConnString = ConfigurationSettings.AppSettings["ConnectionString"];        
        private SqlConnection conn; 
        private SqlCommand cmd;
        private SqlDataAdapter myAdapter;    
        private DataSet ds;        

        public SQLHelper()
        {            
            Open();
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            if (conn == null)
            {
                conn = new SqlConnection(ConnString);
                try
                {
                    conn.Open();
                }
                catch(Exception e )
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Dispose();
            conn = null;
        }

        /// <summary>
        /// 创建命令对象，调用存储过程
        /// </summary>
        /// <param name="procName">存储过程名称.</param>
        /// <param name="prams">存储过程参数.</param>
        /// <returns>返回命令对象.</returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams) 
        {
            // 确保连接是打开的
            Open();
            cmd = new SqlCommand(procName, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            // 给存储过程添加参数
            if (prams != null) 
            {
                foreach (SqlParameter parameter in prams)
                    cmd.Parameters.Add(parameter);
            }
            
            // 返回参数
            cmd.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));

            return cmd;
        }

    
        /// <summary>
        /// 运行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public int RunProc(string procName) 
        {
            cmd = CreateCommand(procName, null);
            cmd.ExecuteNonQuery();    
            
            return (int)cmd.Parameters["ReturnValue"].Value;
        }

        /// <summary>
        /// 运行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称.</param>
        /// <param name="prams">存储过程参数.</param>
        /// <returns>返回值.</returns>
        public int RunProc(string procName, SqlParameter[] prams) 
        {
            cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();        
            
            return (int)cmd.Parameters["ReturnValue"].Value;
        }

        /// <summary>
        /// 运行存储过程
        /// </summary>
        /// <param name="procName">存储过程名称.</param>
        /// <param name="dataReader">返回值.</param>
        public void RunProc(string procName, out SqlDataReader dataReader) 
        {
            cmd = CreateCommand(procName, null);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 运行存储过程
        /// </summary>
        /// <param name="procName">存储过程名称.</param>
        /// <param name="prams">存储过程参数.</param>
        /// <param name="dataReader">返回值.</param>
        public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) 
        {
            cmd = CreateCommand(procName, prams);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }
    
        public SqlDataAdapter GetAdatper(string procName,SqlParameter[] prams)
        {
            cmd = CreateCommand(procName, prams);
            myAdapter = new SqlDataAdapter(cmd);        
            return myAdapter;
        }    
        

        /// <summary>
        /// 生成存储过程参数
        /// </summary>
        /// <param name="ParamName">参数名称.</param>
        /// <param name="DbType">参数类型.</param>
        /// <param name="Size">参数大小.</param>
        /// <param name="Direction">参数方向.</param>
        /// <param name="Value">参数值.</param>
        /// <returns>返回新参数.</returns>
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 
        {
            SqlParameter param;

            if(Size > 0)
                param = new SqlParameter(ParamName, DbType, Size);                
            else
                param = new SqlParameter(ParamName, DbType);

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;

            return param;
        }

        
        /// <summary>
        /// 生成输入参数
        /// </summary>
        /// <param name="ParamName">参数名称.</param>
        /// <param name="DbType">参数类型.</param>
        /// <param name="Size">参数大小.</param>
        /// <param name="Value">参数值.</param>
        /// <returns>返回新参数.</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) 
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }        

        /// <summary>
        /// 生成输出参数.
        /// </summary>
        /// <param name="ParamName">参数名称.</param>
        /// <param name="DbType">参数类型.</param>
        /// <param name="Size">参数大小.</param>
        /// <returns>返回新参数.</returns>
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) 
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }        

        /// <summary>
        /// 执行单条SQL语句
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool ExecSql(string strSql)
        {   
            bool Result = false;
            Open();
            cmd = new SqlCommand(strSql,conn);
            try
            {
                cmd.ExecuteNonQuery();
                Result = true;
            }
            catch
            {
            }
            cmd.Dispose();
            return Result;
        }

        /// <summary>
        /// 执行多条语句
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool ExecSql(string[] strSql)
        {
            bool Result = false;        
            if (strSql != null) 
            {
                Open();
                cmd = new SqlCommand();
                
                SqlTransaction tr ;
                tr = conn.BeginTransaction();
                cmd.Connection = conn;
                cmd.Transaction = tr;                    
                try
                {
                    foreach (string Sql in strSql)
                    {
                        cmd.CommandText = Sql;
                        cmd.ExecuteNonQuery();
                    }
                    tr.Commit();
                    Result = true;
                }
                catch
                {
                    tr.Rollback();
                    Close();    
                    throw;
                }                
            }
            return Result;
        }

        /// <summary>
        /// 执行单条SQL语句，并返回第一行，第一列的值s
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object ExecSqlScalar(string strSql)
        {   
            object Return_Value = new object();
            Open();
            cmd = new SqlCommand(strSql,conn);
            Return_Value = cmd.ExecuteScalar();

            cmd.Dispose();
            return Return_Value;
        }

        /// <summary>
        /// 得到DateSet记录集
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataSet GetDateSet(string strSQL)
        {      
            Open();            
            myAdapter = new SqlDataAdapter(strSQL,conn);
            ds = new DataSet();
            myAdapter.Fill(ds);    
            return ds;
        }
        public SqlDataAdapter GetDataAdapter(string strSQL)
        {      
            Open();            
            myAdapter = new SqlDataAdapter(strSQL,conn);
            
            return myAdapter;
        }

        
        /// <summary>
        /// 得到DataReader
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public SqlDataReader GetReader(string strSQL)
        {            
            cmd = new SqlCommand(strSQL,conn);
            return cmd.ExecuteReader();
        }
        /// <summary>
        /// 得到DataReader关闭数据库连接
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public SqlDataReader GetReaderCloseDb(string strSQL)
        {            
            cmd = new SqlCommand(strSQL,conn);
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// Get Scalar
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public object GetScalar(string strSQL)
        {
            cmd = new SqlCommand(strSQL,conn);
            return cmd.ExecuteScalar();
        }

        
    }
}