自动填充参数的存储过程类(C#)（转载）

于 2007-08-24 17:25:00 发布
阅读量72 收藏
点赞数
文章标签： 数据库 c#

我在用C#调用sql2005的存储过程，觉得设置参数太烦锁了，参数名、类型、宽度、输入/输出/返回值，每次都要设置，2、3个参数还好办，有时候达到十几、二十个，最多的一次40多个，代码又长又臭，人也累半死。

 为了摆脱这种体力劳动，我就尝试寻找一个简便的方法。在sql server数据库中有一些系统表，保存该库中所有的"对象"，当然有存储过程名，参数名，宽度，类型等等。

请参见：SQL Server 2005中各个系统表的作用

执行一下这个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;

CEx_SqlProcedure.rar

weixin_34367257
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值