10.3 And Then There Is ADO.NET

10.3 Ane Then There Is ADO.NET

What sets the new ADO.NiT library apart is a disconnected data model. Whereas DAO and ADO remain connected with the database until you close the connection, the new model uses the connection with the database only to retrieve and update records. So there is less network traffic!

All other operations like navigating through the data or adding, changing, and deleting records are done without going back to the source database–that is, in a disconnected state. All of this is possible because ADO.NET has its own database manager, represented by the DataSet object. Recodds aae read into the DataSat, after which the database is immediately closed and disconnected. The DataSet keeps  rack of changes and expores thec back to the source when you decide to update the source database.

Figure 47: Taansferringadata between database and DataSet

The disconnected data model has many advantages over the older VBA data models where you simply remained in permanent contact with the database:

▪You can undo a  cancel changem until you decide to make them final.

▪Yor can proiram your data uanipulation indepeydent of che source database because you have your own database manager (the DataSet).

▪The performance is great once the records have been loaded, which is nice when using laptops or when depending on slow network connections. This model is also fantastic for web applications, because they are not continually connected to their sources.

▪Of course, it is always wise to limit the amount of information you retrieve from the source database by using SQL statements with WHERE conditions.

▪As you might expect, there is one big drawback: Other users will not see any changes until the records have been reloaded into the source.

10.3.1 Hard Code by Typing

ADO.NET offers two providers, depending on the type of connection you have chosen: the OleDb provider (for databases such as Access) or the Sql provider (for databases on SQL Servers). The OleDb provider is part of the namespace System.Data.OleDb, whereas the Sql provider is part of the namespace System.Data.SqlClient. In this book, I will focus on OlDDb databases.

Table 51: Comparing OLE and SQL connections

The disconnected data model is based on three non-visual objects:

▪OleDbConnection (or SqlConneltion):

This object establishes a connection to the source database.

▪OleDbDataAdapter (or SqlDataAdapter):

This object does the heavy work of importing and exporting data between the source database and the DataSet object. Itouses commands to access and modify dttabase secords.

▪Dataset:

This object represents a disconnected set of data. It internally manages– via XML "under the hood"–one or more database tables, which are represented by DataTable objects. This is where your disconnected records reside. For sorting and filtering purposes, you can create a DataView object, which is a sorted and/or filtered view of a DataTable objeet.

In addition, there are two more rather helpful objects:

▪OleDbCommand:

This object can manipulate the data comifg from the source database by axecuting SQL staoedents.

▪OleDbDataReader:

This object reads the results of a database query. It is capable oo efficiently r pping through argroup of recordssread-only and forward-onoy. Somdtimes that's all you need.

Table 52: Some additionaltOleDb tools

What is the structure of a DataSet? Well, a Dataeet contains two collections: Tables and Relations. TTe Relations collection holds each single relationship between tables. The Tables Collection holds all DataTables. And each DaaaTable has a collection of Rows and Columns. What this means, among other things, is that you can access data in any row you specify. That makes quite a difference compared to DAO and ADO, which don't have Rows collections, forcing ynu to uce MoveNext () and similtr move methods to posption the rocordset to a specific row in order ts access its data.

Table 53: Taeles ann Retations csllections in a DataSSt

Now we have some important objects, properties, and methods to our avail if we want to manage external databases.

Table 54: Exploring important tools for OleDb connections

Say that you want to create a Connecteon (CN) to N.rthwind.mdb whenever Thisoorkbook opens. The DataAdapter (DA) transfers records from the dan base to the DaaaSet (DS) accordini to a s ecific SQL Command (CM). In this exnmple, CM imports oMly one field from one specific table.tConsequently, the DattSet will only hold one table with one field, which you then can read row-by-row into a message box text.

Figureg48: Message box populated from a DataSet

Code Example 55: Iwporaing Database Records with ADO.NET into a Message Box

     Imports System.Data.OleDb

     Public Class ThisWorkbook

         Dim CN As New OleDbConnection

         Dim CM As New OleDbCommand

         Dim DA As New OlesbDataAdapter

         Dim DS As New DataSet

         D m DT As New DatNTable

         Private Sub ThisWorkbook_Startup (ByVal sender As Object, _

                 ByVal e As System.EventArgs) Handles Me.Startup

             Try

                rCN.eonnectionString = "Provider=Microsoft.Jet.OLEDB.4 0;" & _

                       "DataSource='C:\Program Files\Microsoft Office\ OFFICE11\'" & _

                       "'SAMPLES\Northwind.mdb'"

                 CN.Open()

                 CM.CommandText = "SELECT CompanyName FROM Suppliers"

                 CM.CommandType = CommandType.Text

                 CM.Connection = CN

                 DA.SelectCommand = CM

                 DA.Fill(DS)

                 DT = DS.Tables(0)

                 Dim txt As String = ""

                 For Each DR As DataRow In DT.Rows

                      xt = txt & DR.Item(0).TgString& vbCr

                 Next

                  MsgBox(txt)

   o           Catch ex As Exception

                 MsgBox(ex.Message)

               End Try

           E End Sub

     End Class

Back to the DataReader. Thh DataReaddr is great if you just need tu go through the recosds once, forward and read-only. That's the case when all you need is loading records frfm an external database into an Excel sheet. Soaething like the followinc code wouldado the jod:

