reading parameters in C#

Unpossible

anybody know how to read out parameters from a stored procedure in C#?

I can write the procedures no problem its just the code on the C# side thats causing me problems

JazzyJ

Something to this effect:


SqlConnection cnPubs = new SqlConnection("server=localhost;integrated security=true;database=pubs");

SqlCommand cmdAuthors = new SqlCommand("up_AuthorBookCount", cnPubs);

cmdAuthors.CommandType = CommandType.StoredProcedure;

cmdAuthors.Parameters.Add("@au_id", SqlDbType.NVarChar, 11);
cmdAuthors.Parameters["@au_id"].Value = AuthorID;

cmdAuthors.Parameters.Add("@Count", SqlDbType.Int);
cmdAuthors.Parameters["@Count"].Direction = ParameterDirection.Output;

cnPubs.Open();
int iCount;
cmdAuthors.ExecuteNonQuery();
iCount = (int) cmdAuthors.Parameters["@Count"].Value;
cnPubs.Close();

Unpossible

cmdAuthors.Parameters.Add("@au_id", SqlDbType.NVarChar, 11);
cmdAuthors.Parameters["@au_id"].Value = AuthorID;

cmdAuthors.Parameters.Add("@Count", SqlDbType.Int);
cmdAuthors.Parameters["@Count"].Direction = ParameterDirection.Output;

could you please explain this piece in detail? as in whats happening

bonkey

Originally posted by Unpossible
could you please explain this piece in detail? as in whats happening

The first line of each "pair" is defining a parameter which the stored procedure needs.

Earlier data-access libraries queried the DB and figured out what parameters were needed -how many, what they were named, what datatype, what "direction" (input, output, return val), and so on, so all you had to do was supply the values.

However, this was actually quite poor in performance terms, so they decided it was a better approach to make the developer specifically state what parameters he wants, how many, what direction, what they're called, and so on and so forth.

The second line of each pair is actually supplying a value for each parameter.

jc

Unpossible

I already know how to enter parameters using:

myCommand.Parameters.Add(new SqlParameter("@Staff", SqlDbType.Int)).Value = i;

this enters in the staffNo, how would i read the output, i.e. date

Unpossible

its ok i got the parameters outputting. Do i still need a dataset if in just reading the output parameters

Kernel32

You don't need and shouldn't use a dataset if you are only interested in output parameters.

Also if you use the vs.net IDE you don't need to do a lot of parameter setup. Use the visual sqlCommand, drag it on, set the connection, the proc name and it will go to the database and build the parameter list for you. I am amazed at how many .Net developers are not using the IDE to its full potential and end up writing twice as much code as they need to.

Unpossible

I have to write the code, its my third year project & i have to do as little dragging and dropping as possible