An SqlServer Store Procedure DataAccess Layer Class

这是一个C#使用存储过程的Helper类。

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;

namespace SqlServerStoredProcedureDataAccessLayer
{

    public class SqlDatabase
    {

        #region Sql Object Methods

        public static SqlConnection GetConnection(string connectionString)
        {
            SqlConnection sqlConn = new SqlConnection(connectionString);
            return sqlConn;
        }

        public static SqlConnection GetConnection()
        {
            SqlConnection sqlConn = GetConnection(DefaultConnectionString());
            return sqlConn;
        }

        public static SqlCommand GetCommand(string storedProc,string connectionString)
        {
            SqlCommand sqlCommand = new SqlCommand(storedProc,GetConnection(connectionString));
            return sqlCommand;
        }

        public static SqlCommand GetCommand(string storedProc)
        {
            SqlCommand sqlCommand = GetCommand(storedProc,DefaultConnectionString());
            return sqlCommand;
        }

        public static SqlCommand GetStoredProcCommand(string storedProc,List<SqlParameter> commandParameters,string connectionString)
        {
            SqlCommand sqlCommand = GetCommand(storedProc,connectionString);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.AttachParameters(commandParameters);
            return sqlCommand;
        }

        public static SqlCommand GetStoredProcCommand(string storedProc,SqlParameter commandParameter,connectionString);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add(commandParameter);
            return sqlCommand;
        }

