SQLHelper.cs的经典代码

本文转自http://blog.csdn.net/ccnuxhb/article/details/3933493

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  
    }  
}   
简单用一下:

view plaincopy to clipboardprint?
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;   
        }   
    }   
}