Programming Dynamic Data Access

At least two techniques for programming dynamic data access allow a user to specify the result set at run time. First, you can use a SQL string with a variable as an argument for an OleDbCommand command. By letting users control the value of the variable, you can make the result set from the SQL string dynamic at run time. Second, you can add one or more parameters to an OleDbCommand object and use the values for these parameters as inputs to SQL strings or even queries in an Access database. The second approach is dynamic because users can set the parameter values at run time. The Access queries can include both data access queries (those that return a result set) and action queries (such as those for inserts , updates, and deletes).

I have found that most developers consider using parameters less friendly than using variables in SQL strings. Nevertheless, using parameters is a more robust approach. You can use parameters in a wider variety of situations, including data manipulation via data adapters. The final section in this chapter demonstrates this use of parameters with data adapters. This section s coverage of parameters will give you valuable background you need to deal with them later in the chapter.

Figure 8-4 shows the Design view as well as the initial operational view of Form3 in the ADONETSamples project. The code behind Form3 demonstrates coding techniques for the two dynamic data access approaches described a moment ago. As with Form2 in the preceding section, the form load event transforms the Design view of Form3 into the initial operational view of the form. The click event procedures for Button1 and Button2 demonstrate ways of using SQL strings with variables to implement dynamic data access. The click event procedures for Button3 and Button4 illustrate techniques of using parameters to achieve dynamic data access. The text boxes on Form3 allow for user input to the SQL strings and parameters for OleDbCommand objects.

click to expand
Figure 8-4: The text boxes facilitate the specification of values by users for data access, and the click event procedures for the buttons implement dynamic data access with the values in the text boxes.

Dynamic Data Access with a SQL String and a Variable

The click event procedure for Button1 reads the Text property for TextBox1 and uses that value for a variable in a SQL string. The Button1_Click procedure starts by instantiating the OleDbConnection and OleDbCommand objects. The procedure uses the connection string to point the OleDbConnection object at the Northwind database. Next, the procedure copies the Text property value for TextBox1 to the str1 string variable and uses this variable in a string expression to specify a SQL statement for a property of the cmd1 object. The SQL statement counts the number of ProductID values within a CategoryID value. Because the SQL statement returns a scalar value, the procedure uses the ExecuteScalar method for cmd1 . The sample copies the returned scalar value to int1 and concludes by displaying a message box with the number of products in the specified category and closing the connection.

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Declare and instantiate Connection and Command objects Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb") Dim cmd1 As New System.Data.OleDb.OleDbCommand() Save the Text property value of TextBox1 for easy re-use Dim str1 = TextBox1.Text Assign Connection and CommandText property values for the cmd1 OleDbCommand object; notice CategoryID value in WHERE clause depends on Text property for TextBox1 With cmd1 .Connection = cnn1 .CommandText = _ "SELECT Count(CategoryID) FROM Products " & _ "WHERE CategoryID = " & str1 End With Save result set from command in int1 memory variable cnn1. Open () Dim int1 As Integer = cmd1.ExecuteScalar Display result from query MsgBox("The number of products in " & _ "category " & str1 & " is " & _ int1.ToString & ".") Close connection cnn1.Close() End Sub 

The sample code demonstrates dynamic data access because a user can input any value from 1 through 8 in TextBox1 and the procedure will open a message box showing the number of products in the category. Figure 8-5 shows Form3 with a value of 6 in TextBox1 just before a click to the Basic Data Command control ( Button1 ), along with the message box that the Button1_Click procedure returns after the click. The values 1 through 8 are the original CategoryID values from the Northwind database. Entering CategoryID values outside the range of CategoryID values in the Products table into TextBox1 returns a message box that says the category contains zero products.

click to expand
Figure 8-5: A form demonstrating dynamic data access from an Access database with a Windows form in a Visual Basic .NET project

Validating a String Variable Before Using It

If a client has ever accused you of building a system that doesn t work even though it performed all the tasks that you both agreed on, you will appreciate the value of validating user input before using it. Users have a way of entering values that an application does not expect.

Resolving validation issues often has at least a couple of approaches, such as checking data values before a program uses them or catching an Exception object after the error happens. This sample performs a couple of checks on the values in TextBox1 to confirm that it is a valid CategoryID value from the Categories table. The validation occurs in the click event procedure for Button2 . To help perform data validation, the Button2_Click procedure calls a separate Function procedure (MaxMinID) that returns either the minimum or maximum CategoryID value depending on the value of a Boolean variable that the Button2_Click event procedure passes it.

