How to Call SQL Stored Procedures in Visual Basic

Using stored procedures in VB.NET is needed in applications that use a database for dynamic content. Using stored procedures is faster than inline SQL. It also prevents SQL injection attacks from hackers. Calling a stored procedure only takes a few lines of code in the code file for the application.

Instructions

1. Create and open the database connection. In Visual Basic, there is a connection object used to connect to the SQL Server. The following code creates a connection variable and opens it.
   Dim con As New SqlClient.SqlConnection
   con.ConnectionString = "Data Source=mySQLServer;User ID=Username;Password=myPass;"
   con.Open()

2. Instantiate the SQL Command object. This object is used to set parameters that are sent to the SQL Server for processing. In the code below, the "sql" variable is created. The command also uses the "con" variable from step one to call the database. The "sel_customer" parameter is the name of the stored procedure.
   Dim sql As New SqlClient.SqlCommand("sel_customer", con)

3. Set the command type as a "Stored Procedure." This code programs the command object to know a stored procedure is being called:
   sql.CommandType = CommandType.StoredProcedure;

4. Add a parameter to the stored procedure. Most stored procedures require parameters. In the code below, the stored procedure requires the customer's ID number to search for the information.
   sql.Parameters.Add(new SqlParameter("@CustomerId",SqlDbType.Numeric,0,"CustId"));
   sql.Parameters[0].Value=22;

5. Retrieve the information. In this simple request, only one parameter is returned. The code below sends the request to the server and retrieves the customer's first name:
   sql.ExecuteNonQuery();
   string firstname=(string) sql.Parameters["@firstname"].Value;

References

• macronimous.com