How to read the output variable from .net c#?

Question

Does anyone know how can I read the output variable from .net c#?

Example:

If I have the following stored proc which will return the output variables (@customer_id, @customer_name, @customer_address, @customer_age) instead of the select variable, how can I read the output variable with the following?

mySqlCommand.CommandText = "EXEC app_customers @name=" + sName.Text;
mySqlConnection.Open();
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
while (mySqlDataReader.Read())
{
}

Just search fro c# stored procedure output parameter and the first 2 links are java2s.com/Tutorial/CSharp/0560__ADO.Net/… and java2s.com/Tutorial/CSharp/0560__ADO.Net/… — Adriaan Stander
– Adriaan Stander, Commented Sep 15, 2011 at 4:09

Anthony Pegram · Accepted Answer · 2011-09-15 04:35:59Z

7

When the result is a single value (or if you're just interested in the first value in the first column), use the method ExecuteScalar.

It returns an object, simply cast it to the expected type.

int id = (int)mySqlCommand.ExecuteScalar();

Note: the way you're invoking a procedure is not the normal way to do it. Set the command to reference the stored procedure, then add appropriate parameters to the command.Parameters collection. Invoking the procedure using "exec ..." is not a best practice and may even leave you vulnerable. If you need more info on executing such a call, start here.

Edit:

If it is truly an output parameter you need to capture (I believe I misread your question), then the above paragraph is even more applicable. Consider this approach:

mySqlCommand.CommandText = "app_customers";
mySqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
mySqlCommand.Parameters.AddWithValue("@name", theValue);
var customerIdParam = mySqlCommand.Parameters.Add("@customer_id", System.Data.SqlDbType.Int);
customerIdParam.Direction = System.Data.ParameterDirection.Output;
// add more parameters, setting direction as appropriate

mySqlCommand.ExecuteNonQuery();
int customerId = (int)customerIdParam.Value;
// read additional outputs

edited Sep 15, 2011 at 4:35

answered Sep 15, 2011 at 4:07

Anthony Pegram

128k28 gold badges229 silver badges252 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TrueWill Over a year ago

Parameters will also help protect you against SQL injection attacks.

Jin Yong Over a year ago

@Anthony Pegram: how about if the output variable is more then one?

Anthony Pegram Over a year ago

@Jin, you could try adding more parameters (should work with multiple output parameters, but I don't have experience trying that). However, if you are truly returning many such values, you might consider selecting those values and returning them as a table.

Collectives™ on Stack Overflow

How to read the output variable from .net c#?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related