.net DBHelper

  DBHelper.cs是程序来连接数据的，也是一个程序必不可少的一个类(本人道行浅，目前这样认为)。由于一个程序基本写一次，容易忘记。所有写在这里备注

 

 

首先是引用

 

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

 

然后代码：

（

conn.Close();
conn.Dispose();
cmd.Dispose();
这几个为了防患于未然也加上去了

）

  public class DBHelper
    {
        private static readonly string strconnection = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
        private static SqlConnection conn = new SqlConnection(strconnection);
        //创建给sqlreadr使用
        public static SqlConnection GetConnection() { return conn; }
        /// <summary>
        /// 打开数据库链接
        /// </summary>
        public static void OpenSqlConnection()
        {
            if (GetConnection().State == ConnectionState.Closed) { DBHelper.GetConnection().Open(); }
        }
        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public static void ClosSqlConnection()
        {
            if (GetConnection().State == ConnectionState.Open) { DBHelper.GetConnection().Close(); }
        }


        /// <summary>
        /// 基础ExecuteNonQuery 方法
        /// </summary>
        /// <param name="SQLString">执行的sql语句</param>
        /// <param name="type">是否执行存储过程·</param>
        /// <param name="prep">存储过程参数</param>
        /// <returns></returns>
        private static int ExecuteNonQuery(string SQLString, CommandType type, SqlParameter[] prep)
        {
            using (SqlConnection conn = new SqlConnection(strconnection))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, conn))
                {
                    int r = 0;
                    try
                    {
                        conn.Open();
                        cmd.CommandType = type;
                        if (prep != null && prep.Length > 0)
                        {
                            foreach (SqlParameter p in prep)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        r = cmd.ExecuteNonQuery();
                    }
                    catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                        cmd.Dispose();
                    }
                    return r;
                }
            }

        }

        /// <summary>
        /// 基础datatable
        /// </summary>
        /// <param name="SQLString">执行的sql语句</param>
        /// <param name="type">是否执行存储过程·</param>
        /// <param name="prep">存储过程参数</param>
        /// <returns></returns>
        private static DataTable GetDataTable( string SQLString, CommandType type, SqlParameter[] prep)
        {
            using (SqlConnection conn = new SqlConnection(strconnection))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, conn))
                {
                    SqlDataAdapter dap = null;
                    DataTable dt = null;
                    try
                    {
                        cmd.CommandType = type;
                        if (prep != null && prep.Length > 0)
                        {
                            foreach (SqlParameter p in prep)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        dap = new SqlDataAdapter(cmd);
                        dt = new DataTable();
                        dap.Fill(dt);
                    }
                    catch (Exception ex) { }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                        cmd.Dispose();
                    }
                    return dt;
                }
            }
        }

        /// <summary>
        /// 基础SqlDataReader
        /// </summary>
        /// <param name="SQLString">执行的sql语句</param>
        /// <param name="type">是否执行存储过程·</param>
        /// <param name="prep">存储过程参数</param>
        /// <returns></returns>
        private static SqlDataReader GetList(string SQLString, CommandType type, SqlParameter[] prep)
        {
            SqlCommand cmd = null;
            SqlDataReader dr = null;
            cmd = new SqlCommand(SQLString, conn);
            if (prep != null && prep.Length > 0)
            {
                cmd.Parameters.AddRange(prep);
            }
            cmd.CommandType = type;
            try { dr = cmd.ExecuteReader(); }
            catch (Exception ex) { }
            return dr;
        }


        //调用ExecuteNonQuery 方法
        public static int ExecuteNonQuery(string SQLString, SqlParameter[] prep) {
            return ExecuteNonQuery(SQLString, CommandType.StoredProcedure, prep);
        }
        public static int ExecuteNonQuery(string SQLString) {
            return ExecuteNonQuery(SQLString, CommandType.Text, null);
        }
        //调用GetDataTable 方法
        public static DataTable GetDataTable(string SQLString, SqlParameter[] prep) {
            return GetDataTable(SQLString, CommandType.StoredProcedure, prep);
        }
        public static DataTable GetDataTable(string SQLString)
        {
            return GetDataTable(SQLString, CommandType.Text, null);
        }
        //调用SqlDataReader  调用此方法需要先打开conn  使用完毕之后关闭conn
        public static SqlDataReader GetList(string SQLString, SqlParameter[] prep)
        {
            return GetList(SQLString, CommandType.StoredProcedure, prep);
        }
        public static SqlDataReader GetList(string SQLString)
        {
            return GetList(SQLString, CommandType.Text, null);
        }


    }