Writing a Simple SQLClient Class DataSet Program

Writing a Simple SQLClient Class DataSet Program

Our next sample program, SQLDataGrid, will not only show you how the DataSet object works, it will also drive home the concept of how integral XML is to ADO.NET. SQLDataGrid retrieves all the records from the Customers table of the Northwind database and displays them in a DataGrid control. We can then scroll through the records and edit them. When we're finished, we can commit the changes to the original database. We can examine the records retrieved and also look at the XML representation of the data schema that defines the table within the data set.

While the OleDB classes are more generic for ADO, we'll use the SQLClient classes for our first DataSet example. As I've mentioned, the System.Data.SQLClient classes are optimized for SQL Server 2000. However, the SQL managed provider supports named parameters and not positional parameters, so the syntax for the SQL provider is just a bit more involved than that required for the OleDB provider. Luckily, the Visual Studio .NET IDE provides a few wizards to take care of the grungy code. We have to write only a few lines to build a fully operational program. The finished product will look like Figure 10-6.

Figure 10-6

Our SQLDataGrid program in action.

Getting Started

Start a new Windows Application project in Visual Studio .NET and name it SQLDataGrid. The next step we take is to add a connection to the Northwind database. In the next chapter, we will add a connection manually, but for now let's use the Data Link Properties box.

  1. Select View | Server Explorer from the main IDE menu. The Data Connections collection holds connections to various databases we might use. We want to add a new connection to the Northwind database and then select the records in the Customers table. To add a new connection, right-click Data Connections and then click Add Connection, as shown in Figure 10-7.

    Figure 10-7

    Adding a connection to the Northwind database.

  2. We will be using our local machine in this example, so, in the Data Link Properties dialog box, enter "host" (or your SQL Server name) for the server name. Next, select the option for Use Windows NT Integrated Security. (You could also select the Use A Specific User Name And Password option and enter appropriate information such as QSUser for the user name and QSPassword for the password.) Click the Refresh button to make sure that the server's name is legitimate and you didn't make a typo. If you type the name incorrectly, when you attempt to retrieve the available databases, you will get a data link error message, shown in Figure 10-8.

    Figure 10-8

    If a server name isn't valid, you get a data link error.

    If you get a data link error, a message box notifies you that the catalog of databases for the local server can't be retrieved, as shown in Figure 10-9.

    Figure 10-9

    The database catalog error message.

    When the server name is resolved correctly, you'll see the databases installed on your PC. Click the drop-down list and select Northwind, shown in Figure 10-10.

    Figure 10-10

    Selecting the Northwind database in the Data Link Properties dialog box.

  3. To be sure that we can connect to the Northwind database, click the Test Connection button. The message box indicates that the test connection succeeded. Life is good, as Figure 10-11 clearly shows.

    Figure 10-11

    Our connection succeeded.

Before dismissing the Data Link Properties dialog box, click the Provider tab. The Microsoft OLE DB Provider for SQL Server is selected by default, as shown in Figure 10-12. You can see, however, that several other managed OLE DB providers are listed. After the .NET Framework is released and more programmers migrate to this platform, you'll see more and more providers become available. Click the OK button. The connection information required to connect to the Northwind database will be written for us.

Figure 10-12

The Provider tab of the Data Link Properties dialog box.

Now we need to add an SqlConnection object to the program.

  1. In the toolbox, click the Data tab and double-click the SqlConnection control to add a new SqlConnection object to our project. The default name is SqlConnection1.

  2. Right-click on the SqlConnection1 object and select Properties. We need to provide our new connection object with the connection string it needs to connect to the database. Click the ConnectionString drop-down box and select the Northwind connection we just created, as shown in Figure 10-13.

note

You could have first added an SqlConnection object and then selected New Connection, and the Data Link Properties dialog box from the previous procedure would have been displayed. Also note in the top drop-down box that our connection lives in the System.Data.SqlClient namespace within the .NET Framework.

Figure 10-13

Designating our connection string.

In case you were wondering, here's the code that Visual Basic .NET added on my machine when I chose the connection in the ConnectionString drop-down box. (The code will be slightly different on your machine.) Notice that the SqlConnection object's ConnectionString property shows which server to use, which database we're connecting to, some security information, and information about the workstation and packet transfer size.

Me.SqlConnection1.ConnectionString = _ "data source=localhost;initial catalog=Northwind;" & _ "integrated security=SSPI;persist security info=False;" & _ "workstation id=localhost;packet size=4096"

Adding a DataAdapter Object to Our Program

