SQLHelper.cs _爱资料

//Copyright(C) 2000-2006 Shixin Corporation
//All rights reserverd
//文件名: SQLHelper.cs
//创建者:
//创建日期: 2006-03-21
//概述: DataAccess层的数据访问Helper文件，模仿自Microsoft DAAB1.0。
//修改人/修改日期：
//开放STR_CONNECTION连接字符串，因为SqlConnection是非托管资源，无法自动回收。

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Collections.Specialized;

using SPS.Common;

namespace SPS.DataAccess
{
/**////

/// SQLHelper 被DataAccess里的任何类调用，执行Insert，Update，SetValid和Select等组成的存储过程
///

    public class SqlHelper
    {
        //缓存SqlParameter数组对象的Hashtable
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

//连接字符串
public static string STR_CONNECTION;

//SqlConnection
//private static SqlConnection conn;

"Public Functions"#region "Public Functions"
/**////

/// 获取SqlConnection
///

        ///
        /// 因为SqlConnection不是托管资源，所以在这里无法回收，所以在这里屏蔽掉
        ///
        /// SqlConnection对象
//        public static SqlConnection GetConnection()
//        {
//            if(conn==null)
//                conn=new SqlConnection(STR_CONNECTION);
//            return conn;
//        }

/**////

/// 执行XXXInsert,XXXUpdate,XXXSetValid类型的存储过程
///

        ///
        /// 参数Hashtable里的Key名需要和存储过程里的参数名保持一致；
        /// 存储过程的参数全部要在Hashtable中存在；
        ///
        /// 由存储过程参数组成的Hashtable
        /// 存储过程名称
        /// Insert情况的PKID、或Update、SetValid情况的改变记录数
        public static int ModifyTable(Hashtable hashtable,string strSPName)
        {
            //获取SQL连接
            //SqlConnection conn=GetConnection();
            using(SqlConnection conn=new SqlConnection(STR_CONNECTION))
            {
                //依据连接字符串和存储过程名称构造出这个存储过程的参数数组
                SqlParameter[] sqlParas=GetSpParameterSet(conn.ConnectionString,strSPName);

                //循环位每个存储参数数组的元素赋值
                for(int i=0,j=sqlParas.Length;i                {
                    string strKeyName=sqlParas[i].ParameterName;

                    //当没有输入参数
                    try
                    {sqlParas[i].Value=hashtable[strKeyName];}
                    catch
                    {sqlParas[i].Value=null;}

                    //如果输入参数是null
                    if(sqlParas[i].Value==null)
                    {
                        switch(sqlParas[i].SqlDbType)
                        {
                            case SqlDbType.Int:
                                sqlParas[i].Value=-1;
                                break;
                            case SqlDbType.Decimal:
                                sqlParas[i].Value=-1;
                                break;
                            default:
                                sqlParas[i].Value=DBNull.Value;
                                break;
                        }
                    }
                }

                //执行存储过程
                SqlHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,strSPName,sqlParas);

                //取出输出参数的值，
                //注意： Insert,Update,SetValid存储过程，只允许第一个参数类型为 out
                return (int)sqlParas[0].Value;
            }
        }

        /**////

/// 执行GetXXX类型的存储过程
///

