Using Programmatically Created ADO.NET Objects

One of the most common types of Web applications is one that lets users specify one or more search arguments that determine a result set from a database. This section illustrates an approach to handling this type of application requirement. From a .NET development perspective, the sample in this section differs in an important way from the one in the preceding section. Instead of using graphical tools to specify ADO.NET objects such as Connection , DataAdapter , and DataSet , this sample specifies these objects programmatically. Specifying the objects programmatically rather than graphically requires only a little more code, but the payoff is big if you plan to reuse the approach elsewhere (for example, in another project). Simply copy the code, and your application will work in another context.

When you use graphically created ADO.NET objects, you have to re-create the steps for instantiating ADO.NET objects manually. This, of course, introduces the possibility of variations between contexts. In addition, I find it tedious to follow the graphical steps. If you are of the same mindset, you will find creating ADO.NET objects programmatically an attractive alternative. Chapters 7 through 9 illustrate multiple techniques for programmatically specifying ADO.NET objects.

Design and Operational Page Views

The sample application in this section works with the Customers table in the Northwind database. Users can generate a result set of customers by specifying a country, city, or both for the customers that they want to examine. In fact, users can type only the first letter or any number of letters at the beginning of the name of a country or city, and the application will return all matching rows from the Customers table. The application populates a DataGrid control with a result set after a user clicks the Search button. The DataGrid control shows CustomerID, CompanyName, ContactName, Phone, City, and Country column values for each row in the result set. Figure 11-6 displays a result set based on first letter arguments for both the country and city arguments.

click to expand
Figure 11-6: A result set on a Web page based on partial inputs for the country and city arguments
Note  

The column headers in the result set do not appear in alphabetical order, which is the default for graphically created ADO.NET objects. Instead, the columns appear in the order that you specify in the SELECT statement for the result set.

Figure 11-7 shows a Design view of the Web page, which is available as WebForm2.aspx in the WebDataSamples project. The DataGrid control appears below the two TextBox controls for gathering search arguments. Notice that no tray appears below the Web page because the application does not use any graphically specified ADO.NET objects. The Button control allows a user to launch a search after inputting characters in the first or second text box.

click to expand
Figure 11-7: The Design view for a Web page enabling lookups based on user inputs to either text box

The Code Behind the Web Page

The code behind the Web page for the sample application consists of three module-level declarations and a pair of event procedures ”one for the Page_Load event and the other for the click event of the Search button ( Button1 ). The three module-level statements declare and instantiate Connection , DataAdapter , and DataSet ADO.NET objects. Actually, only one of these objects ( cnn1 ) strictly requires a module-level declaration because it gets used in two or more procedures. However, it is good practice when developing applications with ADO.NET objects to declare them at the module level (unless you are sure that the ADO.NET objects have limited scope). This precaution makes it simple to use the objects in as many procedures as necessary.

The Page_Load procedure has two code blocks. One is inside the If Then statement that executes once when the Page object initially loads. This block assigns Text property values to the Button1 control and the two Label controls for the text boxes on the Web page. This code needs to run only once because the .NET Framework automatically restores the initial Text property settings on subsequent roundtrips of the Web page between the browser and the server. The cnn1 OleDbConnection object does not automatically persist between roundtrips. Therefore, the code for managing the cnn1 connection string runs each time the Page_Load event fires.

Note  

You can use Session variables to persist objects such as ADO.NET objects between roundtrips of a Web page from a browser to a server and back. See Chapter 10 for more in-depth coverage of Session variables and a code sample demonstrating their use. This section s sample does not use Session variables so that we stay focused on simple declaration techniques for ADO.NET objects.

All the major work for this sample takes place in the Button1_Click procedure, and even that code is remarkably straightforward. The most devious task is to get the CommandText property for the cmd1 OleDbCommand object specified correctly, based on the input to the text box for country ( TextBox1 ) and the text box for city ( TextBox2 ). An If...Then ElseIf...Else statement handles the assignment. With two text boxes, four possible input states exist. Three of these include having a value in one or both text boxes; the fourth state occurs when neither text box contains a value.

The Then and two ElseIf clauses handle cases in which a user makes an entry in either or both text boxes. The syntax for the CommandText property assignments shows how to use the LIKE operator. This operator permits a user to input any number of characters at the beginning of a name for a country or a city. The Else clause of the If...Then ElseIf...Else statement handles the case in which a user clicks the Search button without inputting anything in either text box. This clause writes a message to the Web page by invoking the Write method for the Response object. The message reminds the user to input something in one or both text boxes. After displaying the message in the Else clause, the procedure clears the data source for the DataGrid control and binds the control to the data source. These steps clear any prior result set values showing in the DataGrid for validly entered country or city arguments.

Note  

The LIKE operator in the CommandText SQL statement uses a percent sign (%) wildcard symbol to designate the asterisk (*) wildcard symbol that Access developers regularly use. ADO uses the % wildcard symbol in a similar fashion.

If a user makes a valid entry in either text box, the procedure then applies the cmd1 object in code following the If...Then ElseIf...Else statement. Putting the cmd1 object to use involves two tasks . First, the procedure fills the local Customers DataTable object in the das1 dataset with a subset of the rows selected by the CommandText property for the cmd1 object. Next, the procedure binds the DataGrid1 control to the DataTable object in the das1 dataset. Because this sample uses an untyped dataset declared at run time, it features a different syntax than the preceding sample for designating a DataTable object within a dataset. The prior sample used a dot notation ( datasetname . datatablename ) because it relied on a typed dataset declared at run time. The correct syntax for this sample references the Tables collection for the das1 dataset with an index designating the name of the DataTable object ( Customers ).

 Dim cnn1 As New OleDb.OleDbConnection() Dim dap1 As New OleDb.OleDbDataAdapter() Dim das1 As New System.Data.DataSet() Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load When page first opens If Not IsPostBack Then Button1.Text = "Search" Label1.Text = "Country" Label2.Text = "City" End If Re-assign connection string for current opening of the page Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 &= _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" cnn1.ConnectionString = str1 End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Assign CommandText property value based on Text property values for TextBox1 and/or TextBox2; if Text property is missing for both controls, display message to user asking to specify at least one Text property Dim cmd1 As New OleDb.OleDbCommand() With cmd1 .Connection = cnn1 If TextBox1.Text <> "" And TextBox2.Text = "" Then .CommandText = _ "SELECT CustomerID, CompanyName, " & _ "ContactName, Phone, City, Country " & _ "FROM Customers " & _ "WHERE Country LIKE " & TextBox1.Text & "% " ElseIf TextBox1.Text = "" And TextBox2.Text <> "" Then .CommandText = _ "SELECT CustomerID, CompanyName, " & _ "ContactName, Phone, City, Country " & _ "FROM Customers " & _ "WHERE City LIKE " & TextBox2.Text & "% " ElseIf TextBox1.Text <> "" And TextBox2.Text <> "" Then .CommandText = _ "SELECT CustomerID, CompanyName, " & _ "ContactName, Phone, City, Country " & _ "FROM Customers " & _ "WHERE Country LIKE " & TextBox1.Text & _ "% AND " & _ "City LIKE " & TextBox2.Text & "% " Else Response.Write( _ "Please input the beginning of a city or " & _ "a country.") das1.Clear() DataGrid1.DataBind() Exit Sub End If End With Fill Customers DataTable object in dap1 DataAdapter object dap1.SelectCommand = cmd1 dap1.Fill(das1, "Customers") Bind DataGrid1 control to Customers DataTable object DataGrid1.DataSource = (das1.Tables("Customers")) DataGrid1.DataBind() End Sub 
 


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