The DataAdapter is the object that connects to the database to fill the memory resident DataSet. Then the DataAdapter connects to the database again to update the data on the basis of the operations performed while the DataSet held the data.

In the past, data processing has been primarily connection-based. Now, in an effort to make multitiered applications more efficient, data processing is turning to a message-based approach that revolves around chunks of information. At the center of this approach is the DataAdapter, which provides a link between a DataSet and its data source that's used to retrieve and save data. It accomplishes these processes by means of requests to the appropriate SQL commands made against the data source.

  1. In the toolbox, double-click the SqlDataAdapter control to add a SqlDataAdapter to our program. When the SqlDataAdapter is added, the Data Adapter Configuration Wizard is displayed, as shown in Figure 10-14. Click Next, and follow the steps to configure the new SqlDataAdapter.

    Figure 10-14

    The opening screen for the Data Adapter Configuration Wizard.

  2. Select the database connection to the Northwind database we just built, as shown in Figure 10-15. (Notice that at this point you can still create a new connection by clicking New Connection.) Click Next.

    Figure 10-15

    Selecting our database connection in the wizard.

    Remember when I mentioned that the SQLClient DataAdapter is a bit trickier to set up than the OleDB DataAdapter? This is where the wizard earns its pay. We'll let the wizard add the SQL statements by selecting the Use SQL Statements option, shown in Figure 10-16. Click Next to continue.

    Figure 10-16

    Letting the wizard do the work.

  3. The next screen, shown in Figure 10-17, lets us use a standard SQL statement to select the data we want the DataAdapter to retrieve from the database. In this case, type in the SELECT statement shown in Figure 10-17, which selects all the records and all the fields from the Customers table in the Northwind database.

    Figure 10-17

    Selecting the data we want to retrieve.

  4. Click the Advanced Options button. You'll see the Advanced SQL Generation Options dialog box, shown in Figure 10-18. Leave the three options checked by default. You can see that the wizard will generate all the SQL statements for us and also take care of the details of detecting changes between the data in the database and our data set.

    Figure 10-18

    Advanced options in the wizard.

  5. Click OK to close the Advanced SQL Generation Options dialog box. Now click the Query Builder button to display the Query Builder, shown in Figure 10-19. You can see that all columns for all records are selected. If you wanted to modify the conditions for retrieving data from the database, you would do that here. Let's keep things simple for our first example and leave the SELECT statement as is.

    Figure 10-19

    The Query Builder.

  6. Click OK to close the Query Builder dialog box, and then click Next in the configuration wizard. Using our instructions, the wizard now goes to work constructing the underpinnings of the SQL connection and commands.

  7. Click Finish to dismiss the wizard, whose work is now done. We'll soon see that the work was not trivial.

I described the SqlDataAdapter as a bridge between the data source and the memory-resident data set. The wizard added the commands for that bridge, which are illustrated in Figure 10-20.

Figure 10-20

The wizard builds its bridge.

Finishing the User Interface

Before we look at the data and the program's code in more detail, let's finish the interface for our form. Add a DataGrid component from the Windows Forms tab of the toolbox to the form and also two command buttons. Set the properties for the controls as listed in Table 10-9. Your form should now look something like Figure 10-21.

Figure 10-21

Adding interface controls to our form.

Table 10-9  Properties for the SQLDataGrid Form

Object

Property

Value

Form

Text

SQLClient Example

DataGrid

Defaults

Button

Text

&Retrieve Data

Name

btnRetrieve

Button

Text

&Update Database

Name

btnUpdate

tip

Take a moment and bring up the properties sheet for the DataGrid control. Click the AutoFormat hyperlink displayed under the list of properties. An Auto Format dialog box will be displayed. Click a few of the formats to get an idea of how you can display your data. When you've finished exploring the various built-in formats, click Cancel to stick with the default view. When you start writing your own .NET database programs for production, you can add a lot of eye candy options for free.

A Sneak Preview of Our Data from the DataAdapter