        ///
        /// 参数Hashtable里的Key名需要和存储过程里的参数名保持一致；
        /// 存储过程的参数全部要在Hashtable中存在；
        ///
        /// 由存储过程参数组成的Hashtable
        /// 存储过程名称
        /// DataSet
        public static DataSet GetDataSet(Hashtable hashtable,string strSPName)
        {
            //获取SQL连接
            //SqlConnection conn=GetConnection();

            using(SqlConnection conn=new SqlConnection(STR_CONNECTION))
            {
                //依据连接字符串和存储过程名称构造出这个存储过程的参数数组
                SqlParameter[] sqlParas=GetSpParameterSet(conn.ConnectionString,strSPName);

                //循环位每个存储参数数组的元素赋值
                for(int i=0,j=sqlParas.Length;i                {
                    string strKeyName=sqlParas[i].ParameterName;

                    //当没有输入参数
                    try
                    {sqlParas[i].Value=hashtable[strKeyName];}
                    catch
                    {sqlParas[i].Value=null;}

                    //如果输入参数是null
                    if(sqlParas[i].Value==null)
                    {
                        switch(sqlParas[i].SqlDbType)
                        {
                            case SqlDbType.Int:
                                sqlParas[i].Value=-1;
                                break;
                            case SqlDbType.Decimal:
                                sqlParas[i].Value=-1;
                                break;
                            default:
                                sqlParas[i].Value=DBNull.Value;
                                break;
                        }
                    }
                }

                //执行存储过程
                DataSet dtReturn=SqlHelper.ExecuteDataset(conn,CommandType.StoredProcedure,strSPName,sqlParas);

                //返回结果集
                return dtReturn;
            }
        }
        #endregion

Private utility methods & constructors#region Private utility methods & constructors

        static SqlHelper()
        {
            NameValueCollection nvc=(NameValueCollection)ConfigurationSettings.GetConfig("Database");
            STR_CONNECTION="server="+ DESEncryptor.DesDecrypt(nvc["Server"])
                +";database="+ DESEncryptor.DesDecrypt(nvc["Database"]) +";uid="
                + DESEncryptor.DesDecrypt(nvc["UID"])
                +";pwd="+ DESEncryptor.DesDecrypt(nvc["PWD"]) +"";
//            STR_CONNECTION="server=.;database=QL_SPS;uid=sa;pwd=sa";
        }

/**////

        /// 把SqlParameter数组赋值给Command
        ///
        /// This behavior will prevent default values from being used, but
        /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
        /// where the user provided no input value.
        ///

        /// 要添加参数的SqlCommand
        /// 被添加的SqlParameter数组
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            foreach (SqlParameter p in commandParameters)
            {
                //check for derived output value with no value assigned
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }

                command.Parameters.Add(p);
            }
        }

/**////

/// 按需创建SqlCommand，并且设定Connection，Transaction，命令类别
///

        /// 要创建的SqlCommand
        /// SQL Server连接
        /// 事务或null值
        /// 命令类别 (stored procedure)
        /// 存储过程名称
        /// 与SqlCommand有关的参数或null值
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            //如果连接没有打开，则打开连接
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

//把数据库连接与SqlCommand关联起来
command.Connection = connection;

//设置存储过程名称
command.CommandText = commandText;

            //如果需要Transaction，则设置Transaction
            if (transaction != null)
            {
                command.Transaction = transaction;
            }

//设置命令类型
command.CommandType = commandType;

            //添加参数
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }

return;
}

/**////

/// 执行一个不返回结果集的存储过程
///

        ///
        /// e.g.:
        /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        ///
        /// SQL server数据库连接
        /// 命令类型 (stored procedure)
        /// 存储过程名
        /// 参数数组
        /// 返回命令所影响的记录数
        private static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            //创建一个SqlCommand
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

            //finally, execute the command.
            int retval = cmd.ExecuteNonQuery();

            // detach the SqlParameters from the command object, so they can be used again.
            cmd.Parameters.Clear();
            return retval;
        }



        /**////

/// 执行一个带参数的存储过程，返回一个结果集
///

        ///
        /// e.g.:
        /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        ///
        /// SQL Server连接
        /// 命令类别(stored procedure)
        /// 存储过程名称
        /// 参数数组
        /// 返回的DataSet
        private static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            //创建一个命令
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

            //创建 DataAdapter 和 DataSet
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            da.Fill(ds);
            cmd.Parameters.Clear();

            //返回结果集
            return ds;
        }

/**////

/// 从存储过程里面，构造出SqlParameter数组
///

        /// SQL Server连接字符串
        /// 存储过程名称
        /// 标志是否把返回值加入SqlParameter数组
        /// SqlParameter数组
        private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {

            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(spName,cn))
            {
                cn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

//要返回的数组从下面而来
SqlCommandBuilder.DeriveParameters(cmd);

                if (!includeReturnValueParameter)
                {
                    //默认SqlParameter数组的第一个元素是存储过程的返回值
                    cmd.Parameters.RemoveAt(0);
                }

SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);

                return discoveredParameters;
            }
        }

        //深拷贝缓存里的参数数组
        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }
            return clonedParameters;
        }

