How to Convert SQL Commands

Inline SQL commands entered within the code of a website can be converted into stored procedures. Stored procedures are faster and are less vulnerable to hackers. Converting SQL commands from a web page to a stored procedure is performed using the SQL Server Management Console and the application's code files.

Instructions

1. Create a stored procedure. The first step in the conversion is creating the stored procedure for replacement. For instance, for this example, the inline SQL is below:
   select first_name from customer where ID=<value>
   Create the stored procedure using the following syntax:
   create proc sel_Customer
   ( @ID int ) as
   select first_name from customer where ID=@ID

2. Add the namespaces to the top of the code file. This allows you to call stored procedures within the code. The following is the syntax to add SQL namespaces.
   using System.Data.OleDb;
   using System.Data.SqlClient;

3. Instantiate the command class that calls the stored procedure. The SQLCommand class is available from Microsoft, so it can be called without adding any components. The following instantiates the class and sets the call type as a stored procedure:
   SqlCommand mySQL= new SqlCommand(myDBConnection);
   mySQL.CommandType = CommandType.StoredProcedure;

4. Add the ID parameter. Since the stored procedure created in Step 1 requires the "@ID" variable, it needs to be added to the command prior to data retrieval. The syntax below creates the value:
   SqlParameter myVariable = mySQL.Parameters.Add( "@ID", SqlDbType.Int, 10);
   myVariable.Value = "1";

5. Open the database connection and call the stored procedure. In this example, the data is returned to a SQLReader object. The object you use can be the original code used for inline SQL since the recordsets returned are the same. The following example runs the stored procedure and returns the values into a SQLReader object:
   myDBConnection.Open();
   SqlDataReader myRdr = mySQL.ExecuteReader();

6. Display the first name of the customer. The following syntax writes the customer's first name to the screen:
   Console.WriteLine(myRdr[0].ToString())

References

• java2s.com