执行一下这个sql：
1SELECT B.[name], C.[name] AS [type], B.length, B.isoutparam, B.isnullable 2FROM sysobjects AS A INNER JOIN"; 3syscolumns AS B ON A.id = B.id AND A.xtype = 'P' AND A.name = '你的存储过程名' INNER JOIN 4systypes C ON B.xtype = C.xtype AND C.[name] <> 'sysname' 5ORDER BY ROW_NUMBER() OVER (ORDER BY B.id), B.isoutparam
看到结果了吧，此时此刻你是否有豁然开朗的感觉？
正是源于此，下面这个类就有了用武之地，从此不用繁琐的配置存储过程参数
1using System; 2using System.Collections; 3using System.Text; 4using System.Data.SqlClient; 5using System.Data; 6using System.Xml; 7 8namespace fnSwordLibrary.DataBase 9{ 10 public class CEx_SqlProcedure 11 { 12 #region 数据成员 13 private SqlConnection _SqlConnection = null; 14 private String _Procedure = String.Empty; 15 private SqlCommand _SqlCmd = new SqlCommand(); 16 private Hashtable _InputTable = null; // 保存input参数和值 17 private String _LastError = String.Empty; 18 #endregion 19 20 #region 构造函数 21 public CEx_SqlProcedure() 22 { 23 _InputTable = new Hashtable(); 24 _SqlCmd.CommandType = CommandType.StoredProcedure; 25 } 26 27 public CEx_SqlProcedure(SqlConnection SqlConnection) 28 : this() 29 { 30 this.SqlConnection = SqlConnection; 31 } 32 33 public CEx_SqlProcedure(String Procedure, SqlConnection SqlConnection) 34 : this() 35 { 36 this.SqlConnection = SqlConnection; 37 this.Procedure = Procedure; 38 } 39 #endregion 40 41 #region 属性 42 public String LastError 43 { 44 get 45 { 46 return this._LastError; 47 } 48 } 49 50 public Object ReturnValue 51 { 52 get 53 { 54 return _SqlCmd.Parameters["RetVal"].Value; 55 } 56 } 57 58 public SqlConnection SqlConnection 59 { 60 set 61 { 62 this._SqlConnection = value; 63 _SqlCmd.Connection = this._SqlConnection; 64 } 65 } 66 67 public String Procedure 68 { 69 set 70 { 71 this._Procedure = value; 72 _SqlCmd.CommandText = this._Procedure; 73 } 74 75 get 76 { 77 return this._Procedure; 78 } 79 } 80 #endregion 81 82 #region 公共方法 83 /// <summary> 84 /// 执行存储过程，仅返回是否成功标志 85 /// </summary> 86 /// <param name="Procedure">存储过程名</param> 87 /// <returns>是否成功标志</returns> 88 public Boolean ExecuteNonQuery(String Procedure) 89 { 90 this.Procedure = Procedure; 91 return ExecuteNonQuery(); 92 } 93 94 /// <summary> 95 /// 执行存储过程，仅返回是否成功标志 96 /// </summary> 97 /// <returns>是否成功标志</returns> 98 public Boolean ExecuteNonQuery() 99 { 100 Boolean RetValue = true; 101 // 绑定参数 102 if (Bindings() == true) 103 { 104 try 105 { 106 // 执行 107 _SqlCmd.ExecuteNonQuery(); 108 } 109 catch (Exception ex) 110 { 111 _LastError = "execute command error: " + ex.Message; 112 RetValue = false; 113 } 114 } 115 else 116 { 117 RetValue = false; 118 } 119 120 _InputTable.Clear(); 121 122 return RetValue; 123 } 124 125 /// <summary> 126 /// 执行存储过程，返回SqlDataReader 127 /// </summary> 128 /// <param name="Procedure">存储过程名</param> 129 /// <returns>数据库读取行的只进流SqlDataReader</returns> 130 public SqlDataReader ExecuteReader(String Procedure) 131 { 132 this.Procedure = Procedure; 133 return ExecuteReader(); 134 } 135 136 /// <summary> 137 /// 执行存储过程，返回SqlDataReader 138 /// </summary> 139 /// <returns>数据库读取行的只进流SqlDataReader</returns> 140 public SqlDataReader ExecuteReader() 141 { 142 SqlDataReader sqlReader = null; 143 // 绑定参数 144 if (Bindings() == true) 145 { 146 try 147 { 148 // 执行 149 sqlReader = _SqlCmd.ExecuteReader(); 150 } 151 catch (Exception ex) 152 { 153 _LastError = "execute command error: " + ex.Message; 154 } 155 } 156 157 _InputTable.Clear(); 158 159 return sqlReader; 160 } 161 162 /// <summary> 163 /// 执行存储过程，返回SqlDataAdapter 164 /// </summary> 165 /// <param name="Procedure">存储过程名</param> 166 /// <returns>SqlDataAdapter</returns> 167 public SqlDataAdapter ExecuteAdapter(String Procedure) 168 { 169 this.Procedure = Procedure; 170 return ExecuteAdapter(); 171 } 172 173 /// <summary> 174 /// 执行存储过程，返回SqlDataAdapter 175 /// </summary> 176 /// <returns>SqlDataAdapter</returns> 177 public SqlDataAdapter ExecuteAdapter() 178 { 179 SqlDataAdapter sqlAdapter = null; 180 181 // 绑定参数 182 if (Bindings() == true) 183 { 184 try 185 { 186 // 执行 187 sqlAdapter = new SqlDataAdapter(_SqlCmd); 188 } 189 catch (Exception ex) 190 { 191 _LastError = "execute command error: " + ex.Message; 192 } 193 } 194 195 _InputTable.Clear(); 196 197 return sqlAdapter; 198 } 199 200 /// <summary> 201 /// 获取output的键值 202 /// </summary> 203 /// <param name="Output">output键名称</param> 204 /// <returns>output键值</returns> 205 public Object GetOutputValue(String Output) 206 { 207 return _SqlCmd.Parameters[Output].Value; 208 } 209 210 /// <summary> 211 /// 设置Input参数值 212 /// </summary> 213 /// <param name="Key">参数名</param> 214 /// <param name="Value">参数值</param> 215 public void SetInputValue(String Key, Object Value) 216 { 217 if (Key == null) 218 { 219 return; 220 } 221 if (!Key.StartsWith("@")) 222 { 223 Key = "@" + Key; 224 } 225 226 if (_InputTable.ContainsKey(Key)) 227 { 228 _InputTable[Key] = Value; 229 } 230 else 231 { 232 _InputTable.Add(Key, Value); 233 } 234 } 235 236 /// <summary> 237 /// 获取已设置的Input参数值 238 /// 注：存储过程被成功执行后, Input参数被清空 239 /// </summary> 240 /// <param name="Key">参数名</param> 241 /// <returns>参数值</returns> 242 public Object GetInputValue(String Key) 243 { 244 if (Key == null) 245 { 246 return null; 247 } 248 if (!Key.StartsWith("@")) 249 { 250 Key = "@" + Key; 251 } 252 253 if (_InputTable.ContainsKey(Key)) 254 { 255 return _InputTable[Key]; 256 } 257 else 258 { 259 return null; 260 } 261 } 262 #endregion 263 264 #region 私有方法 265 /// <summary> 266 /// 给SqlCommand对象绑定参数 267 /// </summary> 268 /// <returns>是否成功标志</returns> 269 private Boolean Bindings() 270 { 271 _SqlCmd.Parameters.Clear(); 272 XmlReader sqlXmlReader = GetParameters(); 273 try 274 { 275 while (sqlXmlReader.Read()) 276 { 277 try 278 { 279 if (Byte.Parse(sqlXmlReader["isoutparam"]) == 1) 280 { 281 // 绑定output参数 282 _SqlCmd.Parameters.Add(sqlXmlReader["name"], 283 GetSqlDbType(sqlXmlReader["type"]), 284 Int32.Parse(sqlXmlReader["length"])).Direction = ParameterDirection.Output; 285 } 286 else 287 { 288 // 绑定input参数，并赋值 289 _SqlCmd.Parameters.Add(sqlXmlReader["name"], 290 GetSqlDbType(sqlXmlReader["type"]), 291 Int32.Parse(sqlXmlReader["length"])).Value = this.GetInputValue(sqlXmlReader["name"]); 292 /* 293 * 不必担心赋值的ParametersValue类型问题，SqlParameter.Value是object类型，自动转换 294 */ 295 } 296 } 297 catch (Exception ex) 298 { 299 _LastError = sqlXmlReader["name"] + " parameter error: " + ex.Message; 300 return false; 301 } 302 } 303 304 // 绑定返回值 305 _SqlCmd.Parameters.Add("RetVal", SqlDbType.Variant).Direction = ParameterDirection.ReturnValue; 306 } 307 catch (Exception ex) 308 { 309 _LastError = "binding parameter error: " + ex.Message; 310 return false; 311 } 312 313 return true; 314 } 315 316 /// <summary> 317 /// 由存储过程名, 取包含参数的XmlReader 318 /// </summary> 319 /// <param name="Procedure">存储过程名</param> 320 /// <returns>包含参数的XmlReader</returns> 321 private XmlReader GetParameters() 322 { 323 String sqlStr = "SELECT B.[name], C.[name] AS [type], B.length, B.isoutparam, B.isnullable"; 324 sqlStr += " FROM sysobjects AS A INNER JOIN"; 325 sqlStr += " syscolumns AS B ON A.id = B.id AND A.xtype = 'P' AND A.name = '" + _Procedure + "' INNER JOIN"; 326 sqlStr += " systypes C ON B.xtype = C.xtype AND C.[name] <> 'sysname'"; 327 sqlStr += " ORDER BY ROW_NUMBER() OVER (ORDER BY B.id), B.isoutparam"; 328 sqlStr += " FOR XML RAW"; 329 SqlCommand sqlCmd = new SqlCommand(sqlStr, _SqlConnection); 330 // <row name="Action" type="varchar" length="50" isoutparam="0" isnullable="1" /> 331 XmlReader sqlXmlReader = null; 332 try 333 { 334 sqlXmlReader = sqlCmd.ExecuteXmlReader(); 335 } 336 catch (Exception ex) 337 { 338 if (sqlXmlReader != null) sqlXmlReader.Close(); 339 sqlXmlReader = null; 340 _LastError = "get parameters error: " + ex.Message; 341 } 342 finally 343 { 344 sqlCmd.Dispose(); 345 sqlCmd = null; 346 } 347 return sqlXmlReader; 348 } 349 350 protected internal static SqlDbType GetSqlDbType(String TypeName) 351 { 352 switch (TypeName) 353 { 354 case "image": 355 return SqlDbType.Image; 356 case "text": 357 return SqlDbType.Text; 358 case "uniqueidentifier": 359 return SqlDbType.UniqueIdentifier; 360 case "tinyint": 361 return SqlDbType.TinyInt; 362 case "smallint": 363 return SqlDbType.SmallInt; 364 case "int": 365 return SqlDbType.Int; 366 case "smalldatetime": 367 return SqlDbType.SmallDateTime; 368 case "real": 369 return SqlDbType.Real; 370 case "money": 371 return SqlDbType.Money; 372 case "datetime": 373 return SqlDbType.DateTime; 374 case "float": 375 return SqlDbType.Float; 376 case "sql_variant": 377 return SqlDbType.Variant; 378 case "ntext": 379 return SqlDbType.NText; 380 case "bit": 381 return SqlDbType.Bit; 382 case "decimal": 383 return SqlDbType.Decimal; 384 case "numeric": 385 return SqlDbType.Decimal; 386 case "smallmoney": 387 return SqlDbType.SmallMoney; 388 case "bigint": 389 return SqlDbType.BigInt; 390 case "varbinary": 391 return SqlDbType.VarBinary; 392 case "varchar": 393 return SqlDbType.VarChar; 394 case "binary": 395 return SqlDbType.Binary; 396 case "char": 397 return SqlDbType.Char; 398 case "timestamp": 399 return SqlDbType.Timestamp; 400 case "nvarchar": 401 return SqlDbType.NVarChar; 402 case "nchar": 403 return SqlDbType.NChar; 404 case "xml": 405 return SqlDbType.Xml; 406 default: 407 return SqlDbType.Variant; 408 } 409 } 410 #endregion 411 } 412}
1// 调用示例 2// 设置连接对象 3CEx_SqlProcedure SqlProcedure = new CEx_SqlProcedure("doArticle", SqlConnection); 4// 填充参数 5SqlProcedure.SetInputValue("@Action", "GetArticles"); 6SqlProcedure.SetInputValue("@Keywords", Keyword); 7SqlProcedure.SetInputValue("@SortID", Sort); 8SqlProcedure.SetInputValue("@CurPage", CurPage); 9SqlProcedure.SetInputValue("@PageSize", PageSize); 10// 执行 11SqlProcedure.ExecuteAdapter().Fill(sqlDataSet); 12// 取output和返回值(为了简洁，我直接用object) 13object MaxPage = SqlProcedure.GetOutputValue("@MaxPage"); 14object ReturnValue = SqlProcedure.ReturnValue;