The click event procedure for Button2 is an adaptation of the Button1_Click procedure. Therefore, the following listing shows the new code in boldface to make it easy to identify. The listing also shows the MaxMinID Function procedure in boldface because it is also new relative to the sample code for Button1. The new code in the Button2_Click procedure starts by saving the maximum CategoryID value in MaxCatID and the minimum CategoryID value in MinCatID. Then, the procedure performs as many as two tests on the str1 String variable before using the value in the expression for the SQL statement that defines the CommandText property for the OleDbCommand object defined and manipulated within the Button2_Click procedure. The first test uses the IsNumber method for the Char structure to see whether the value in str1 represents a number. If str1 s value passes this test, the procedure then determines whether str1 s value represents a quantity that is less than the minimum CategoryID value or greater than the maximum CategoryID value. Failing either of these tests aborts the normal flow of the procedure and presents a message box with a reminder about entering legitimate values. Aside from these extra lines of code in the Button2_Click procedure and the MaxMinID Function procedure, this sample duplicates the code for Button1.

 Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Declare and instantiate Connection and Command objects Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb") Dim cmd1 As New System.Data.OleDb.OleDbCommand() Save the Text property value of TextBox1 for easy re-use Dim str1 = TextBox1.Text  Save Minimum and Maximum CategoryID values for   re-use without re-computing function   Dim MaxCatID As Integer = MaxMinID(True)   Dim MinCatID As Integer = MaxMinID(False)   First test for validity as a number, then test   for number in right range   If Char.IsNumber(str1) = False Then   MsgBox("Please enter a valid number for "  &  _   "CategoryID.", , "Number check")  Exit Sub ElseIf CInt(str1) < MinCatID Or _ CInt(str1) > MaxCatID Then MsgBox("Please enter a number between " & _ MinCatID.ToString & " and " & MaxCatID.ToString & ".") Exit Sub End If Assign Connection and CommandText property values for the cmd1 OleDbCommand object; notice CategoryID value in WHERE clause depends on Text property for TextBox1 With cmd1 .Connection = cnn1 .CommandText = _ "SELECT Count(CategoryID) FROM Products " & _ "WHERE CategoryID = " & str1 End With Save result set from command in int1 memory variable cnn1.Open() Dim int1 As Integer = cmd1.ExecuteScalar Display result from query MsgBox("The number of products in " & _ "category " & str1 & " is " & _ int1.ToString & ".") Close connection cnn1.Close() End Sub Function MaxMinID(ByVal Max As Boolean) As Integer Declare and instantiate Connection and Command objects Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb") Dim cmd1 As New System.Data.OleDb.OleDbCommand() cnn1.Open() Assign Connection and CommandText property values for the cmd1 OleDbCommand object cmd1.Connection = cnn1 Return the highest or lowest CategoryID value from the Categories table If Max = True Then cmd1.CommandText = _ "SELECT CategoryID FROM Categories " & _ "ORDER BY CategoryID DESC" Else cmd1.CommandText = _ "SELECT CategoryID FROM Categories" End If Save result set from command in int1 memory variable Dim int1 As Integer = cmd1.ExecuteScalar Return result Return int1 Close connection cnn1.Close() End Function 

Figure 8-6 contrasts the operation of the Button1_Click procedure with that of the Button2_Click procedure. The form on the left shows TextBox1 with a value of 9. This is outside the original range of CategoryID values for the Northwind database. Clicking Button1 returns a message box that resembles the top one on the right side of Figure 8-6. Clicking Button2 displays a message box that resembles the bottom one on the right side of the figure. Notice that the bottom message does not return zero as the count for a nonexistent category. Instead, the message reminds the user to enter a valid number. It is good programming practice to validate user input in production applications. The samples in this book infrequently demonstrate data validation because they are tutorial in nature and are intended to demonstrate a concept other than data validation.

click to expand
Figure 8-6: Clicking the Validate Before Exec button ( Button2 ) reminds you to designate a valid CategoryID value instead of returning a count of the products in a nonexistent category, which happens when you click the top button on the form.

Dynamic Data Access with a SQL String and a Parameter

Instead of using a string variable, you can use a parameter to specify a value for a SQL statement at run time. The parameter can accept input from a text box just as a string variable can. You can use parameters directly with OleDbCommand objects or with Command objects associated with data adapters. This section describes and demonstrates how to add, specify, and use parameters with OleDbCommand objects.

