Stored procedures are precompiled SQL statements stored in a database, offering improved performance, security, and maintainability. In VB.NET, executing a stored procedure from a code-behind file involves connecting to the database, setting up command objects, and handling parameters. This guide provides a step-by-step approach to executing stored procedures efficiently.
Experienced programmers prefer to use stored procedures in place of inline SQL Query. There are several advantages of using stored procedure. First of all stored procedure provides security & it helps to improve performance. Once a stored procedure execute it cached on the server. Stored procedure are easy to maintain & debug like a separate unit. Stored procedure can be tested independent of the application. In this session let us show you in Microsoft Technology VB.NET how to execute a stored procedure from the Code behind file.
In the below example I am using the stored procedure “USP_SearchLogo”. This stored procedure accepts 4 parameters. To execute this stored procedure here I am creating a new instance (SearchLogoCmd) of SqlCommand object by passing the stored procedure name & connection details (Connection String). Then to make compiler know that we are using stored procedure in command object in place of SQL Query, I defined command type to StoredProcedure. Now I need to pass parameter values before execute the procedure. To do that I am using SearchLogoCmd.Parameters.Add method. In this method I am passing parameter name with the data type depending upon the Stored procedure. Finally executing the Command object using ExecuteReader.
Execute a Stored Procedure from VB.NET Example
Dim SearchLogoCmd As New SqlCommand("USP_SearchLogo", sqlConn)
SearchLogoCmd.CommandType = CommandType.StoredProcedure
SearchLogoCmd.Parameters.Add("@NativeFlag", SqlDbType.NVarChar).Value = Request("NativeFlag")
SearchLogoCmd.Parameters.Add("@LogoName", SqlDbType.NVarChar).Value = Trim(txtLogoName.Text)
SearchLogoCmd.Parameters.Add("@LogoFileName", SqlDbType.NVarChar).Value = Trim(txtFileName.Text)
SearchLogoCmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = ddlStatus.SelectedValue
Try
sqlConn.Open()
ds = ConvertDataReaderToDataSet(SearchLogoCmd.ExecuteReader())
grdImageFiles.DataSource = ds
grdImageFiles.DataBind()
Catch ex As Exception
Response.Write(ex.ToString())
Finally
sqlConn.Close()
End Try
You may noticed here I used a function ConvertDataReaderToDataSet(). This is because of here my Stored procedure returns reader specific data. To bind those data to my gridview “grdImageFiles” using a DataSet I used this function.
Best Practices
1. Use Parameterized Queries – Avoid SQL injection by always using parameters.
2. Dispose Objects – Utilize `Using` statements to ensure proper disposal of `SqlConnection` and `SqlCommand`.
3. Connection Pooling – Let ADO.NET manage connections efficiently.
4. Error Logging – Log exceptions for debugging and monitoring.
Conclusion
Executing a stored procedure from VB.NET involves setting up a database connection, configuring a `SqlCommand` object, passing parameters, and handling results. Following these steps ensures secure and efficient database operations. By adhering to best practices, developers can maintain robust and scalable applications.