        public static SqlCommand GetStoredProcCommand(string storedProc,connectionString);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            return sqlCommand;
        }

        public static SqlCommand GetStoredProcCommand(string storedProc,List<SqlParameter> commandParameters)
        {
            SqlCommand sqlCommand = GetCommand(storedProc);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.AttachParameters(commandParameters);
            return sqlCommand;
        }

        public static SqlCommand GetStoredProcCommand(string storedProc,SqlParameter commandParameter)
        {
            SqlCommand sqlCommand = GetCommand(storedProc);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add(commandParameter);
            return sqlCommand;
        }

        public static SqlCommand GetStoredProcCommand(string storedProc)
        {
            SqlCommand sqlCommand = GetCommand(storedProc);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            return sqlCommand;
        }

        #endregion

        #region Connection String Methods

        public static string GetConnectionString(string key)
        {
            return ConfigurationManager.ConnectionStrings[key].ConnectionString;
        }

        private static string DefaultConnectionString()
        {
            return GetConnectionString("SqlDatabase");
        }

        #endregion

        #region ExecuteScalar

        public static object ExecuteScalar(string storedProc,string connectionString)
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc,connectionString);
            return ExecuteScalar(sqlCommand);
        }

        public static object ExecuteScalar(string storedProc,commandParameter,commandParameters,connectionString);
            return ExecuteScalar(sqlCommand);
        }

        public static object ExecuteScalar(string storedProc)
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc);
            return ExecuteScalar(sqlCommand);
        }

        public static object ExecuteScalar(string storedProc,SqlParameter commandParameter)
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc,commandParameter);
            return ExecuteScalar(sqlCommand);
        }

        public static object ExecuteScalar(string storedProc,List<SqlParameter> commandParameters)
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc,commandParameters);
            return ExecuteScalar(sqlCommand);
        }

        private static object ExecuteScalar(SqlCommand sqlCommand)
        {
            Object scalarResult;
            sqlCommand.Connection.Open();
            scalarResult = sqlCommand.ExecuteScalar();
            sqlCommand.Connection.Close();
            return scalarResult;
        }

        #endregion

        #region ExecuteNonquery

        public static void ExecuteNonquery(string storedProc,connectionString);
            sqlCommand.Connection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlCommand.Connection.Close();
        }

        public static void ExecuteNonquery(string storedProc,commandParameter);
            sqlCommand.Connection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlCommand.Connection.Close();
        }

        public static void ExecuteNonquery(string storedProc,commandParameters);
            sqlCommand.Connection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlCommand.Connection.Close();
        }

        #endregion

        #region ExecuteReader

        public static SqlDataReader ExecuteReader(string storedProc,connectionString);
            sqlCommand.Connection.Open();
            return sqlCommand.ExecuteReader();
        }

        public static SqlDataReader ExecuteReader(string storedProc,commandParameters);
            sqlCommand.Connection.Open();
            return sqlCommand.ExecuteReader();
        }

        public static SqlDataReader ExecuteReader(string storedProc,commandParameter);
            sqlCommand.Connection.Open();
            return sqlCommand.ExecuteReader();
        }

        public static SqlDataReader ExecuteReader(string storedProc)
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc);
            sqlCommand.Connection.Open();
            return sqlCommand.ExecuteReader();
        }

        #endregion

        #region Fill DataSet methods

        public static void FillDataSet(string storedProc,DataSet dataSet,connectionString);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataSet);
            sqlCommand.Connection.Close();
        }

        public static void FillDataSet(string storedProc,DataSet dataSet)
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc,commandParameters);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataSet);
            sqlCommand.Connection.Close();
        }

        public static void FillDataSet(string storedProc,commandParameter);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataSet);
            sqlCommand.Connection.Close();
        }

        public static void FillDataSet(string storedProc,DataSet dataSet)
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataSet);
            sqlCommand.Connection.Close();
        }

        #endregion

        #region Fill DataTable methods

        public static void FillDataTable(string storedProc,DataTable dataTable,connectionString);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataTable);
            sqlCommand.Connection.Close();
        }

        public static void FillDataTable(string storedProc,DataTable dataTable )
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc,commandParameters);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataTable);
            sqlCommand.Connection.Close();
        }

        public static void FillDataTable(string storedProc,commandParameter);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataTable);
            sqlCommand.Connection.Close();
        }

        public static void FillDataTable(string storedProc,DataTable dataTable )
        {
            SqlCommand sqlCommand = GetStoredProcCommand(storedProc);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
            dataAdapter.Fill(dataTable);
            sqlCommand.Connection.Close();
        }

        #endregion

        #region Get DataTable methods

        public static DataTable GetDataTable(string storedProc,string connectionString)
        {
            DataTable dtResults = new DataTable();
            FillDataTable(storedProc,dtResults,connectionString);
            return dtResults;
        }

        public static DataTable GetDataTable(string storedProc,List<SqlParameter> commandParameters)
        {
            DataTable dtResults = new DataTable();
            FillDataTable(storedProc,dtResults);
            return dtResults;
        }

        public static DataTable GetDataTable(string storedProc,SqlParameter commandParameter)
        {
            DataTable dtResults = new DataTable();
            FillDataTable(storedProc,dtResults);
            return dtResults;
        }

        public static DataTable GetDataTable(string storedProc)
        {
            DataTable dtResults = new DataTable();
            FillDataTable(storedProc,dtResults);
            return dtResults;
        }

        #endregion

        #region Get DataSet methods

        public static DataSet GetDataSet(string storedProc,string connectionString)
        {
            DataSet dsResults = new DataSet();
            FillDataSet(storedProc,dsResults,connectionString);
            return dsResults;
        }

        public static DataSet GetDataSet(string storedProc,List<SqlParameter> commandParameters )
        {
            DataSet dsResults = new DataSet();
            FillDataSet(storedProc,dsResults);
            return dsResults;
        }

        public static DataSet GetDataSet(string storedProc,SqlParameter commandParameter )
        {
            DataSet dsResults = new DataSet();
            FillDataSet(storedProc,dsResults);
            return dsResults;
        }

        public static DataSet GetDataSet(string storedProc )
        {
            DataSet dsResults = new DataSet();
            FillDataSet(storedProc,dsResults);
            return dsResults;
        }

        #endregion

    }

    internal static class SqlDatabaseExtensions
    {
        public static void AttachParameters(this SqlCommand command,List<SqlParameter> commandParameters)
        {
            command.Parameters.AddRange(commandParameters.ToArray());

            //if (commandParameters != null)
            //{
            //    foreach (SqlParameter p in commandParameters)
            //    {
            //        if (p != null)
            //        {
            //            // Check for derived output value with no value assigned 
            //            if ((p.Direction == ParameterDirection.InputOutput ||
            //                p.Direction == ParameterDirection.Input) &&
            //                (p.Value == null))
            //            {
            //                p.Value = DBNull.Value;
            //            }
            //            command.Parameters.Add(p);
            //        }
            //    }
            //}
        }
    }




}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!