asp.net中自动填充参数的存储过程类(C#)
执行一下这个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;