Let's take a quick look at the data that we'll retrieve. Right-click on the form, and then select Preview Data. You can see the data that will be displayed when our program comes to life.

  1. In the Data Adapter Preview dialog box, be sure that SqlData Adapter1 is selected in the Data Adapters list, as shown in Figure 10-22, and then click the Fill Dataset button. This dialog box provides all sorts of information, including how large the data set will be in bytes.

    Figure 10-22

    Previewing our data in the Data Adapter Preview dialog box.

  2. Close the Data Adapter Preview dialog box, right-click the form again, and select Generate Dataset to display the Generate Dataset dialog box, shown in Figure 10-23. Accept the defaults, and then click OK.

    Figure 10-23

    The Generate Dataset dialog box.

    When you dismiss the Generate Dataset dialog box, a new DataSet object will be added to your program. Right-click on the DataSet object to display its properties dialog box, shown in Figure 10-24.

    Figure 10-24

    The properties dialog box for the new DataSet object.

    Notice the two hyperlinks at the bottom of the properties dialog box. One leads you to a view of the database schema; the other shows a view of the data set properties.

  3. Click the View Schema hyperlink. You'll see the database schema, which is the template of the table our data adapter will use to pull the records from the database. The schema is shown in Figure 10-25.

    Figure 10-25

    The database schema.

XML Schema for the Customers Table

Our program shuttles data between the data source (the Customers table in the Northwind database) and our data set via the data adapter. The schema provides the rules for how the data will be represented in our data set and the ground rules for how tables, records, and columns relate to one another.

Now either right-click the schema and select View XML Source or click the XML tab at the bottom left of the IDE. Either of these actions will display the XML representation of the schema that the data set will use. You can see that the XML schema is human readable (in other words, it is not binary). It is nested and case sensitive. The elements show each of the fields that the data set is expecting as well as the field's data type attribute.

<xsd:schema targetNamespace="http://www.tempuri.org/DataSet1.xsd" xmlns="http://www.tempuri.org/DataSet1.xsd" xmlns:xsd=http://www.w3.org/2001/XMLSchema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"> <xsd:element name="DataSet1" msdata:IsDataSet="true"> <xsd:complexType> <xsd:choice maxOccurs="unbounded"> <xsd:element name="Customers"> <xsd:complexType> <xsd:sequence> <xsd:element name="CustomerID" type="xsd:string" /> <xsd:element name="CompanyName" type="xsd:string" /> <xsd:element name="ContactName" type="xsd:string" minOccurs="0" /> <xsd:element name="ContactTitle" type="xsd:string" minOccurs="0" /> <xsd:element name="Address" type="xsd:string" minOccurs="0" /> <xsd:element name="City" type="xsd:string" minOccurs="0" /> <xsd:element name="Region" type="xsd:string" minOccurs="0" /> <xsd:element name="PostalCode" type="xsd:string" minOccurs="0" /> <xsd:element name="Country" type="xsd:string" minOccurs="0" /> <xsd:element name="Phone" type="xsd:string" minOccurs="0" /> <xsd:element name="Fax" type="xsd:string" minOccurs="0" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:choice> </xsd:complexType> <xsd:unique name="Constraint1" msdata:PrimaryKey="true"> <xsd:selector xpath=".//Customers" /> <xsd:field xpath="CustomerID" /> </xsd:unique> </xsd:element> </xsd:schema>

Just Add Code

Now that we've taken the grand tour of the SqlConnection, SqlDataAdapter, and SqlDataSet objects, we can write a few lines of code to make our program fully functional. Add the following lines of code to both the btnRetrieve and btnUpdate Click event handlers. Remember that structured error handling that would ordinarily be added to any production code is omitted for the sake of clarity.

Private Sub btnRetrieve_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRetrieve.Click SqlDataAdapter1.Fill(DataSet11, "Customers") With DataGrid1 .DataSource = DataSet11 .AllowSorting = True .AlternatingBackColor = System.Drawing.Color.Bisque .SetDataBinding(DataSet11, "Customers") End With End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click DataGrid1.Update() SqlDataAdapter1.Update(DataSet11) End Sub

Running Our Program

Go ahead and run the program. Click the Retrieve Data button. A connection is established with the data source, and the data grid is populated. The AlternatingBackColor property gives the grid a more professional look. The AllowSorting property is set to True by default, but I wanted to illustrate how easy it is to give your grid superhuman properties with a single line of code.

Click the first column heading in the grid. Notice the recessed up arrow at the right side of the heading, shown in Figure 10-26. This arrow is a cue that this column can be sorted and that it is sorted in ascending order.

Figure 10-26

Sorting our data set.

Now click the ContactName column heading. Clicking sorts the records in the data set in ascending order. Click again and our program sorts the data set in descending order. You can click the column headings a few times until you get bored. At times you might need to sort on multiple columns. For example, you might need to show all customers sorted alphabetically by city. While the data grid can sort only on a single column, DataView objects have more flexibility.

Editing Our Data