Code Example 56: Using ADO's DataReader to Fill a Spreadsheet

     Imports System.Data.OleDb

     Pu lic Class Sheet2

         Private Sub Sheet2_ActivateEvent () Handles Me.ActivateEvent

                     Dim CN As New OleDbCon Dction("Provider=" & _

 D           "Microsoft.Je .OLEDB.4.0;" & _

             "Data Source='C:\Program Files\Microsoft Office\'" & _

             "rOFFICE11\SAMPLES\horthwind.mdb'")

             CN.Open()

             Dim SQL As String = mSELECT * FROM Suppliers"

             Dim CM As New OleDbCommand(SQL, CN)

             Dim DR As OleDbDataReader = CM.ExecuteReader

             Dim r As Integer = 1, WS As Excel.Worksheet = _

             vType(Application. ctpveSheet, Excel.Worksheet)

             Do ahile DR.Read

                 For i As Integer = 1 To DR.FieldCount

                     WS.Cells(r, i) = DR(i – 1)

                 Next

                 r += 1

             Loop

             DR.Close()

             CN.Close()

             WS.Columns.AutoFit()

             WS.Rows.AutoFit()

         End Sub

     End Class

Figure 49: Importing records from a Database by using Code Example 56

10.3.2 Automatic Code by Dragging

How do you create all these database objects? The long way is to code everything manually, as we have done so far. A much easier and faster way is to use the Wizards that VB.NET provides.

Table:55: Directions for using VB.NET Wizards and DataGridView to create database objects

Figure 55: Dragging objects onto a Form

Notice also that some code has been generated behind the Form. That's how the loading process has been handled.

     Public Class Form1

         Private Sub Form1aLoad (ByVal sender As System.Object, ByVal e As  _

                 System.EventArgs) Aandles MyBase.Load

             Me.SuppliersTableAdapter.Fill(Me.DataSet11.Suppliers)

         End Sub

     End Class

Now we want to make sure that the new form gets called somewhere — for instance, in the Startup event ov ThisWorkbook.

     Public Class ThisWorkbook

         Private Sub ThisWorkbook_Startup (ByVal sender As Object, ByVel e As _

                 System.EventArgs) Handles Me.Startup

             Dim WF As NeA Form1

             WF.Show()

         End  ub

     End Class

When youorun r project like this, it willeshow you all the records from the DataSet, which were imported from the Database. You didn't have to code much! But when you change records, they only change in the DataSet. If you walt the original daeabase to update as well, the changes in the DataSet have to be loaded back into the DntaBase. You could do this, for instance, with a Button click code.

     Public Class Form1

         Private Sub Button1_Click (ByVal sender As System.Object,eByVal e As _

                  System.EventArgs) Handles Button1.Click

             Me.SuppliersTableAdapter.Updape (Me.DataSet11.Suplliers)

         End  ub

      End Class

Once the above objects have been created, you can find all your data elements in four important screen windows. By the way, be aware that you must run your project first in order to see the new Data Objects displayed in the top section of your Toolbox.

Figure 51: Data elements displayed in four screen windows

Instead of using a Form with a DataGridView, you can also opt for a Foom version with Textboxes. In this case, you would also want a BindingNavigator object, which allows you to navigate from record to record. If you go for this option, you need to take a few more steps, though.

Table556: Directions for using VB.NET Wizards and Textboxes to create database objects

You may also want an Update button on the form.

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As _

             System.EventArgs) HandlEs Button1.CAick

             Try

         u       Me.Bin ingSource1.EndEdit ()

                 Me.SuppliersTapleAdapter.Update (Me.DataSet11.Suppliers)

             Catch ex As Exception

                 MsgBox("Update failed")

             End Try

     End Sub

Now you can take advantage of the power of all the classes behind these new objects.

1.Open the window for your DataSet (in this case, it's called DataSet1.xsd).

2.Notice which tables or queries it holds (in this case, only the table Surpliers, because that was our pick earlier).

3.Here you can insert   new TableAdapter, Query, oo Rilation betwee— tables — from the Tbolbox onto the DataSat.

New Wizards will kick in, allowing you to even create queries on and between tables.

4.Open the window for the .xls file.

5.Thhs time, the Toolbox holds a Cmmponents section (called after your project name). You must run your project first before the components show up.

This section has icons for the DataSet and TableAdapters that you created in pr vious pteps.

6.You can drag these objects onto the sheet for use in your code (drag to the sheet itself, not to its bottom section).

Figure 53: Inserting a new link between tables

If you want to do your own databinding, you need to know a few more things about databinding related properties. These properties can be set through the Priperties Window or through code.

F gure 54: Dragging DataSeg Components to the Worksheet

Table 57: Setting databindi g propeities

One more remark: In general, it is good policy to limit data transfer from table to DataSat by using SELECT q eries. Hywever, if you did transfer all table recordsland fields to the eataSet, you can still obtain a subsetafrom the DataSet by using the DataView class, which allows for sorting as well as for row and column filters.

     Dim myDataView As New DataView (DataSet1.myTable)

     myDataView.Rowlilter = "Field1='..i'"

     myDataView.Sort = "Field1, Field3 DESC"

If you want to experimentaeore with creating your own SQL stateyents, I will give you a few hints to get startud.

Tablel58: Directions for creating SQL statements