Calling stored procedures from C#

Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:

SqlCommand c =newSqlCommand("AddAuthor", myConnection);
c.CommandType =CommandType.StoredProcedure;

c.Parameters.Add(newSqlParameter("@.LastName",SqlDbType.VarChar, 100));
c.Parameters["@.LastName"].Value ="Last";

c.Parameters.Add(newSqlParameter("@.FirstName",SqlDbType.VarChar, 100));
c.Parameters["@.FirstName"].Value ="First";

In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction.

But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN@.@.IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code?

Thanks for the help in advance. Happy 4th to all! : ) -- Curt

If you do

SELECT @.@.IDENTITY in your stored procedure, you can get the value with

c.ExecuteScalar();

I do not know if this will work with the RETURN statement,though

|||

To use an output parameter, you need to add an output parameter to your SqlCommand prior to execution, like:

SqlParameter output =new SqlParameter("@.Return_Value", SqlDbType.VarChar, 100);output.Direction = ParameterDirection.Output;c.Parameters.Add(output);

When you execute your SP, you need to set the value. Then you can get the new value from the parameter.

This article is based around using a SqlDataSource, but it has details on the SP updateshttp://aspnet.4guysfromrolla.com/articles/050207-1.aspx.

I'd also take a look herehttp://msdn2.microsoft.com/en-gb/library/ms190315.aspx at the difference between @.@.IDENTITY and SCOPE_IDENTITY (my guess is SCOPE_IDENTITY is what you really want).

Hope that helps.

Aaron

|||

dt...

Yeah, when I added the "SELECT" to my SPROC, it worked fine - thanks for the tip. And yeah, as you suspected it didn't work for the "RETURN" - I had to add the "SELECT" to my SPROC. No problem though...

Athens, eh? Well, that goes back a couple of thousand years, doesn't it? ; ) Thanks for the help!

Curt

|||

however, if you want to get multiple values from your database you should use output parameters, just like agolden described. Moreover, if you use SQL Server 2005, you should use SCOPE_IDENTITY, as agolden also stated.