Our grid not only displays our data easily, it also permits editing. If you select the first record and make a change, as illustrated in Figure 10-27 with the ContactName column, the leftmost cell icon changes from an arrow to a pencil, indicating that the current row is being edited. The data grid and the underlying data set hold any changes we might make to the data. Because we don't currently have a connection to the underlying database, the data source is not updated—yet.

Figure 10-27

Editing data in the data grid.

If you want to commit the changes to the underlying database table, clicking the Update Database button does the trick. Be careful because clicking this button will actually change the Northwind Customers table if you commit any changes. And this update is accomplished with two lines of code! Now that is powerful.

How the Code Works

When the user clicks the Retrieve Data button, the data set is filled with the records from the database. Populating the data set is pretty simple. We call the Fill method of the SqlDataAdapter and pass in the data set to be filled and the name of the table. Remember that the data adapter is already configured with the connection and it knows the SQL statements required to extract the records from the Customers table.

Private Sub btnRetrieve_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRetrieve.Click SqlDataAdapter1.Fill(DataSet11, "Customers")

This is all the code that's required to connect to the database with the connection object, grab the data with the DataAdapter object, and fill the memory-resident data set.

Remember that the DataSet object is independent of the data source, which means it does not require a persistent connection to the database. The connection is opened to get the data and closed once the DataSet has been filled. The Fill method retrieves rows from the data source by using the SELECT statement specified by an associated SelectCommand property. In our case, the wizard wrote that statement for us. If you look at the code the wizard wrote, it simply selects each field in the Customers table for us.

Me.SqlSelectCommand1.CommandText = "SELECT CustomerID, " & _ "CompanyName, ContactName, ContactTitle, Address, " & _ "City, Region, PostalCode, Country, Phone, Fax " & _ "FROM Customers"

The Connection property of the SqlSelectCommand was also taken care of for us.

Me.SqlSelectCommand1.Connection = Me.SqlConnection1

The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is manually opened before Fill is called, it remains open. In other words, if we were to add SqlConnection1.Open to our code, we would have to explicitly close the connection. Otherwise, letting the DataAdapter open and close the connection as necessary is more efficient.

The Fill operation then adds the rows to the destination DataTable object in the DataSet, creating the DataTable objects if they do not already exist. The DataSet stores data using .NET Framework data types. For most applications, these types provide a convenient representation of data source information, as we saw in the database schema shown earlier. However, this representation might cause a problem when the data type in the data source is an SQL Server decimal. The .NET Framework decimal data type allows a maximum of 28 significant digits, while the SQL Server decimal data type allows 38 significant digits. If the SqlDataAdapter determines, during a fill operation, that the precision of an SQL Server decimal field is greater than 28 characters, the current row will not be added to the DataTable. Instead the FillError event will occur, which enables you to determine whether a loss of precision will occur and respond appropriately.

So, we just filled the DataSet with a single command. Now we want to configure our DataGrid and bind it to the data set so that our data can be displayed. We really only need to set the DataSource property and invoke the SetDataBinding method of the DataGrid object to display our information. However, we will also set the AllowSorting property to True, allowing the Data Grid to now sort individual columns. And setting the AlternatingBackColor property gives a nice aesthetic look to the final product.

With DataGrid1 .DataSource = DataSet11 .AllowSorting = True .AlternatingBackColor = System.Drawing.Color.Bisque .SetDataBinding(DataSet11, "Customers") End With

Updating the Data Source

When the user wants to commit the changes made in the data grid back to the underlying data source, clicking the Update Database button does the job in only two lines of code. The first line updates the data grid control. This operation is for cosmetic reasons. The DataGrid.Update method forces the control to paint any currently invalid areas. Next the SqlDataAdapter.Update method is called, which does the heavy lifting to update the data source. The Update method calls the respective INSERT, UPDATE, or DELETE statement for each inserted, updated, or deleted row in the specified DataSet.

Private Sub btnUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click DataGrid1.Update() SqlDataAdapter1.Update(DataSet11) End Sub

When our application calls the SqlDataAdapter object's Update method, the data adapter examines the RowState property and executes the required INSERT, UPDATE, or DELETE statement on the basis of the order of the indexes configured in the DataSet.

For example, Update might first execute a DELETE statement, followed by an INSERT statement, and then another DELETE statement because of the ordering of the rows in the DataTable that resides in the DataSet. An application can call the GetChanges method in situations in which you must control the sequence of statement types (for example, an INSERT before an UPDATE).

