MickJ27

Hi all,

I'm just getting my head around the .NET Framework in this area so apologies if this question is dumb.

All examples in help and books relating to running a stored procedure seem to involve a database and the fill method. There must be numerous circumstances where you simply want to pass parameters into a stored procedure and let the database do the work, then access the output params.

Can I define and run a stored procedure without a datatable



Re: .NET Framework Data Access and Storage Execute Stored Procedure (and access output params) without a data table ?

Jeff Wharton

ExecuteNonQuery

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

ExecuteScalar

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.






Re: .NET Framework Data Access and Storage Execute Stored Procedure (and access output params) without a data table ?

MickJ27

OK - Thanks very much Jeff, much clearer. How are the output parameters accessed after the call Is this via the parameters array



Re: .NET Framework Data Access and Storage Execute Stored Procedure (and access output params) without a data table ?

Jeff Wharton

To get the return value using ExecuteNonQuery use:

Dim returnValue As Integer

returnValue = SQLCommand.ExecuteNonQuery

To get the return value using ExecuteScalar use (where object is replaced with the type of variable you expect returned i.e. string, int, datetime etc):

Dim returnValue As Object

returnValue = SQLCommand.ExecuteScalar






Re: .NET Framework Data Access and Storage Execute Stored Procedure (and access output params) without a data table ?

MickJ27

Thanks Jeff - what about the Output type parameters rather than the Return value (am I right in thinking a Stored Procedure can only return an Integer )



Re: .NET Framework Data Access and Storage Execute Stored Procedure (and access output params) without a data table ?

kbradl1

The return value can only be an integer. To get non-integer data use an output parameter:

SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

//show output parameter value
Console.WriteLine(cmd.Parameters["@ProductName"].Value);