Click here to Skip to main content
15,071,581 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have a stored procedure which is simple, but I want it anyway to be a stored procedure mainly due to design considerations (Go find the bugs in the random queries when changing stuff in the tables...)

Anyway, the procedure is supposed to be something like that

SQL
CREATE PROCEDURE _Proc(@id int, @events int OUTPUT)
AS
   SELECT @events = EventID from Table where ID = @id
   RETURN


How can I get multiple values in my C# code when using an SqlCommand?

EDIT:

I want to read the values from @events, just as if I was reading them from EventID in a regular SQL Command.

Does SQL Data Reader still work in such case? If so, how can it be operated?

C#
using(SqlCommand c = new SqlCommand("_Proc") { CommandType = CommandType.StoredProcedure })
{
c.Connection = connection...
// Then what? How do I extract the values of EventID? using SqlDataReader? If so, in the normal way, as if it was a simple query?
}
Posted
Updated 25-Oct-11 1:33am
v3
Comments
Praveen Meghwal 25-Oct-11 7:04am
   
Please make it clear. What out put you are expecting from the stored procedure. Looking at your stored procedure you will not get any result as you are just setting the value for variable @events. You will have to add a select statement after it like "select @events"
BobJanova 25-Oct-11 7:32am
   
Can't an SP just return a table? I think I'm missing the point of this question.
ShacharK 25-Oct-11 7:34am
   
Can it? RETURN SELECT ...?

C#
using (SqlConnection conn = new SqlConnection())
  {
    SqlCommand cmd = new SqlCommand("_proc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, int.MaxValue, ParameterDirection.Input));

    cmd.Parameters.Add(new SqlParameter("@events", SqlDbType.Int, int.MaxValue, ParameterDirection.Output));
@events = EventID
    conn.Open();

    cmd.ExecuteNonQuery();
    int id = cmd.Parameters["@events"].Value;

    conn.Close();
  }
   
Comments
ShacharK 25-Oct-11 8:39am
   
@events should be a multiple-valued column... I know how to do the simple query.

I need to read @events or simply the EventID column from C#
If you want to return multiple values from a single column then your stored procedure is wrong. You should not be using OUTPUT since it will only return a single value.

SQL
CREATE PROCEDURE _Proc(@id int)
AS
Begin
   SELECT EventID from Table where ID = @id
End


You will then use a SqlDataReader to read the values returned by the stored procedure. This examples just adds the returned eventIds to a list.

C#
int id = ????;  // Whatever value you are going to passed into the stored procedure
List<int> eventIds = new List<int>();

using (SqlConnection conn = new SqlConnection())
{
    SqlCommand cmd = new SqlCommand("_proc", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@ID", id));
 
    conn.Open();
 
    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
	using (SqlDataReader reader = command.ExecuteReader();)
	{
	    while (reader.Read())
		eventIds.Add(int.Parse(reader["EventId"].ToString()));
	}
    }
}
</int></int>
   
Comments
ShacharK 25-Oct-11 9:40am
   
Finally... All I was waiting for was someone to say that its just like the regular query syntax... :)
You will be able to access the value of output parameter after closing the reader only.
   
Comments
ShacharK 25-Oct-11 7:30am
   
I'm not really interested in it as an output parameter, see it as a "pseudo-code", I just want the multiple values that are returned in the column EventID. How can I get them from an SQL Command?
Create your parameters with INOUT keyword inside your proc, while adding the parameter to command object set the appropriate property of the parameter object, set values to your @events variable inside your procedure.

now you will be able to access as are expecting.
   
you can use standart ways that you use before in normal queries
but in Sql command you must write EXEC before your store procedure name
and dont use commandtype


like this :

SqlConnection con = new SqlConnection(["ConnectionString"])
SqlCommand com = new SqlCommand("EXEC _Proc @id",con);
com.Parameters.AddWithValue("@id",["IDVALUE"]);
con.Open();
SqlDataReader rdr = com.ExecuteReader();
ArrayList liste = new ArrayList();
While(rdr.Read())
{
liste.Add(rdr[0]); //if it returns multiple you can add them another arrays=> liste1.Add(rdr[1]) ..
}
con.Close();
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900