How do I communicate with a stored procedure?

[Vernware]

I am developing a program using asp.net 2.0. I am using C# as the programming language. In this project I am connected to a SQLServer database (SQLServer 2005). I have created a stored procedure in this database and want to call it from C#, pass it some parameters and receive from it a return value. I am not clear as to how to call it, pass the parameters and then access the return value. I would appreciate any help/advise/suggestions/etc on this. Thanks, Vern Vern

[kryzchek]

Use an SqlCommand object: using System.Data; using System.Data.SqlClient; SqlConnection objConnection = null; SqlCommand objCommand = null; //Set up your SqlConnection object objConnection = new SqlConnection(YOUR_CONNECTION_STRING); //Set up your SqlCommand object objCommand = new SqlCommand(); objCommand.CommandType = CommandType.StoredProcedure; objCommand.Connection = objConnection; objCommand.Connection.Open(); //Add in any parameters objCommand.Parameters.Add("@MyParam", MyParamValue); objCommand.ExecuteNonQuery(); if (objCommand.Connection != null) { objCommand.Connection.Close(); objCommand.Connection.Dispose(); } if (objCommand != null) { objCommand.Dispose(); objCommand = null; }

[Ennis Ray Lynch Jr]

See using System.Data.SqlClient; Here is some code to play with. If you are returning a query use ExecuteReader instead.

SqlConnection connection = new SqlConnection("someConnectionString");
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "stored procedure name");

		SqlParameter parameterCustomerId = command.CreateParameter();
		parameterCustomerId.DbType = System.Data.DbType.Int64;
		parameterCustomerId.Direction = System.Data.ParameterDirection.Output;
		
		command.ExecuteNonQuery();

I feel so silly, my code doesn't work! parameterCustomerId.ParameterName = "@customerId"; command.Parameters.Add(parameterCustomerId); needs to be added before the execute.

---

File Not Found

[Vernware]

Ennis: Thanks for the tips. This wsa very helpful. Thanks again, Vern

[Vernware]

kryzchek: Thanks for the code. This code and other code I got from this site was very helpful. Thanks again, Vern