If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception. If you don't want to use the wizard and instead write the code yourself, you can create a SqlCommandBuilder object to automatically generate SQL statements for single-table updates by using the SelectCommand property of a .NET data provider. Then the CommandBuilder generates any additional SQL statements required. However, this magical generation logic requires key column information to be present in the DataSet.

If you feel like writing code manually and you're working with a keyed, single table, you can write the following code to generate the required SQL commands.

Dim daDataAdapter As SqlDataAdapter = _ New SqlDataAdapter("SELECT * FROM Customers", _ sqlConn) Dim sqlCommandBuilder As SqlCommandBuilder = _ New SqlCommandBuilder(daDataAdapter) '— We explicitly open the database connection. '-- Of course, we would also have to code that. sqlConn.Open() Dim dsDataSet As DataSet = New DataSet daDataAdapter.Fill(dsDataSet, "Customers") '-- Code to modify data in DataSet here. '-- Without the SqlCommandBuilder, this line would fail. custDA.Update(dsDataSet, "Customers") sqlConn.Close()

The wizard, however, added all of the commands needed. If you were to look at the code the wizard provided, you would see something like this.

Friend WithEvents SqlConnection1 As _ System.Data.SqlClient.SqlConnection Friend WithEvents SqlDataAdapter1 As _ System.Data.SqlClient.SqlDataAdapter Friend WithEvents SqlSelectCommand1 As _ System.Data.SqlClient.SqlCommand Friend WithEvents SqlInsertCommand1 As _ System.Data.SqlClient.SqlCommand Friend WithEvents SqlUpdateCommand1 As _ System.Data.SqlClient.SqlCommand Friend WithEvents SqlDeleteCommand1 As _ System.Data.SqlClient.SqlCommand Friend WithEvents DataGrid1 As _ System.Windows.Forms.DataGrid Friend WithEvents btnRetrieve As _ System.Windows.Forms.Button Friend WithEvents btnUpdate As _ System.Windows.Forms.Button Friend WithEvents DataSet11 As SQLDataGrid.DataSet1

The wizard also added the following UPDATE statement for us using the database schema we looked at earlier as its guide. Now, while a wizard many times has a dark side, in this case it really did provide much of the low-level, tedious code we would have to work with otherwise.

Me.SqlUpdateCommand1.CommandText = "UPDATE Customers " & _ "SET CustomerID = @CustomerID, CompanyName = " & _ "@CompanyName, ContactName = @ContactName, " & _ "ContactTitle = @ContactTitle, Address = Address, " & _ "City = @City, Region = @Region, PostalCode = " & _ "@PostalCode, Country = @Country, Phone = @P" & _ "hone, Fax = @Fax WHERE (CustomerID = " & _ "@Original_CustomerID) AND (Address = @Origi" & _ "nal_Address OR @Original_Address1 IS NULL AND " & _ "Address IS NULL) AND (City = @Original_City OR " & _ "@Original_City1 IS NULL AND City IS NULL) AND " & _ "(CompanyName = @Original_CompanyName) AND " & _ "(ContactName = @Original_ContactName OR " & _ "@Original_ContactName1 IS NULL AND ContactName " & _ "IS NULL) AND (ContactTitle = @Original_ContactTitle " & _ "OR @Original_ContactTitle1 IS NULL AND ContactTitle " & _ "IS NULL) AND (Country = @Original_Country OR " & _ "@Original_Country1 IS NULL AND Country IS NULL) " & _ "AND (Fax = @Original_Fax OR @Original_Fax1 IS NULL " & _ "AND Fax IS NULL) AND (Phone = @Original_Phone " & _ "OR @Original_Phone1 IS NULL AND Phone IS NULL) AND " & _ "(PostalCode = @Original_PostalCode OR " & _ "@Original_PostalCode1 IS NULL AND PostalCode IS " & _ "NULL) AND (Region = @Original_Region OR " & _ "@Original_Region1 IS NULL AND Region IS NULL); " & _ "SELECT CustomerID, CompanyName, ContactName, " & _ "ContactTitle, Address, City, Region, PostalCode, " & _ "Country, Phone, Fax FROM Customers WHERE " & _ "(CustomerID = @Select_CustomerID)"

The wizard puts in quite a bit of additional code for us. When you get a moment, poke through the form and take a look. In the next chapter, we'll write some examples using the OleDB managed provider. Luckily, these classes are easier to work with when hand coding.



Coding Techniques for Microsoft Visual Basic. NET
Coding Techniques for Microsoft Visual Basic .NET
ISBN: 0735612544
EAN: 2147483647
Year: 2002
Pages: 123
Authors: John Connell

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