DBHelper

public class DBHelper

{

       //using System.Data.SqlClient;需要导入相应的命名空间

//-------不带参数---------------------------------------------------------------------------------------------------
        /// <summary>
        /// 创建数据库连接的方法
        /// </summary>
        /// <returns>SqlConnection</returns>
        public static SqlConnection createConn()
        {
            string conn_str = ConfigurationManager.ConnectionStrings["MyApplication"].ConnectionString;
            SqlConnection conn = new SqlConnection(conn_str);
            return conn;
        }

        /// <summary>
        /// 获取执行查询获取DataReader的方法
        /// </summary>
        /// <param name="cmdText">sql字符串或存储过程名称</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader getDataReader(string cmdText)
        {
            SqlConnection conn = DBHelper.createConn();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return sdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 执行sql语句的方法
        /// </summary>
        /// <param name="cmdText">sql字符串或存储过程名称</param>
        public static int executeSql(string cmdText)
        {
            SqlConnection conn = DBHelper.createConn();
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            int temp = cmd.ExecuteNonQuery();
            conn.Close();
            return temp;
        }

        /// <summary>
        /// 执行sql语句获得第一行第一列的字符串
        /// </summary>
        /// <param name="cmdText">sql字符串或存储过程名称</param>
        /// <returns>查询的结果</returns>
        public static string executeScalarStr(string cmdText)
        {
            SqlConnection conn = DBHelper.createConn();
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            string temp = Convert.ToString(cmd.ExecuteScalar());
            conn.Close();
            return temp;
        }

        /// <summary>
        /// 执行sql语句获得第一行第一列的数字,用来获得count()结果
        /// </summary>
        /// <param name="cmdText">sql字符串或存储过程名称</param>
        /// <returns>查询的结果数字</returns>
        public static int executeScalar(string cmdText)
        {
            SqlConnection conn = DBHelper.createConn();
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            int temp = Convert.ToInt32(cmd.ExecuteScalar());
            conn.Close();
            return temp;
        }

        /// <summary>
        /// 无参数的存储过程的处理方法。
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="cmd">根据数据库连接和存储过程创建的SqlCommand对象</param>
        /// <param name="cmdText">存储过程名称</param>
        private static void PrepareCommandPro(SqlConnection conn, SqlCommand cmd, string cmdText)
        {
            //判断连接的状态。如果是关闭状态，则打开
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            //cmd属性赋值
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.StoredProcedure;
        }

        /// <summary>
        /// 返回一个数据集
        /// </summary>
        /// <param name="cmdStr">sql语句</param>
        /// <returns></returns>
        public static DataSet getDataSet(string cmdStr)
        {
            SqlConnection con = DBHelper.createConn();
            try
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmdStr, con);
                DataSet ds = new DataSet();
                sda.Fill(ds, "table");
                return ds;
            }
            catch
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 无参数的存储过程的执行
        /// </summary>
        /// <param name="cmdStr">存储过程</param>
        /// <returns>一个数据集</returns>
        public static DataSet getDataSetPro(string cmdStr)
        {
            SqlConnection con = DBHelper.createConn();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommandPro(con, cmd, cmdStr);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds, "table");
                cmd.Parameters.Clear();
                return ds;
            }
            catch
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 获取执行查询获取DataReader的方法
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns></returns>
        public static SqlDataReader getDataReader(SqlCommand cmd)
        {
            SqlConnection conn = DBHelper.createConn();
            cmd.Connection = conn;
            conn.Open();
            SqlDataReader sda;
            sda = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            conn.Close();
            return sda;
        }

 //--------------带参数------------------------------------------------------------------

        /// <summary>
        /// 执行sql语句的方法
        /// </summary>
        /// <param name="cmdText">sql字符串</param>
        /// <param name="cmdParms">sql字符串中的参数</param>
        public static int executeSql(string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = DBHelper.createConn();
            PrepareCommand(conn, cmd, cmdText, cmdParms);
            int temp = cmd.ExecuteNonQuery();
            conn.Close();
            cmd.Parameters.Clear();
            return temp;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="cmdParms">存储过程中的参数</param>
        public static int executePro(string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = DBHelper.createConn();
            PrepareCommandPro(conn, cmd, cmdText, cmdParms);
            int temp = cmd.ExecuteNonQuery();
            conn.Close();
            cmd.Parameters.Clear();
            return temp;
        }

        /// <summary>
        /// 执行sql语句获得第一行第一列的字符串
        /// </summary>
        /// <param name="cmdText">sql字符串或存储过程名称</param>
        /// <param name="cmdParms">sql字符串中的参数</param>
        /// <returns>查询的结果</returns>
        public static string executeScalarStr(string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = DBHelper.createConn();
            PrepareCommand(conn, cmd, cmdText, cmdParms);
            string result = Convert.ToString(cmd.ExecuteScalar());
            conn.Close();
            cmd.Parameters.Clear();
            return result;
        }

        /// <summary>
        /// 执行sql语句获得第一行第一列的数字,用来获得count()结果
        /// </summary>
        /// <param name="cmdText">sql字符串或存储过程名称</param>
        /// <param name="cmdParms">sql字符串中的参数</param>
        /// <returns>查询的结果数字</returns>
        public static int executeScalar(string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = DBHelper.createConn();
            PrepareCommand(conn, cmd, cmdText, cmdParms);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            conn.Close();
            cmd.Parameters.Clear();
            return result;
        }

        /// <summary>
        /// 返回一个数据集
        /// </summary>
        /// <param name="cmdStr">存储过程</param>
        /// <param name="cmdParms">存储过程中的参数</param>
        /// <returns></returns>
        public static DataSet getDataSetPro(string cmdStr, params SqlParameter[] cmdParms)
        {
            SqlConnection con = DBHelper.createConn();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommandPro(con, cmd, cmdStr, cmdParms);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds, "table");
                cmd.Parameters.Clear();
                return ds;
            }
            catch
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 返回一个数据集
        /// </summary>
        /// <param name="cmdStr">sql语句</param>
        /// <param name="cmdParms">sql语句中的参数</param>
        /// <returns></returns>
        public static DataSet getDataSet(string cmdStr, params SqlParameter[] cmdParms)
        {
            SqlConnection con = DBHelper.createConn();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(con, cmd, cmdStr, cmdParms);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds, "table");
                cmd.Parameters.Clear();
                return ds;
            }
            catch
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 处理数据库连接以及参数的方法
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="cmd">根据数据库连接和sql语句创建的SqlCommand对象</param>
        /// <param name="cmdText">sql语句名称</param>
        /// <param name="cmdParms">sql语句中的参数</param>
        private static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
        {
            //判断连接的状态。如果是关闭状态，则打开
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            //cmd属性赋值
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;

            //添加cmd需要的参数
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 处理数据库连接以及参数的方法
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="cmd">根据数据库连接和存储过程创建的SqlCommand对象</param>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="cmdParms">存储过程中的参数</param>
        private static void PrepareCommandPro(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
        {
            //判断连接的状态。如果是关闭状态，则打开
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            //cmd属性赋值
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.StoredProcedure;

            //添加cmd需要的存储过程参数
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 获取执行查询获取DataReader的方法
        /// </summary>
        /// <param name="cmdText">sql字符串或存储过程名称</param>
        /// <param name="cmdParms">sql字符串中的参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader getDataReader(string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = DBHelper.createConn();
            try
            {
                PrepareCommand(conn, cmd, cmdText, cmdParms);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return sdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

}