/**////

/// 从存储过程解析出这个存储过程的参数的集合
///

        ///
        /// 首先从数据库查询, 然后缓存起来供以后调用
        ///
        /// SQL server 的 Connection String
        /// 存储过程名称
        /// SqlParameters数组
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

/**////

/// 从存储过程解析出这个存储过程的参数的集合
///

        ///
        /// 首先从数据库查询, 然后缓存起来供以后调用
        ///
        /// SQL server 的 Connection String
        /// 存储过程名称
        /// 标志返回值是否放到返回的参数数组
        /// SqlParameters数组
        private static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            //定义Key
            string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":"");

            SqlParameter[] cachedParameters;

            //依据Key从缓存Hashtable里取出值
            cachedParameters = (SqlParameter[])paramCache[hashKey];

            if (cachedParameters == null)
            {    //如果取出的值是null，则从数据库获取存储过程的所有参数，并且放入缓存
                cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
            }

            return CloneParameters(cachedParameters);
        }
        #endregion
    }
}

项目中一直使用的一个类，觉得还是蛮好用的。
public abstract class SqlHelper {

        public SqlHelper() {
        }

        /**////

/// 连接字符串
///

public static string CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ Environment.CurrentDirectory + @"\SalaryStat.mdb;User ID=admin;Password=;Jet OLEDB:Database Password=tcyald";

/**////

        /// 用于执行一段SQL语句，只有三个最简的必要参数，省去了commandtype。
        /// To excute a SQL statement, which reuturns a integer stand for effect line number.
        /// default Command type is text
        ///

        /// 连接字符串 (Conntection String)
        /// command的字符串 (SQL Statement)
        /// 参数列表 (Paramters)
        /// 返回影响行数 (effect line number)
        public static int ExecuteNonQuery(string connString, string cmdText, params OleDbParameter[] cmdParms) {
            OleDbCommand cmd = new OleDbCommand();

            using (OleDbConnection conn = new OleDbConnection(connString)) {
                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);

                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;

            }
        }

/**////

        /// 用于执行一段SQL语句。
        /// To excute a SQL statement, which reuturns a integer stand for effect line number.
        ///

        /// 连接字符串 (Connection String)
        /// command的字符串 (SQL Statement)
        /// command的类型，具体见：CommandType (Command type)
        /// 参数列表 (Paramters)
        /// 返回影响行数 (effect line number)
        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) {

OleDbCommand cmd = new OleDbCommand();

            using (OleDbConnection conn = new OleDbConnection(connString)) {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

/**////

        /// 用于执行一段SQL语句。传入的值是connection.
        /// To excute a SQL statement, which reuturns a integer stand for effect line number.
        /// a connection is passed in instead of a connection string
        ///

        /// 一个以初始化好的OleDbConnection (a Conncection)
        /// 连接字符串 (Conntection String)
        /// command的字符串 (SQL Statement)
        /// 参数列表 (Paramters)
        /// 返回影响行数 (effect line number)
        public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) {

OleDbCommand cmd = new OleDbCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

/**////

        /// 用于执行一段SQL语句。需要传入一个事务Transaction.
        /// To excute a SQL statement, which reuturns a integer stand for effect line number.
        /// a transaction is reqired
        ///

        /// 一个Trasaction (Trasaction)
        /// command的字符串 (SQL Statement)
        /// command的类型，具体见：CommandType (Command type)
        /// 参数列表 (Paramters)
        /// 返回影响行数 (effect line number)
        public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

/**////

        /// 用于执行一个Select语句返回一个datareader，省略了commandtype参数
        /// To excute a SQL statement, and reuturns a dataReader.
        /// default command type is text
        ///

        /// 连接字符串 (Conntection String)
        /// command的字符串 (SQL Statement)
        /// 参数列表 (Paramters)
        /// datareader
        public static OleDbDataReader ExecuteReader(string connString, string cmdText, params OleDbParameter[] cmdParms) {
            OleDbCommand cmd = new OleDbCommand();
            OleDbConnection conn = new OleDbConnection(connString);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try {
                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);
                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //    cmd.Parameters.Clear();
                return rdr;
            }
            catch {
                conn.Close();
                throw;
            }
        }

/**////

        /// 用于执行一个Select语句返回一个datareader
        /// To excute a SQL statement, and reuturns a dataReader.
        ///

        /// 连接字符串 (Connection String)
        /// command的字符串 (SQL Statement)
        /// command的类型，具体见：CommandType (Command type)
        /// 参数列表 (Paramters)
        /// dataReader
        public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) {
            OleDbCommand cmd = new OleDbCommand();
            OleDbConnection conn = new OleDbConnection(connString);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //    cmd.Parameters.Clear();
                return rdr;
            }
            catch {
                conn.Close();
                throw;
            }
        }

