SQLHelper.cs的经典代码收集

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace HelloWinForm.DBUtility
{
    class SQLHelper
    {
        #region 通用方法
        // 数据连接池
        private SqlConnection con;
        /// <summary>
        /// 返回数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public static String GetSqlConnection()
        {
            String conn = ConfigurationManager.AppSettings["connectionString"].ToString();
            return conn;
        }
        #endregion
        #region 执行sql字符串
        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static int ExecuteSql(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return 1;
            }
        }
        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <param name="param">参数对象数组</param>
        /// <returns></returns>
        public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return 1;
            }
        }
        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection
            }
            catch //(Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回数据表
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <returns></returns>
        public static DataTable ExecuteDt(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回DataSet
        /// </summary>
        /// <param name="Sqlstr">SQL语句</param>
        /// <returns></returns>
        public static DataSet ExecuteDs(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        #endregion
        #region 操作存储过程
        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <returns>存储过程的返回值</returns>
        public int RunProc(string procName)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            cmd.ExecuteNonQuery();
            this.Close();
            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)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();
            this.Close();
            return (int)cmd.Parameters[0].Value;
        }
        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="dataReader">结果集</param>
        public void RunProc(string procName, out SqlDataReader dataReader)
        {
            SqlCommand 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)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 创建Command对象用于访问存储过程
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <returns>Command对象</returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            // 确定连接是打开的
            Open();
            //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
            SqlCommand cmd = new SqlCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            // 添加存储过程的输入参数列表
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                    cmd.Parameters.Add(parameter);
            }
            // 返回Command对象
            return cmd;
        }
        /// <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>
        /// 创建存储过程参数
        /// </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;
        }
        #endregion
        #region 数据库连接和关闭
        /// <summary>
        /// 打开连接池
        /// </summary>
        private void Open()
        {
            // 打开连接池
            if (con == null)
            {
                //这里不仅需要using System.Configuration;还要在引用目录里添加
                con = new SqlConnection(GetSqlConnection());
                con.Open();
            }
        }
        /// <summary>
        /// 关闭连接池
        /// </summary>
        public void Close()
        {
            if (con != null)
                con.Close();
        }
        /// <summary>
        /// 释放连接池
        /// </summary>
        public void Dispose()
        {
            // 确定连接已关闭
            if (con != null)
            {
                con.Dispose();
                con = null;
            }
        }
        #endregion
    }
}

using System; using System.Collections.Generic; using System.Text; using System.Collections; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace HelloWinForm.DBUtility { class SQLHelper { #region 通用方法 // 数据连接池 private SqlConnection con; /// <summary> /// 返回数据库连接字符串 /// </summary> /// <returns></returns> public static String GetSqlConnection() { String conn = ConfigurationManager.AppSettings["connectionString"].ToString(); return conn; } #endregion #region 执行sql字符串 /// <summary> /// 执行不带参数的SQL语句 /// </summary> /// <param name="Sqlstr"></param> /// <returns></returns> public static int ExecuteSql(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = Sqlstr; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); return 1; } } /// <summary> /// 执行带参数的SQL语句 /// </summary> /// <param name="Sqlstr">SQL语句</param> /// <param name="param">参数对象数组</param> /// <returns></returns> public static int ExecuteSql(String Sqlstr, SqlParameter[] param) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = Sqlstr; cmd.Parameters.AddRange(param); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); return 1; } } /// <summary> /// 返回DataReader /// </summary> /// <param name="Sqlstr"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(String Sqlstr) { String ConnStr = GetSqlConnection(); SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的 try { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = Sqlstr; conn.Open(); return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection } catch //(Exception ex) { return null; } } /// <summary> /// 执行SQL语句并返回数据表 /// </summary> /// <param name="Sqlstr">SQL语句</param> /// <returns></returns> public static DataTable ExecuteDt(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn); DataTable dt = new DataTable(); conn.Open(); da.Fill(dt); conn.Close(); return dt; } } /// <summary> /// 执行SQL语句并返回DataSet /// </summary> /// <param name="Sqlstr">SQL语句</param> /// <returns></returns> public static DataSet ExecuteDs(String Sqlstr) { String ConnStr = GetSqlConnection(); using (SqlConnection conn = new SqlConnection(ConnStr)) { SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn); DataSet ds = new DataSet(); conn.Open(); da.Fill(ds); conn.Close(); return ds; } } #endregion #region 操作存储过程 /// <summary> /// 运行存储过程(已重载) /// </summary> /// <param name="procName">存储过程的名字</param> /// <returns>存储过程的返回值</returns> public int RunProc(string procName) { SqlCommand cmd = CreateCommand(procName, null); cmd.ExecuteNonQuery(); this.Close(); 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) { SqlCommand cmd = CreateCommand(procName, prams); cmd.ExecuteNonQuery(); this.Close(); return (int)cmd.Parameters[0].Value; } /// <summary> /// 运行存储过程(已重载) /// </summary> /// <param name="procName">存储过程的名字</param> /// <param name="dataReader">结果集</param> public void RunProc(string procName, out SqlDataReader dataReader) { SqlCommand 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) { SqlCommand cmd = CreateCommand(procName, prams); dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } /// <summary> /// 创建Command对象用于访问存储过程 /// </summary> /// <param name="procName">存储过程的名字</param> /// <param name="prams">存储过程的输入参数列表</param> /// <returns>Command对象</returns> private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { // 确定连接是打开的 Open(); //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) ); SqlCommand cmd = new SqlCommand(procName, con); cmd.CommandType = CommandType.StoredProcedure; // 添加存储过程的输入参数列表 if (prams != null) { foreach (SqlParameter parameter in prams) cmd.Parameters.Add(parameter); } // 返回Command对象 return cmd; } /// <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> /// 创建存储过程参数 /// </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; } #endregion #region 数据库连接和关闭 /// <summary> /// 打开连接池 /// </summary> private void Open() { // 打开连接池 if (con == null) { //这里不仅需要using System.Configuration;还要在引用目录里添加 con = new SqlConnection(GetSqlConnection()); con.Open(); } } /// <summary> /// 关闭连接池 /// </summary> public void Close() { if (con != null) con.Close(); } /// <summary> /// 释放连接池 /// </summary> public void Dispose() { // 确定连接已关闭 if (con != null) { con.Dispose(); con = null; } } #endregion } } 简单用一下:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using HelloWinForm.DBUtility;
namespace HelloWinForm.DAL
{
    class Student
    {
        public string test()
        {
            string str = "";
            SqlDataReader dr = SQLHelper.ExecuteReader("select * from Student");
            while (dr.Read())
            {
                str += dr["StudentNO"].ToString();
            }
            dr.Close();
            return str;
        }
    }
}