SqlHelperExtension数据库操作基类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Reflection;
using System.Resources;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

    public class SqlHelperExtension
    {
        //private String CONFIG_CONNECTION_STRING = "ConnectionString";
        private static SqlConnection SQLCONNECTION;
        private static String CONNECT_FLAG = GetConnectFlag();
        public static String DB_CONN_STRING = GetConnectionString();

        public static void Fill(IDataReader dataReader, DataSet dataSet, string tableName, int from, int count)
        {
            if (tableName == null)
                tableName = "unknownTable";

            if (dataSet.Tables[tableName] == null)
                dataSet.Tables.Add(tableName);

            // Get the DataTable reference
            DataTable fillTable;
            if (tableName == null)
                fillTable = dataSet.Tables[0];
            else
                fillTable = dataSet.Tables[tableName];

            DataRow fillRow;
            string fieldName;
            int recNumber = 0;
            int totalRecords = from + count;
            while (dataReader.Read())
            {
                if (recNumber++ >= from)
                {
                    fillRow = fillTable.NewRow();
                    for (int fieldIdx = 0; fieldIdx < dataReader.FieldCount; fieldIdx++)
                    {
                        fieldName = dataReader.GetName(fieldIdx);
                        if (fillTable.Columns.IndexOf(fieldName) == -1)
                            fillTable.Columns.Add(fieldName, dataReader.GetValue(fieldIdx).GetType());
                        fillRow[fieldName] = dataReader.GetValue(fieldIdx);
                    }
                    fillTable.Rows.Add(fillRow);
                }
                if (count != 0 && totalRecords <= recNumber)
                    break;
            }
            dataSet.AcceptChanges();
        }

        public static SqlConnection GetConnection()
        {
            try
            {
                //0:一直连接
                if (CONNECT_FLAG == "0")
                {
                    //没有创建则重新创建
                    if (SQLCONNECTION == null)
                    {
                        SQLCONNECTION = new SqlConnection(DB_CONN_STRING);
                    }
                    //连接没有打开则重新打开连接
                    if (SQLCONNECTION.State != ConnectionState.Open)
                    {
                        SQLCONNECTION.Open();
                    }
                    return SQLCONNECTION;
                }
                else //1:每次创建新的连接
                {
                    SqlConnection newSqlConnection = new SqlConnection(DB_CONN_STRING);
                    //打开连接
                    newSqlConnection.Open();
                    return newSqlConnection;
                }
            }
            catch (SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static String GetConnectionString()
        {
            String connectionString;
            try
            {
                connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
            }
            catch (Exception e)
            {
                throw new Exception("连接数据库失败", e);
            }
            return connectionString;
        }

        public static String GetConnectFlag()
        {
            String strConnectFlag = ConfigurationManager.AppSettings["CONNECT_FLG"];
            if (strConnectFlag ==null)
            {
                throw new Exception("读取连接类型失败");
            }
            return strConnectFlag;
        }

        public static void CloseConnection(SqlConnection sqlConnection)
        {
            if (CONNECT_FLAG == "1")
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                    sqlConnection = null;
                }
            }
        }

        public static Exception GetApplicationException(SqlException e)
        {
            switch (e.Number)
            {
                //'Case 1231
                //'    Return New ApplicationException(UTL.Common.MSG_HASHTABLE.Item("MSG-SQLERROR-00001").ToString, e)
                //'Case 2627
                //'    Return New ApplicationException(UTL.Common.MSG_HASHTABLE.Item("MSG-SQLERROR-00002").ToString, e)
                //'Case 207
                //'    Return New ApplicationException(UTL.Common.MSG_HASHTABLE.Item("MSG-SQLERROR-00003").ToString, e)
                //'Case 208
                //'    Return New ApplicationException(UTL.Common.MSG_HASHTABLE.Item("MSG-SQLERROR-00004").ToString, e)
                case 1231:
                    return new ApplicationException("服务器连接超时", e);
                case 2627:
                    return new ApplicationException("主键重复", e);
                case 207:
                    return new ApplicationException("列不存在", e);
                case 208:
                    return new ApplicationException("表不存在", e);
                case 53:
                    return new ApplicationException("服务器连接失败", e);
            }
            return e;
        }


    #region "Transaction"
        public static SqlTransaction OpenTransaction()
        {
            try
            {
                SqlConnection sqlconnection;
                SqlTransaction sqlTransaction;
                sqlconnection = GetConnection();
                sqlTransaction = sqlconnection.BeginTransaction(IsolationLevel.ReadCommitted);
                return sqlTransaction;
            }
            catch (Exception e)
            {
                throw new Exception("打开事务失败",e);
            }
        }

        public static void RollbackTransaction(SqlTransaction sqlTransaction)
        {
            if (sqlTransaction == null)
            {
                throw new Exception("回滚事务失败");
            }
            else
            {
                sqlTransaction.Rollback();
                CloseConnection(sqlTransaction.Connection);
                sqlTransaction.Dispose();
                sqlTransaction = null;
            }
        }

        public static void CommitTransaction(SqlTransaction sqlTransaction)
        {
            if (sqlTransaction == null)
            {
                throw new Exception("提交事务失败");
            }
            else
            {
                sqlTransaction.Commit();
                CloseConnection(sqlTransaction.Connection);
                sqlTransaction.Dispose();
                sqlTransaction = null;
            }
        }
    #endregion

     

    #region "ExecuteDatasetDS"
        public static DataSet ExecuteDataSetDS(string strSQL)   //自定义方法
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();
            SqlCommand scd = new SqlCommand(strSQL, sqlConnection);
            try
            {   
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(scd);
                sda.Fill(ds);
                return ds;

            }
            catch (Exception e)
            {
                throw e;
            }

        }
        public static void ExecuteDatasetDS(DataSet dataSet, String tablename, String strSQL)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlConnection, System.Data.CommandType.Text, strSQL);
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch(System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
         }

        public static void ExecuteDatasetDS(DataSet dataSet, String tablename, String strSQL, Dictionary<String, String> dictParameter)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static void ExecuteDatasetDS(DataSet dataSet, String tablename, String strSQL, Dictionary<String, Double> dictParameter)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static void ExecuteDatasetDS(DataSet dataSet, String tablename, String strSQL, Dictionary<String, String> dictParaStr, Dictionary<String, Double> dictParaDbl)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParaStr, dictParaDbl));
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }
    #endregion

    #region "ExecuteNonQuery"

        public static int ExecuteNonQuery(String strSQL)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                return SqlHelper.ExecuteNonQuery(sqlConnection, System.Data.CommandType.Text, strSQL);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static int ExecuteNonQuery(String strSQL, Dictionary<String, String> dictParameter)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                return SqlHelper.ExecuteNonQuery(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static int ExecuteNonQuery(String strSQL, Dictionary<String, Double> dictParameter)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                return SqlHelper.ExecuteNonQuery(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static int ExecuteNonQuery(String strSQL, Dictionary<String, String> dictParaStr, Dictionary<String, Double> dictParaDbl)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                return SqlHelper.ExecuteNonQuery(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParaStr, dictParaDbl));
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }
    #endregion

    #region "ExecuteDatasetDSTransaction"
        public static void ExecuteDatasetDSTransaction(SqlTransaction sqlTransaction, DataSet dataSet, String tablename, String strSQL)
        {
            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlTransaction, System.Data.CommandType.Text, strSQL);
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static void ExecuteDatasetDSTransaction(SqlTransaction sqlTransaction, DataSet dataSet, String tablename, String strSQL, Dictionary<String, String> dictParameter)
        {
            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlTransaction, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static void ExecuteDatasetDSTransaction(SqlTransaction sqlTransaction, DataSet dataSet, String tablename, String strSQL, Dictionary<String, Double> dictParameter)
        {
            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlTransaction, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static void ExecuteDatasetDSTransaction(SqlTransaction sqlTransaction, DataSet dataSet, String tablename, String strSQL, Dictionary<String, String> dictParaStr, Dictionary<String, Double> dictParaDbl)
        {
            try
            {
                SqlDataReader DataReader = SqlHelper.ExecuteReader(sqlTransaction, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParaStr, dictParaDbl));
                SqlHelperExtension.Fill(DataReader, dataSet, tablename, 0, 0);
                DataReader.Close();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }
    #endregion

    #region "ExecuteNonQueryTransaction"
        public static int ExecuteNonQueryTransaction(SqlTransaction sqlTransaction, String strSQL)
        {
            try
            {
                return SqlHelper.ExecuteNonQuery(sqlTransaction, System.Data.CommandType.Text, strSQL);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
               throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static int ExecuteNonQueryTransaction(SqlTransaction sqlTransaction, String strSQL, Dictionary<String, String> dictParameter)
        {
            try
            {
                return SqlHelper.ExecuteNonQuery(sqlTransaction, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static int ExecuteNonQueryTransaction(SqlTransaction sqlTransaction, String strSQL, Dictionary<String, Double> dictParameter)
        {
            try
            {
                return SqlHelper.ExecuteNonQuery(sqlTransaction, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static int ExecuteNonQueryTransaction(SqlTransaction sqlTransaction, String strSQL, Dictionary<String, String> dictParaStr, Dictionary<String, Double> dictParaDbl)
        {
            try
            {
                return SqlHelper.ExecuteNonQuery(sqlTransaction, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParaStr, dictParaDbl));
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }
    #endregion

    #region "ExecuteDataset"
        public static DataSet ExecuteDataset(String strSQL)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                DataSet dataSetResult = SqlHelper.ExecuteDataset(sqlConnection, System.Data.CommandType.Text, strSQL);
                return dataSetResult;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static DataSet ExecuteDataset(String strSQL, Dictionary<String, String> dictParameter)
        {
            SqlConnection sqlConnection; 
            sqlConnection = GetConnection();

            try
            {
                DataSet dataSetResult = SqlHelper.ExecuteDataset(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
                return dataSetResult;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static DataSet ExecuteDataset(String strSQL, Dictionary<String, Double> dictParameter)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                DataSet dataSetResult = SqlHelper.ExecuteDataset(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParameter));
                return dataSetResult;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }

        public static DataSet ExecuteDataset(String strSQL, Dictionary<String, String> dictParaStr, Dictionary<String, Double> dictParaDbl)
        {
            SqlConnection sqlConnection;
            sqlConnection = GetConnection();

            try
            {
                DataSet dataSetResult = SqlHelper.ExecuteDataset(sqlConnection, System.Data.CommandType.Text, strSQL, ToSqlParameter(dictParaStr, dictParaDbl));
                return dataSetResult;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw GetApplicationException(e);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CloseConnection(sqlConnection);
            }
        }
    #endregion

    #region "ToSqlParameter"
        private static SqlParameter[] ToSqlParameter(Dictionary<String, String> dictParameter)
        {
            int intTotalNum;
            intTotalNum = dictParameter.Count;
            SqlParameter[] sqlParameters = new SqlParameter[intTotalNum];
            int intCount;
            intCount = 0;

            foreach (KeyValuePair<String, String> kvp in dictParameter)
            {
                //Console.WriteLine("Key = {0}, Value = {1}", kvp.Key, kvp.Value)
                sqlParameters[intCount] = new SqlParameter(kvp.Key, kvp.Value);
                intCount = intCount + 1;
            }

            return sqlParameters;
        }

        private static SqlParameter[] ToSqlParameter(Dictionary<String, Double> dictParameter)
        {
            int intTotalNum;
            intTotalNum = dictParameter.Count;
            SqlParameter[] sqlParameters = new SqlParameter[intTotalNum];
            int intCount;
            intCount = 0;

            foreach (KeyValuePair<String, Double> kvp in dictParameter)
            {
                sqlParameters[intCount] = new SqlParameter(kvp.Key, kvp.Value);
                intCount = intCount + 1;
            }

            return sqlParameters;
        }

        private static SqlParameter[] ToSqlParameter(Dictionary<String, String> dictParaStr, Dictionary<String, Double> dictParaDbl)
        {
            int intTotalNum;
            intTotalNum = dictParaStr.Count + dictParaDbl.Count;
            SqlParameter[] sqlParameters = new SqlParameter[intTotalNum];
            int intCount;
            intCount = 0;

            foreach (KeyValuePair<String, String> kvp in dictParaStr)
            {
                sqlParameters[intCount] = new SqlParameter(kvp.Key, kvp.Value);
                intCount = intCount + 1;
            }

            foreach (KeyValuePair<String, Double> kvp in dictParaDbl)
            {
                sqlParameters[intCount] = new SqlParameter(kvp.Key, kvp.Value);
                intCount = intCount + 1;
            }

            return sqlParameters;
        }
    #endregion

    #region "ExecuteNoqueryProcedure"
    public static int ExecuteNoqueryProcedure(String strProcedureName, SqlCommand cmdSqlCommand)
    {
        SqlConnection sqlConnection;
        sqlConnection = GetConnection();
        try
        {
            cmdSqlCommand.Connection = sqlConnection;
            cmdSqlCommand.CommandType = CommandType.StoredProcedure;
            cmdSqlCommand.CommandText = strProcedureName;

            return cmdSqlCommand.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException e) 
        {
            throw GetApplicationException(e);
        }
        catch (Exception e) 
        {
            throw e;
        }
        finally
        {
            CloseConnection(sqlConnection);
        }
    }
    #endregion
}

 

使用方法:

  1。string sql = "SELECT fch_maintenanceroute FROM dbo.systemuser WHERE  SystemUserID=  '" +ownerId +"'";
        DataSet ds;
        ds = SqlHelperExtension.ExecuteDataset(sql);

2。

        Dictionary<String, String> dictFromZoneStr= new Dictionary<String, String>();
        dictFromZoneStr.Add("@BranchCom_ID", subId + "");

        string sql = " SELECT OrigZone_Name,OrigZone_ID FROM OCS_MS_FromZone "
                    +" WHERE BranchCom_ID = @BranchCom_ID AND Is_Deleted='0 ' ORDER BY OrigZone_ID";

        DataSet ds;
        DataTable dt;
        string output = "";
        string separation = "\n";

        ds = SqlHelperExtension.ExecuteDataset(sql, dictFromZoneStr);