• SQLServer存储过程帮助类


    SQLServer存储过程帮助类:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data;
    using System.Reflection;
    using OfficeOpenXml;
    using OfficeOpenXml.Style;
    using System.Drawing;
    using System.IO;
    
    
    namespace DBUtility
    {
        public class StoreProcedure
        {
    
            // 连接字符串。
            private string connectionString;
            // 存储过程名称。
            private string storeProcedureName;
    
            //// <summary>
            /// 初始化 DataAccessHelper.StoreProceduer 对象。
            /// </summary>
            /// <param name="connectionString">数据库连接字符串。</param>
    
            public StoreProcedure(string connectionString)
            {
                this.connectionString = connectionString;
            }
    
            public StoreProcedure() { }
    
            //// <summary>
            /// 初始化 DataAccessHelper.StoreProceduer 对象。
            /// </summary>
            /// <param name="connectionString">数据库连接字符串。</param>
            /// <param name="storeProcedureName">存储过程名称。</param>
            public StoreProcedure(string storeProcedureName, string connectionString)
            {
                this.connectionString = connectionString;
                this.storeProcedureName = storeProcedureName;
            }
    
            //// <summary>
            /// 获取或设置存储过程名称。
            /// </summary>
            public string StoreProcedureName
            {
                get { return this.storeProcedureName; }
                set { this.storeProcedureName = value; }
            }
    
            //// <summary>
            /// 执行操作类(Insert/Delete/Update)存储过程。
            /// </summary>
            /// <param name="paraValues">传递给存储过程的参数值列表。</param>
            /// <returns>受影响的行数。</returns>
            public int ExecuteNonQuery(params object[] paraValues)
            {
    
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
    
                    SqlCommand command = this.CreateSqlCommand(connection);
    
                    try
                    {
                        this.DeriveParameters(command);
                        this.AssignParameterValues(command, paraValues);
                        connection.Open();
                        int affectedRowsCount = command.ExecuteNonQuery();
                        return affectedRowsCount;
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
    
            //// <summary>
            /// 执行存储过程,返回 System.Data.DataTable。
            /// </summary>
            /// <param name="paraValues">传递给存储过程的参数值列表。</param>
            /// <returns>包含查询结果的 System.Data.DataTable。</returns>
            public DataTable ExecuteDataTable(params object[] paraValues)
            {
    
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
    
                    SqlCommand command = this.CreateSqlCommand(connection);
    
                    try
                    {
                        this.DeriveParameters(command);
                        this.AssignParameterValues(command, paraValues);
                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);
                        return dataTable;
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
    
            //// <summary>
            /// 执行存储过程,填充指定的 System.Data.DataTable。
            /// </summary>
            /// <param name="dataTable">用于填充查询结果的 System.Data.DataTable。</param>
            /// <param name="paraValues">传递给存储过程的参数值列表。</param>
            public void ExecuteFillDataTable(DataTable dataTable, params object[] paraValues)
            {
    
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
    
                    SqlCommand command = this.CreateSqlCommand(connection);
    
                    try
                    {
                        this.DeriveParameters(command);
                        this.AssignParameterValues(command, paraValues);
                        connection.Open();
                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        adapter.Fill(dataTable);
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
    
            //// <summary>
            /// 执行存储过程返回 System.Data.SqlClient.SqlDataReader,
            /// 在 System.Data.SqlClient.SqlDataReader 对象关闭时,数据库连接自动关闭。
            /// </summary>
            /// <param name="paraValues">传递给存储过程的参数值列表。</param>
            /// <returns>包含查询结果的 System.Data.SqlClient.SqlDataReader 对象。</returns>
            public SqlDataReader ExecuteDataReader(params object[] paraValues)
            {
    
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
    
                    SqlCommand command = this.CreateSqlCommand(connection);
    
                    try
                    {
                        this.DeriveParameters(command);
                        this.AssignParameterValues(command, paraValues);
                        connection.Open();
                        return command.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
    
            //// <summary>
            /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
            /// </summary>
            /// <param name="paraValues">传递给存储过程的参数值列表。</param>
            /// <returns>结果集中第一行的第一列或空引用(如果结果集为空)。</returns>
            public object ExecuteScalar(params object[] paraValues)
            {
    
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
    
                    SqlCommand command = this.CreateSqlCommand(connection);
    
                    try
                    {
                        this.DeriveParameters(command);
                        this.AssignParameterValues(command, paraValues);
                        connection.Open();
                        object result = command.ExecuteScalar();
                        //string r = command.Parameters[1].Value.ToString();
                        return result;
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
    
            //// <summary>
            /// 从在 System.Data.SqlClient.SqlCommand 中指定的存储过程中检索参数信息并填充指定的
            /// System.Data.SqlClient.SqlCommand 对象的 System.Data.SqlClient.SqlCommand.Parameters 集  合。
            /// </summary>
            /// <param name="sqlCommand">将从其中导出参数信息的存储过程的 System.Data.SqlClient.SqlCommand 对象。</param>
            internal void DeriveParameters(SqlCommand sqlCommand)
            {
                try
                {
                    sqlCommand.Connection.Open();
                    SqlCommandBuilder.DeriveParameters(sqlCommand);
                    sqlCommand.Connection.Close();
                }
                catch
                {
                    if (sqlCommand.Connection != null)
                    {
                        sqlCommand.Connection.Close();
                    }
                    throw;
                }
            }
    
            // 用指定的参数值列表为存储过程参数赋值。
            private void AssignParameterValues(SqlCommand sqlCommand, params object[] paraValues)
            {
                if (paraValues != null)
                {
                    if ((sqlCommand.Parameters.Count - 1) != paraValues.Length)
                    {
                        throw new ArgumentNullException("The number of parameters does not match number of values for stored procedure.");
                    }
                    for (int i = 0; i < paraValues.Length; i++)
                    {
                        sqlCommand.Parameters[i + 1].Value = (paraValues[i] == null) ? DBNull.Value : paraValues[i];
                    }
                }
            }
    
            // 创建用于执行存储过程的 SqlCommand。
            private SqlCommand CreateSqlCommand(SqlConnection connection)
            {
    
                SqlCommand command = new SqlCommand(this.storeProcedureName, connection);
                command.CommandType = CommandType.StoredProcedure;
    
                return command;
            }
    
            //-------------------------------------这一部分是我完善的,因为没有执行后返回存储过程中的返回值的函数-----------------------
    
            /// <summary>
            /// 执行存储过程,返回存储过程定义的返回值,注意存储过程中参数(paraValues)如果为返回值赋为空,其它值位置对应好
            /// </summary>
            /// <param name="output">返回存储过程中定义的返回值数组</param>
            /// <param name="outParaNum">存储过程中返回值的个数</param>
            /// <param name="paraValues">存储过程全部参数值</param>
    
            public void ExecProcOutput(out object[] output, int outParaNum, params object[] paraValues)
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
    
                    SqlCommand command = this.CreateSqlCommand(connection);
                    output = new object[outParaNum];//存储过程中返回值的个数
                    try
                    {
                        this.DeriveParameters(command);
                        this.AssignParameterValues(command, paraValues);
                        connection.Open();
                        command.ExecuteNonQuery();
                        for (int i = 0; i < outParaNum; i++)//将存储过程返回的参数值返回到程序中
                        {
                            output[i] = command.Parameters[1].Value;
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
    }

    简单举例调用:

     DataTable dt=GetDataQuickly("GetData", beginTime, overTime);

    public DataTable GetDataQuickly(string procName,string beginTime,string overTime) {

    try
            {
            string connString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
            StoreProcedure sp = new StoreProcedure(procName, connString);//类的对象
            Object[] paraValues = new object[2];
            paraValues[0] = beginTime;//从第二个参数开始赋值
            paraValues[1] = overTime;
            DataTable dt = sp.ExecuteDataTable(paraValues);
    
            showResult.DataSource = dt;
            showResult.DataBind();
            return dt;
            }
            catch (Exception)
            {
                Response.Write("系统繁忙,稍后重试.....");
                return null;
            }
        }
  • 相关阅读:
    python3 网络编程
    python3 字典及其方法
    洛谷P2239 螺旋矩阵
    洛谷P4281 紧急会议
    洛谷 P4427 求和
    树的直径
    洛谷P3398 仓鼠找suger
    洛谷P3865 ST表
    洛谷P1638逛画展
    洛谷P1886 滑动窗口
  • 原文地址:https://www.cnblogs.com/jiangxianshen/p/7800314.html
Copyright © 2020-2023  润新知