SQLHelper.cs的经典代码

╰+哭是因爲堅強的太久メ 2022-08-23 12:55 148阅读 0赞

本文转自[http://blog.csdn.net/ccnuxhb/article/details/3933493][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;   
            }   
        }   
    }

[http_blog.csdn.net_ccnuxhb_article_details_3933493]: http://http://blog.csdn.net/ccnuxhb/article/details/3933493