The OleDb Namespace and Stored Procedures In this section, you'll investigate the three stored procedures that use the OleDb namespace. You'll use the three stored procedures you added to the Northwind database from a demonstration page. You'll see how to supply parameter values, as well. Retrieving a List of All Employees To demonstrate calling stored procedures from your ASP.NET applications, start out by investigating the EmpLoad procedure (in EmployeeSP.aspx.vb), which calls the EmployeeList stored procedure. Listing 15.4 shows the complete EmpLoad procedure. Listing 15.4 Call a Stored Procedure to Fill a DataSet Private Sub EmpLoad() Dim ds As DataSet Dim da As OleDbDataAdapter Dim strSQL As String Dim strConn As String strConn = Session("ConnectString").ToString() strSQL = "EmployeeList" Try ds = New DataSet() da = New OleDbDataAdapter(strSQL, strConn) da.Fill(ds, "Employees") With ddlEmps .DataTextField = "Name" .DataValueField = "EmployeeID" .DataSource = ds .DataBind() End With Catch exp As Exception lblError.Text = exp.Message End Try End Sub Not much new here! This procedure looks exactly like procedures you've seen in other chapters, except for one big difference: Rather than specifying a SELECT statement as the SQL expression to be evaluated, this procedure uses the name of a stored procedure. It's that simple pass the DataAdapter object a stored procedure that returns rows, and it can fill a DataSet using the stored procedure just as easily as it did using a SQL SELECT statement directly. The Page_Load procedure for the page calls the EmpLoad procedure so that the DropDownList control gets filled as you load the page: Private Sub Page_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load If Not Page.IsPostBack Then EmpLoad() End If End Sub  | It's time to test calling a stored procedure. Follow these steps to verify that things are working as planned: |
-
In the Solution Explorer window, right-click EmployeeSP.aspx and select Build and Browse from the context menu. -
Verify that the DropDownList control contains the list of employees. Remember, you obtained that list by calling a stored procedure. Adding an Employee The sample page provides text boxes where you can enter a new employee's first and last names. If you enter values and then click Add Using OleDb (we'll come back to Add Using SqlClient later), you'll run a stored procedure that adds the new employee and passes back the new employee's ID. Clicking the button runs the code in Listing 15.5. Listing 15.5 Add a New Row Using a Stored Procedure Private Sub EmpAddOleDb() Dim cmd As OleDbCommand Dim strSQL As String Dim strConn As String Dim intRows As Integer ' Retrieve connection string strConn = Session("ConnectString").ToString ' The stored procedure name strSQL = "EmployeeAdd" Try cmd = New OleDbCommand() With cmd ' Create a Connection object .Connection = _ New OleDbConnection(strConn) ' Set the SQL .CommandText = strSQL .CommandType = CommandType.StoredProcedure ' Create return value parameter ' Must be the first parameter added With .Parameters.Add("ReturnValue", _ OleDbType.Integer) .Direction = ParameterDirection.ReturnValue End With ' Create OUTPUT Parameter for ' EmployeeID which will be ' fetched using @@IDENTITY With .Parameters.Add("EmpID", OleDbType.Integer) .Direction = ParameterDirection.Output End With ' Add input parameters. With .Parameters.Add("FirstName", _ OleDbType.Char, 10) .Value = txtFirst.Text End With With .Parameters.Add("LastName", _ OleDbType.Char, 20) .Value = txtLast.Text End With ' Open the connection .Connection.Open() ' Execute the stored procedure intRows = .ExecuteNonQuery() ' Get the output parameter lblEmpID.Text = _ CStr(.Parameters("EmpID").Value) ' Get the return value parameter lblError.Text = "ReturnValue = " & _ CStr(.Parameters("ReturnValue").Value) & _ "<BR>Rows Affected=" & intRows.ToString() End With ' Reload DropDownList control EmpLoad() Catch exp As Exception lblError.Text = exp.Message Finally ' Close the Connection With cmd.Connection If .State = ConnectionState.Open Then .Close() End If End With End Try End Sub Overall, this procedure doesn't do much (although it does contain what appears to be a lot of code). For the most part, it creates Parameter objects (actually, OleDbParameter or SqlParameter objects) representing each of the parameters you must send the stored procedure, and it appends the parameters to the Parameters property of the Command object. Once it has done that, it calls the stored procedure. Finally, it retrieves the output parameter (the EmpID parameter) and the return value (the error code). Step by step, the procedure takes these actions: -
It retrieves the connection string and sets up the SQL for the Command object: ' Set up the connection and SQL strings. strConn = Session("ConnectString").ToString strSQL = "EmployeeAdd" -
It creates a new Command object and sets its properties, as you've seen before: cmd = New OleDbCommand() With cmd ' Create a Connection object .Connection = _ New OleDbConnection(strConn) ' Set the SQL .CommandText = strSQL .CommandType = CommandType.StoredProcedure ... End With TIP The CommandType parameter indicates to ADO.NET what type of value you've placed in the CommandText property. In this case, you've given the CommandText property the name of a stored procedure and therefore have set the CommandType property accordingly. Although ADO.NET can "guess" what you've sent it, it's best to be explicit. -
It creates a new Parameter object, indicating that it's to be an Integer type and that it's the return value: ' Create return value parameter ' Must be the first parameter added With .Parameters.Add("ReturnValue", _ OleDbType.Integer) .Direction = ParameterDirection.ReturnValue End With TIP When you're using the OleDb namespace, it's important that you add the "return value" parameter first. (That is, if you set the Direction property to be ParameterDirection.ReturnValue, it must be the first parameter added.) When using the SqlClient namespace, the order doesn't matter. Also, the name of the parameter doesn't matter. We've named this ReturnValue, but it could have been named anything. The important issue is that the Direction property has been set to ParameterDirection.ReturnValue. -
It creates the output parameter object, named EmpID (see the definition of the stored procedure, earlier in the chapter, to match up the parameter name). The code indicates that this is an output parameter by setting the Direction property: ' Create OUTPUT Parameter for ' EmployeeID which will be ' fetched using @@IDENTITY With .Parameters.Add("EmpID", OleDbType.Integer) .Direction = ParameterDirection.Output End With -
It creates the two standard input parameters: FirstName and LastName. The code sets the value for each (remember, you're sending these values into the stored procedure, so you need to indicate the value of the parameters before calling it): ' Add input parameters. With .Parameters.Add("FirstName", _ OleDbType.Char, 10) .Value = txtFirst.Text End With With .Parameters.Add("LastName", _ OleDbType.Char, 20) .Value = txtLast.Text End With -
It opens the connection and calls the procedure. Executing the procedure returns the number of rows it affected: ' Open the connection .Connection.Open() ' Execute the stored procedure intRows = .ExecuteNonQuery() -
The stored procedure filled in its output parameter with the new employee ID value, so the code needs to retrieve that and display it: ' Get the output parameter lblEmpID.Text = _ CStr(.Parameters("EmpID").Value) -
The code retrieves the return value and displays this value and the number of rows affected in the label on the page: ' Get the return value parameter lblError.Text = "ReturnValue = " & _ CStr(.Parameters("ReturnValue").Value) & _ "<BR>Rows Affected=" & intRows.ToString() -
It reloads the list of employees, because there's now a new one in the list: ' Reload DropDownList control EmpLoad() -
Whether the procedure succeeds or fails, it closes the connection. Attempting to close a connection that isn't open triggers a runtime error, so the code checks the state of the connection first and only closes it if necessary: ' Close the Connection With cmd.Connection If .State = ConnectionState.Open Then .Close() End If End With
Deleting an Employee After the page loads the list of employees, you can select an employee from the list. If you click Delete Employee, you'll run code that executes a stored procedure to delete the selected employee. NOTE Due to constraints in SQL Server, not every employee will be able to be deleted. If an employee has taken orders, referential integrity will make it impossible for you to delete the employee this is a good thing! If you receive an error, that means the chosen employee has sales information that stops the employee from being deleted. Actually, your best bet is to attempt to delete only employees you've added.
When you click the Delete Employee button, the Click event procedure will call the EmpDelete procedure, as shown in the following code: Private Sub EmpDelete() Dim cmd As OleDbCommand Dim intRows As Integer Dim strSQL As String Dim strConn As String ' Build Connection String strConn = Session("ConnectString").ToString strSQL = "EmployeeDelete" Try cmd = New OleDbCommand() With cmd ' Create a Connection object .Connection = New OleDbConnection(strConn) ' Set the SQL .CommandText = strSQL .CommandType = CommandType.StoredProcedure ' Create Parameter for EmployeeID With .Parameters.Add("EmpID", OleDbType.Integer) .Value = ddlEmps.SelectedItem.Value End With ' Open the connection .Connection.Open() ' Execute the stored procedure intRows = .ExecuteNonQuery() ' Get the return value lblError.Text = "# of rows deleted = " & _ intRows.ToString() ' Reload DropDownList control EmpLoad() End With Catch exp As Exception lblError.Text = exp.Message Finally ' Close the Connection With cmd.Connection If .State = ConnectionState.Open Then .Close() End If End With End Try End Sub This code is nearly identical to the EmpAddOleDb procedure in all its "moving parts." The only real difference is in the parameters: This procedure includes only a single parameter, and because it's an input parameter (the default type), you needn't specify the direction: With .Parameters.Add("EmpID", OleDbType.Integer) .Value = ddlEmps.SelectedItem.Value End With You do need to specify the type (OleDbType.Integer) and the value (ddlEmps.SelectedItem.Value). Otherwise, the two procedures work nearly identically. TIP The order of the parameters you pass to the stored procedure is significant when you use the OleDb namespace. The first parameter must be the return value, if any. After that, you must add parameters to the Parameters collection in the same order in which they appear in the stored procedure (in this case, EmpId, FirstName, and LastName).
|
