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();