/**////

        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列，省去了commandtype
        /// To excute , a SQL statement, and returns the first column of the first line
        /// Default command type is text
        ///

        /// 连接字符串 (Conntection String)
        /// command的字符串 (SQL Statement)
        /// 参数列表 (Paramters)
        /// the first column of the first line
        public static object ExecuteScalar(string connString, string cmdText, params OleDbParameter[] cmdParms) {
            OleDbCommand cmd = new OleDbCommand();

            using (OleDbConnection conn = new OleDbConnection(connString)) {
                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

/**////

        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列
        /// To excute a SQL statement, and returns the first column of the first line
        ///

        /// 连接字符串 (Connection String)
        /// command的字符串 (SQL Statement)
        /// command的类型，具体见：CommandType (Command type)
        /// 参数列表 (Paramters)
        /// the first column of the first line
        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) {
            OleDbCommand cmd = new OleDbCommand();

            using (OleDbConnection conn = new OleDbConnection(connString)) {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

/**////

        /// 用于读取一个值，查询所返回的是结果集中第一行的第一列
        /// To excute a SQL statement, and returns the first column of the first line
        /// a connection is passed in instead of a connection string
        ///

        /// 一个以初始化好的OleDbConnection (a Conncection)
        /// 连接字符串 (Conntection String)
        /// command的字符串 (SQL Statement)
        /// 参数列表 (Paramters)
        /// the first column of the first line
        public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms) {

            OleDbCommand cmd = new OleDbCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

/**////

/// 在执行SQL语句之前的准备工作
///

        /// command
        /// connection
        /// trasaction
        /// command类型
        /// command字符串
        /// 参数列表
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms) {

if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

            if (cmdParms != null) {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

/**////

/// 根据SQL语句查询返回DataSet
///

        /// 查询的SQL语句
        /// DataSet
        public static DataSet GetDataSet(string SQLString) {
            using (OleDbConnection connection = new OleDbConnection(CONN_STRING)) {
                DataSet ds = new DataSet();
                try {
                    connection.Open();
                    OleDbDataAdapter command = new OleDbDataAdapter(SQLString,connection);
                    command.Fill(ds,"ds");
                }
                catch(System.Data.OleDb.OleDbException ex) {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        /**////

/// 根据SQL语句和查询参数查询返回DataSet
///

        /// 查询的SQL语句
        /// 参数
        /// DataSet
        public static DataSet GetDataSet(string SQLString,params OleDbParameter[] cmdParms) {
            using (OleDbConnection connection = new OleDbConnection(CONN_STRING)) {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, connection, null,CommandType.Text,SQLString, cmdParms);
                using( OleDbDataAdapter da = new OleDbDataAdapter(cmd) ) {
                    DataSet ds = new DataSet();
                    try {
                        da.Fill(ds,"ds");
                        cmd.Parameters.Clear();
                    }
                    catch(System.Data.OleDb.OleDbException ex) {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
    }

<> <>

SQLHelper.cs

相关文章

栏目导航

最新文章

点击排行

推荐文章

打赏