OleDbCommand objects have a collection of OleDbParameter objects available through the OleDbCommand Parameters property. Your applications can add parameters to this collection with the Add method. At a minimum, you must specify a parameter name and data type when adding a parameter to the collection for a command. You can refer to individual parameters within the parameters collection by name or position. This allows you to specify an empty string as the name for a parameter and then refer to an individual parameter by its position in the Parameters collection. Whether you reference parameters by name or position, the members of the OleDbParameterCollection object align positionally with parameters in the SQL statement for the CommandText property of an OleDbCommand .

As you specify a parameter that you are adding to the Parameters collection for a command, you designate the System.Data.OleDb namespace. If you are working in a Windows application, you can specify this namespace by just designating OleDb because the System.Data namespace is referenced automatically for a Windows application project. Follow the namespace with a period, the enumeration OleDbType , and another period, and IntelliSense will come to your aid by displaying a list of available OleDbType enumeration values. Select the value that matches the type of data you are specifying for your parameter.

Table 8-1 shows selected OleDbType enumeration values that match Visual Basic .NET and common language runtime (CLR) data types. These mappings are not perfect in all cases. For example, the starting date for the Date enumeration member in OleDbType is December 30, 1899, but the starting date for the Visual Basic .NET Date data type is January 1, 0001. Another example of a minor variance is the mapping of the Char data type in Visual Basic .NET to the LongVarWChar member in OleDbType . The Char data type represents a single Unicode character, but the LongVarWChar enumeration represents a variable- length string of one or more Unicode characters .

Table 8-1: Selected OleDbType Enumeration Members Matching Visual Basic .NET Data Types and CLR Data Types

Visual Basic .NET Data Type

CLR DataType

OleDbType Enumeration Member

Byte

System.Byte

UnsignedTinyInt

Short

System.Int16

SmallInt

Integer

System.Int32

Integer

Long
(long integer)

System.Int64

BigInt

Decimal

System.Decimal

Decimal

Single
(single-precision floating point)

System.Single

Single

Double
(double-precision floating point)

System.Double

Double

Char

System.Char

LongVarWChar

String
(variable length)

System.String (class)

LongVarWChar

Date

System.DateTime

Date

Boolean

System.Boolean

Boolean

Object

System.Object (class)

PropVariant

Note  

See the OleDbType Enumeration topic in the Microsoft Visual Studio .NET Documentation for more OleDbType data types and more details on the data types.

The following listing for the Button3_Click procedure tackles the same task as the click event procedure for Button1 . The only differences are in how you specify a variable value for a SQL statement assigned to the CommandText property for an OleDbCommand object and how you add, specify, and assign a value to the parameter for a command. The lines of code performing these tasks appear in boldface. The SQL statement assignment for the CommandText property uses a question mark (?) to denote parameters. You can optionally assign a name, such as @InCategoryID . Visual Basic .NET refers to the parameters in the SQL statement for the CommandText property by position. Therefore, any name you assign to a parameter in a SQL statement is for your own convenience. Within the Parameters collection for a command, Visual Basic .NET can distinguish between parameters by name or position. The Button3_Click procedure demonstrates how to use a name to refer to a parameter. The Add method for the Parameters collection assigns a name ( @InCategoryID ) to the new parameter, and the Value property applies to a member in the Parameters collection indexed by its name ( @InCategoryID ). Aside from these minor adjustments for using parameters, the code and underlying operation of the Button3_Click procedure directly match that for the Button1_Click procedure.

 Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Declare and instantiate Connection and Command objects Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb") Dim cmd1 As New System.Data.OleDb.OleDbCommand() Save the Text property value of TextBox1 for easy re-use Dim str1 = TextBox1.Text With cmd1 Assign Connection and CommandText property values for the cmd1 OleDbCommand object; notice CategoryID value in WHERE clause depends on Text property for TextBox1 .Connection = cnn1  .CommandText = _   "SELECT Count(CategoryID) FROM Products "  &  _   "WHERE CategoryID = ?"   Assign parameter value based on TextBox1; you must assign   a name, data type and value for your parameter (other   OleDb parameter property value assignments are optional)   .Parameters.Add("@InCategoryID", OleDb.OleDbType.Integer  )  .Parameters("@InCategoryID").Value = TextBox1.Text  End With Save result set from command in int1 memory variable cnn1.Open() Dim int1 As Integer = cmd1.ExecuteScalar Display result from query MsgBox("The number of products in " & _ "category " & str1 & " is " & _ int1.ToString & ".") Close connection cnn1.Close() End Sub 

