/// <summary> /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <res> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); /// </res> /// <param name="connection">a valid SqlConnection</param> /// <param name="spName">the name of the stored procedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) { //if we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } }
楼主说的是MS的这个SqlHelper吗无论几个参数,都可以用这个方法调用存储过程呀
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) { //if we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } } 通过ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)方法调用。
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <res>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
/// </res>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
通过ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)方法调用。
SqlHelper.ExecuteNonQuery(数据库连接字符串, CommandType.StoredProcedure, "存储过程");1个参数
SqlParameter[] sqlParameter = {
new SqlParameter("@SqlWhere",SqlDbType.VarChar,1000),
}; sqlParameter[0].Value = 参数;
SqlHelper.ExecuteNonQuery(数据库连接字符串, CommandType.StoredProcedure, "存储过程",sqlParameter);10个参数同一个参数类似.
@cityName varchar(200),
@ioutput int Output
as
select @ioutput=count(Club.ClubID) from Club,city where Club.CityID=city.CityID and CityName like '%'+ @cityName +'%'
GO
string connString = ConfigurationManager.ConnectionStrings["connStr"].ToString();
SqlParameter[] storedParams = new SqlParameter[1];
storedParams[0] = new SqlParameter("@cityName", SqlDbType.VarChar);
storedParams[0].Value = "成都";
SqlDataReader sdr = SqlHelper.ExecuteReader(connString, CommandType.StoredProcedure, "CityClub_Count", storedParams);
Response.Write(sdr.GetValue(0).ToString());就是要报 过程 'CityClub_Count' 需要参数 '@ioutput',但未提供该参数??????????
@cityName varchar(200),
@ioutput int Output
as
select @ioutput=count(Club.ClubID) from Club,city where Club.CityID=city.CityID and CityName like '%'+ @cityName +'%'
GOSqlHelper.ExecuteNonQuery(数据库连接字符串, CommandType.StoredProcedure, "存储过程"); 1个参数
SqlParameter[] sqlParameter = {
new SqlParameter("@SqlWhere",SqlDbType.VarChar,1000),
}; sqlParameter[0].Value = 参数;
SqlHelper.ExecuteNonQuery(数据库连接字符串, CommandType.StoredProcedure, "存储过程",sqlParameter);
这个问题就是没有搞懂多嘛!