用ExecuteNonQuery执行带参数的存储过程的时候怎么返回OUTPUT的返回值啊?偶用这个:
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "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)
OracleParameter[] commandParameters = ParamsCache.GetSpParameterSet(connectionString, spName); // Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes an array of OracleParameters
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "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)
OracleParameter[] commandParameters = ParamsCache.GetSpParameterSet(connectionString, spName); // Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes an array of OracleParameters
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
{
if( connection == null ) throw new ArgumentNullException( "connection" ); // Create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Finally, execute the command
int retval = cmd.ExecuteNonQuery(); // Detach the OracleParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}
private void cmdSample3_Click(object sender, System.EventArgs e)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null; try
{
try
{
connection = GetConnection(txtConnectionString.Text);
}
catch
{
MessageBox.Show("The connection with the database can磘 be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
} // Set up parameters (1 input and 3 output)
SqlParameter [] arParms = new SqlParameter[4];
// @ProductID Input Parameter
// assign value = 1
arParms[0] = new SqlParameter("@ProductID", SqlDbType.Int );
arParms[0].Value = 1; // @ProductName Output Parameter
arParms[1] = new SqlParameter("@ProductName", SqlDbType.NVarChar, 40);
arParms[1].Direction = ParameterDirection.Output; // @UnitPrice Output Parameter
arParms[2] = new SqlParameter("@UnitPrice", SqlDbType.Money);
arParms[2].Direction = ParameterDirection.Output; // @QtyPerUnit Output Parameter
arParms[3] = new SqlParameter("@QtyPerUnit", SqlDbType.NVarChar, 20);
arParms[3].Direction = ParameterDirection.Output; // Call ExecuteNonQuery static method of SqlHelper class
// We pass in database connection string, command type, stored procedure name and an array of SqlParameter objects
SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "getProductDetails", arParms);
// Display results in text box using the values of output parameters
txtResults.Text = arParms[1].Value + ", " + arParms[2].Value + ", " + arParms[3].Value;
}
catch(Exception ex)
{
string errMessage = "";
for( Exception tempException = ex; tempException != null ; tempException = tempException.InnerException )
{
errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
} MessageBox.Show( string.Format( "There are some problems while trying to use the Data Access Application block, please check the following error messages: {0}"
+ Environment.NewLine + "This test requires some modifications to the Northwind database. Please make sure the database has been initialized using the SetUpDataBase.bat database script, or from the Install Quickstart option on the Start menu.", errMessage ),
"Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
}
finally
{
if(connection != null)
connection.Dispose();
}
}
SqlParameter objects
SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "getProductDetails", arParms);// Display results in text box using the values of output parameters
txtResults.Text = arParms[1].Value + ", " + arParms[2].Value + ", " + arParms[3].Value;
不过请seesea125(一一:抢分惊动了党)老大解释一下,偶编译失败……
要有返回值使用 ExecuteScalar 方法。
SqlParameter[] paras={
new SqlParameter("@id",ID.Text.Trim()),
new SqlParameter("@name",SqlDbType.Char,10)//存储过程的返回参数
};
paras[1].Direction=ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(myconn,CommandType.StoredProcedure ,"search",paras);
//之前我没有加CommandType.StoredProcedure 就不能返回值,加了以后就可以了。
Pwd.Text=paras[1].Value.ToString();
string StoredProcName = HaooData.proc_Article_Process; SqlParameter[] commandParameters = new SqlParameter[40]; commandParameters[0] = new SqlParameter("@sqltype", Art.SqlType );
commandParameters[1] = new SqlParameter("@title",Art.Title);
commandParameters[2] = new SqlParameter("@url",Art.Url);
commandParameters[3] = new SqlParameter("@from",Art.From);
commandParameters[4] = new SqlParameter("@author",Art.Author);
commandParameters[5] = new SqlParameter("@abs",Art.Abs);{ SqlDataReader reader = SqlDataProvider.Get_Reader( StoredProcName, commandParameters );.....................
public static int Get_NonQuery( string Conn, string StoredProcName, SqlParameter[] commandParameters )
{
int Result = -1; try
{
Result = SqlHelper.ExecuteNonQuery(
Conn
, CommandType.StoredProcedure
, StoredProcName
, commandParameters
);
}
catch( Exception ex )
{
Result = -1;
throw new Exception("查询出错:" + ex.Message); } return Result;
}
..........
#region 返回 SqlDataReader 实例 Get_Reader() 田
/// <summary>
/// 返回 SqlDataReader Get_Reader()
/// </summary>
/// <param name="StoredProcName"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static SqlDataReader Get_Reader( string StoredProcName, SqlParameter[] commandParameters )
{
return Get_Reader( Functions.GetConfig(), StoredProcName, commandParameters );
}
public static SqlDataReader Get_Reader( string Conn, string StoredProcName, SqlParameter[] commandParameters )
{
SqlDataReader reader = null; try
{
reader = SqlHelper.ExecuteReader(
Conn
, CommandType.StoredProcedure
, StoredProcName
, commandParameters
); }
catch( Exception ex )
{
reader = null;
throw new Exception("查询出错:"+ex.Message);
} return reader;
}
#endregion