asp.net中自动填充参数的存储过程类(C#)

Wesley13
• 阅读 747

执行一下这个sql:

SELECT B.[name], C.[name] AS [type], B.length, B.isoutparam, B.isnullable
FROM sysobjects AS A INNER JOIN";
syscolumns AS B ON A.id = B.id AND A.xtype = 'P' AND A.name = '你的存储过程名' INNER JOIN
systypes C ON B.xtype = C.xtype AND C.[name] <> 'sysname'
ORDER BY ROW_NUMBER() OVER (ORDER BY B.id), B.isoutparam

看到结果了吧,此时此刻你是否有豁然开朗的感觉?

正是源于此,下面这个类就有了用武之地,从此不用繁琐的配置存储过程参数

using System;
using System.Collections;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

namespace fnSwordLibrary.DataBase
{
    public class CEx_SqlProcedure
    {
        #region 数据成员
        private SqlConnection _SqlConnection = null;
        private String _Procedure = String.Empty;
        private SqlCommand _SqlCmd = new SqlCommand();
        private Hashtable _InputTable = null; // 保存input参数和值
        private String _LastError = String.Empty;
        #endregion

        #region 构造函数
        public CEx_SqlProcedure()
        {
            _InputTable = new Hashtable();
            _SqlCmd.CommandType = CommandType.StoredProcedure;
        }

        public CEx_SqlProcedure(SqlConnection SqlConnection)
            : this()
        {
            this.SqlConnection = SqlConnection;
        }

        public CEx_SqlProcedure(String Procedure, SqlConnection SqlConnection)
            : this()
        {
            this.SqlConnection = SqlConnection;
            this.Procedure = Procedure;
        }
        #endregion

        #region 属性
        public String LastError
        {
            get
            {
                return this._LastError;
            }
        }

        public Object ReturnValue
        {
            get
            {
                return _SqlCmd.Parameters["RetVal"].Value;
            }
        }

        public SqlConnection SqlConnection
        {
            set
            {
                this._SqlConnection = value;
                _SqlCmd.Connection = this._SqlConnection;
            }
        }

        public String Procedure
        {
            set
            {
                this._Procedure = value;
                _SqlCmd.CommandText = this._Procedure;
            }

            get
            {
                return this._Procedure;
            }
        }
        #endregion

        #region 公共方法
        /// <summary>
        /// 执行存储过程,仅返回是否成功标志
        /// </summary>
        /// <param name="Procedure">存储过程名</param>
        /// <returns>是否成功标志</returns>
        public Boolean ExecuteNonQuery(String Procedure)
        {
            this.Procedure = Procedure;
            return ExecuteNonQuery();
        }

        /// <summary>
        /// 执行存储过程,仅返回是否成功标志
        /// </summary>
        /// <returns>是否成功标志</returns>
        public Boolean ExecuteNonQuery()
        {
            Boolean RetValue = true;
            // 绑定参数
            if (Bindings() == true)
            {
                try
                {
                    // 执行
                    _SqlCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    _LastError = "execute command error: " + ex.Message;
                    RetValue = false;
                }
            }
            else
            {
                RetValue = false;
            }

            _InputTable.Clear();

            return RetValue;
        }

        /// <summary>
        /// 执行存储过程,返回SqlDataReader
        /// </summary>
        /// <param name="Procedure">存储过程名</param>
        /// <returns>数据库读取行的只进流SqlDataReader</returns>
        public SqlDataReader ExecuteReader(String Procedure)
        {
            this.Procedure = Procedure;
            return ExecuteReader();
        }

        /// <summary>
        /// 执行存储过程,返回SqlDataReader
        /// </summary>
        /// <returns>数据库读取行的只进流SqlDataReader</returns>
        public SqlDataReader ExecuteReader()
        {
            SqlDataReader sqlReader = null;
            // 绑定参数
            if (Bindings() == true)
            {
                try
                {
                    // 执行
                    sqlReader = _SqlCmd.ExecuteReader();
                }
                catch (Exception ex)
                {
                    _LastError = "execute command error: " + ex.Message;
                }
            }

            _InputTable.Clear();

            return sqlReader;
        }

        /// <summary>
        /// 执行存储过程,返回SqlDataAdapter
        /// </summary>
        /// <param name="Procedure">存储过程名</param>
        /// <returns>SqlDataAdapter</returns>
        public SqlDataAdapter ExecuteAdapter(String Procedure)
        {
            this.Procedure = Procedure;
            return ExecuteAdapter();
        }

        /// <summary>
        /// 执行存储过程,返回SqlDataAdapter
        /// </summary>
        /// <returns>SqlDataAdapter</returns>
        public SqlDataAdapter ExecuteAdapter()
        {
            SqlDataAdapter sqlAdapter = null;

            // 绑定参数
            if (Bindings() == true)
            {
                try
                {
                    // 执行
                    sqlAdapter = new SqlDataAdapter(_SqlCmd);
                }
                catch (Exception ex)
                {
                    _LastError = "execute command error: " + ex.Message;
                }
            }

            _InputTable.Clear();

            return sqlAdapter;
        }

        /// <summary>
        /// 获取output的键值
        /// </summary>
        /// <param name="Output">output键名称</param>
        /// <returns>output键值</returns>
        public Object GetOutputValue(String Output)
        {
            return _SqlCmd.Parameters[Output].Value;
        }

        /// <summary>
        /// 设置Input参数值
        /// </summary>
        /// <param name="Key">参数名</param>
        /// <param name="Value">参数值</param>
        public void SetInputValue(String Key, Object Value)
        {
            if (Key == null)
            {
                return;
            }
            if (!Key.StartsWith("@"))
            {
                Key = "@" + Key;
            }

            if (_InputTable.ContainsKey(Key))
            {
                _InputTable[Key] = Value;
            }
            else
            {
                _InputTable.Add(Key, Value);
            }
        }

        /// <summary>
        /// 获取已设置的Input参数值
        /// 注:存储过程被成功执行后, Input参数被清空
        /// </summary>
        /// <param name="Key">参数名</param>
        /// <returns>参数值</returns>
        public Object GetInputValue(String Key)
        {
            if (Key == null)
            {
                return null;
            }
            if (!Key.StartsWith("@"))
            {
                Key = "@" + Key;
            }

            if (_InputTable.ContainsKey(Key))
            {
                return _InputTable[Key];
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 给SqlCommand对象绑定参数
        /// </summary>
        /// <returns>是否成功标志</returns>
        private Boolean Bindings()
        {
            _SqlCmd.Parameters.Clear();
            XmlReader sqlXmlReader = GetParameters();
            try
            {
                while (sqlXmlReader.Read())
                {
                    try
                    {
                        if (Byte.Parse(sqlXmlReader["isoutparam"]) == 1)
                        {
                            // 绑定output参数
                            _SqlCmd.Parameters.Add(sqlXmlReader["name"],
                             GetSqlDbType(sqlXmlReader["type"]),
                             Int32.Parse(sqlXmlReader["length"])).Direction = ParameterDirection.Output;
                        }
                        else
                        {
                            // 绑定input参数,并赋值
                            _SqlCmd.Parameters.Add(sqlXmlReader["name"],
                             GetSqlDbType(sqlXmlReader["type"]),
                             Int32.Parse(sqlXmlReader["length"])).Value = this.GetInputValue(sqlXmlReader["name"]);
                            /*
                             * 不必担心赋值的ParametersValue类型问题,SqlParameter.Value是object类型,自动转换
                             */
                        }
                    }
                    catch (Exception ex)
                    {
                        _LastError = sqlXmlReader["name"] + " parameter error: " + ex.Message;
                        return false;
                    }
                }

                // 绑定返回值
                _SqlCmd.Parameters.Add("RetVal", SqlDbType.Variant).Direction = ParameterDirection.ReturnValue;
            }
            catch (Exception ex)
            {
                _LastError = "binding parameter error: " + ex.Message;
                return false;
            }

            return true;
        }

        /// <summary>
        /// 由存储过程名, 取包含参数的XmlReader
        /// </summary>
        /// <param name="Procedure">存储过程名</param>
        /// <returns>包含参数的XmlReader</returns>
        private XmlReader GetParameters()
        {
            String sqlStr = "SELECT B.[name], C.[name] AS [type], B.length, B.isoutparam, B.isnullable";
            sqlStr += " FROM sysobjects AS A INNER JOIN";
            sqlStr += " syscolumns AS B ON A.id = B.id AND A.xtype = 'P' AND A.name = '" + _Procedure + "' INNER JOIN";
            sqlStr += " systypes C ON B.xtype = C.xtype AND C.[name] <> 'sysname'";
            sqlStr += " ORDER BY ROW_NUMBER() OVER (ORDER BY B.id), B.isoutparam";
            sqlStr += " FOR XML RAW";
            SqlCommand sqlCmd = new SqlCommand(sqlStr, _SqlConnection);
            // <row name="Action" type="varchar" length="50" isoutparam="0" isnullable="1" />
            XmlReader sqlXmlReader = null;
            try
            {
                sqlXmlReader = sqlCmd.ExecuteXmlReader();
            }
            catch (Exception ex)
            {
                if (sqlXmlReader != null) sqlXmlReader.Close();
                sqlXmlReader = null;
                _LastError = "get parameters error: " + ex.Message;
            }
            finally
            {
                sqlCmd.Dispose();
                sqlCmd = null;
            }
            return sqlXmlReader;
        }

        protected internal static SqlDbType GetSqlDbType(String TypeName)
        {
            switch (TypeName)
            {
                case "image":
                    return SqlDbType.Image;
                case "text":
                    return SqlDbType.Text;
                case "uniqueidentifier":
                    return SqlDbType.UniqueIdentifier;
                case "tinyint":
                    return SqlDbType.TinyInt;
                case "smallint":
                    return SqlDbType.SmallInt;
                case "int":
                    return SqlDbType.Int;
                case "smalldatetime":
                    return SqlDbType.SmallDateTime;
                case "real":
                    return SqlDbType.Real;
                case "money":
                    return SqlDbType.Money;
                case "datetime":
                    return SqlDbType.DateTime;
                case "float":
                    return SqlDbType.Float;
                case "sql_variant":
                    return SqlDbType.Variant;
                case "ntext":
                    return SqlDbType.NText;
                case "bit":
                    return SqlDbType.Bit;
                case "decimal":
                    return SqlDbType.Decimal;
                case "numeric":
                    return SqlDbType.Decimal;
                case "smallmoney":
                    return SqlDbType.SmallMoney;
                case "bigint":
                    return SqlDbType.BigInt;
                case "varbinary":
                    return SqlDbType.VarBinary;
                case "varchar":
                    return SqlDbType.VarChar;
                case "binary":
                    return SqlDbType.Binary;
                case "char":
                    return SqlDbType.Char;
                case "timestamp":
                    return SqlDbType.Timestamp;
                case "nvarchar":
                    return SqlDbType.NVarChar;
                case "nchar":
                    return SqlDbType.NChar;
                case "xml":
                    return SqlDbType.Xml;
                default:
                    return SqlDbType.Variant;
            }
        }
        #endregion
    }
}

 

// 调用示例
// 设置连接对象
CEx_SqlProcedure SqlProcedure = new CEx_SqlProcedure("doArticle", SqlConnection);
// 填充参数
SqlProcedure.SetInputValue("@Action", "GetArticles");
SqlProcedure.SetInputValue("@Keywords", Keyword);
SqlProcedure.SetInputValue("@SortID", Sort);
SqlProcedure.SetInputValue("@CurPage", CurPage);
SqlProcedure.SetInputValue("@PageSize", PageSize);
// 执行
SqlProcedure.ExecuteAdapter().Fill(sqlDataSet);
// 取output和返回值(为了简洁,我直接用object)
object MaxPage = SqlProcedure.GetOutputValue("@MaxPage");
object ReturnValue = SqlProcedure.ReturnValue;
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
Java获得今日零时零分零秒的时间(Date型)
publicDatezeroTime()throwsParseException{    DatetimenewDate();    SimpleDateFormatsimpnewSimpleDateFormat("yyyyMMdd00:00:00");    SimpleDateFormatsimp2newS
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这