How to call SQL store procedure

Question

How to call SQL store procedure

ifotos

Old Hand

Points: 385
More actions
October 12, 2004 at 6:26 pm

#60115

Dear all,
Please advise, is there any documentation or starter guide description/explaination on how to call & run SQL store procedure from either ASP.NET, C# or other programming language? I need a guide to start on how to pass variable from those application to SQL store procedure and capture the return value.
Please advise.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

The best thing to do is get a book on ADO.Net & C#.Net or just search on the Microsoft website for some examples. I have found some good stuff at Microsoft for what you are looking for.

Kindest Regards,

TriggerMe SSChampion Points: 11752 More actions · Answer 2

Sorry, forgot to give you an example to get you started. The code below calls a stored procedure and returns a DataSet. Keep in mind that the example below is just 1 way of doing it. There are several ways of doing what you have asked for.

public DataSet CreateCustomerDataSet(string strCustID)

{

SqlDataAdapter DACustomer = new SqlDataAdapter();

DataSet DSCustomer = new DataSet();

SqlConnection conn = new SqlConnection(strConn);

try

{

DACustomer.SelectCommand = new SqlCommand();

DACustomer.SelectCommand.Connection = conn;

DACustomer.SelectCommand.CommandText = "USP_GetCustomer";

DACustomer.SelectCommand.CommandType = CommandType.StoredProcedure;

DACustomer.SelectCommand.Parameters.Add("@CustomerID", strCustID);

DACustomer.Fill(DSCustomer, "Customer");

conn.Close();

}

catch (SqlException ex)

{

MessageBox.Show(ex.Message, "Northwind Traders",

MessageBoxButtons.OK, MessageBoxIcon.Error);

conn.Close();

}

return DSCustomer;

}

Kindest Regards,

Tal Mcmahon SSC Eights! Points: 850 More actions · Answer 3

Here is a Vb.net flavor Connection:

Dim SqlConnection1 as new System.Data.SqlClient.SqlConnectionSqlConnection1.ConnectionString =YourConnectionStringDim SqlCommand1 as new System.Data.SqlClient.SqlCommandSqlCommand1.CommandText = YourProcedureNameSqlCommand1.CommandType = System.Data.CommandType.StoredProcedureSqlCommand1.Connection = Me.SqlConnection1SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@YourParameter", System.Data.SqlDbType.Int, 4))SqlCommand1.Parameters.Item(0).Value = YourValue dim dr as System.Data.SqlClient.SqlDataReaderSqlConnection1.Open()dr = SqlCommand1.ExecuteReader()'Use your Data List heredr.Close()SqlConnection1.Close()Just a quickieHope that helps

Kindest Regards,

Tal Mcmahon

ifotos Old Hand Points: 385 More actions · Answer 4

Hi Trigger, Tal Mcmahon,

Thanks for the help. I will start from here. Thanks.