Dynamic Data Access with an Access Query and Parameters

Using strings to specify SQL statements for the CommandText property of an OleDbCommand object creates code that is easy to read and understand. However, you can achieve superior performance by using a previously compiled query in an Access database file. When Access has a query that takes parameters (known as a parameter query ), you can specify it with two OleDbCommand properties. Assign the query s name to the CommandText property value. Use the StoredProcedure enumeration member for a CommandType property assignment. Then, assign parameters to the OleDbCommand object. This approach saves compilation time for a query, lets you take advantage of the built-in query designer in Access, builds on familiar techniques, and transfers fewer bytes between the Visual Basic .NET application file and the Access database file.

The Sales By Year query in the Northwind database was initially designed to be called by the form named Sales By Year Dialog. The form includes a couple of text boxes that allow users to specify beginning and ending dates for the orders in a year (or any date range). The SQL view for the Sales By Year query shows the following representation of the query statement. Notice that the statement declares two parameters in its Parameters clause and then uses those parameters in the WHERE clause of the SQL SELECT statement. The parameters point to controls for inputting the beginning and ending dates on the Sales By Year Dialog form. The query returns four fields for each order that falls between the beginning and ending dates. The first of these fields is the shipped date for an order. The first row in the result is the earliest shipped order between the beginning and ending dates. Because the ExecuteScalar method returns the first column value from the first row in a result set, the ExecuteScalar method for an OleDbCommand object based on the Sales By Year query returns the shipped date for the first order in a date range.

 PARAMETERS [Forms]![Sales by Year Dialog]![BeginningDate] DateTime, [Forms]![Sales by Year Dialog]![EndingDate] DateTime; SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, Format([ShippedDate],"yyyy") AS [Year] FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID WHERE (((Orders.ShippedDate) Is Not Null And (Orders.ShippedDate) Between [Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year Dialog]![EndingDate])); 

Notice in Figure 8-7 that Form3 contains two text boxes for specifying beginning and ending dates. These text boxes allow a user to specify input parameter values for the Sales By Year query. Clicking Button4 invokes a click event procedure that passes the parameter values in the text boxes to the Northwind database file, invokes the query, saves the first column value from the first row, and displays the saved result in a text box. Because the query name includes blanks, you must enclose the name in square brackets when specifying it as the value for the cmd1 CommandText property. Instead of designating parameters by name, the assignment of parameter values uses position indexes. The Text property for TextBox2 is passed to the first parameter, and the Text property for TextBox3 is passed to the second parameter. The first and second parameter positions correspond to the Access form controls referenced in the Jet SQL statement for the query, which you saw a moment ago.

click to expand
Figure 8-7: Using parameters for an OleDbCommand object, your Visual Basic .NET applications can pass values to an Access parameter query.
 Private Sub Button4_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button4.Click Declare and instantiate Connection and Command objects Dim cnn1 As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb") Dim cmd1 As New System.Data.OleDb.OleDbCommand() With cmd1 Assign Connection and CommandText property values for the cmd1 OleDbCommand object .Connection = cnn1 .CommandType = CommandType.StoredProcedure .CommandText = "[Sales by Year]" Assign parameter values based on position from TextBox2 and TextBox3; you must assign a name (even a zero-length string), data type and value for your parameter (other OleDb parameter property value assignments are optional) .Parameters.Add("", OleDb.OleDbType.Date) .Parameters(0).Value = TextBox2.Text .Parameters.Add("", OleDb.OleDbType.Date) .Parameters(1).Value = TextBox3.Text End With Save result set from command in date1 memory variable cnn1.Open() Dim date1 As Date = cmd1.ExecuteScalar Display first ShippedDate in result set with Short Date format MsgBox(Format(date1, "Short Date")) Close connection cnn1.Close() End Sub 

Figure 8-7 shows this sample in operation. The beginning and ending dates are the first and last calendar days in the year 1996. Clicking Button4 opens a message box that shows the shipped date for the first order in the year. The sample application formats the date as a Short Date . If you require the day of the week as well as the month, day, and year, you can specify a Long Date format type for the date1 argument in the Format function.

 


Programming Microsoft Visual Basic. NET for Microsoft Access Databases
Programming Microsoft Visual Basic .NET for Microsoft Access Databases (Pro Developer)
ISBN: 0735618194
EAN: 2147483647
Year: 2006
Pages: 111
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net