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