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.
