Parametrised SQL Server Stored Proc From ASP.net / C# / OLEDB

Lets assume that we have a stored procedure that is defined in the following way, it doesn’t really matter what the actual body of the SQL is:

CREATE proc [property].[usp_save_fault]
	(
	@SERVICE_LEVEL_ID int,
	@FAULT_NUMBER nvarchar(255),
	@NOTES nvarchar(1000)
	) AS

--do some stuff here!!!

We want to be able to call this procedure from an ASP.net page using C#. Firstly we need to set up a database connection, and a command object to execute the stored procedure:

Using System.Data.Oledb;

//Open Database Connection NB: you will need to create dbConnectionString to match your database
OleDbConnection dbConnection = new OleDbConnection(dbConnectionString);
dbConnection.Open();

//Set dbSQL to the name of the stored procedure
string dbSQL = "property.usp_save_fault";

//Create a command using the the stored proc name and dbConnection
OleDbCommand dbCommand = new OleDbCommand(dbSQL, dbConnection);

//Set the commandType as Stored Procedure
dbCommand.CommandType = CommandType.StoredProcedure;

Next we need to set up the parameters. The important thing here is that the parameters are set in the same order that they appear in the stored procedure definition.

//Add the parameters: IMPORTANT, the parameters must be added in the same order as they
//are specified in the Stored Procedure!
//In this case these are just text values from textboxes
dbCommand.Parameters.AddWithValue("@SERVICE_LEVEL_ID", Convert.ToInt32(txtServiceLevelID.Text));
dbCommand.Parameters.AddWithValue("@FAULT_NUMBER", txtFaultNumber.Text);
dbCommand.Parameters.AddWithValue("@NOTES", txtNotes.Text);

Now we just execute the command, then close the database connection.

//Execute the SQL
dbCommand.ExecuteNonQuery();
//Close the connection
dbConnection.Close();
Advertisements
This entry was posted in Programming and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s