Namespaces

System.Data.OleDb – contain classes to connect with and modify any datasource (both databases and XML datasources)

 

System.Data.SqlClient  - similar to OleDb above but optimized to work with Microsoft SQL Sever databases

 

 

Classes

System.Data.DataSet - class that consists of a set of DataTables and relationships among the DataTables. Mirrors the structure of a relational databases but is disconnected from the database

 

System.Data.OleDb.OleDbConnection – class that provides a connection to a datasource

 

System.Data.OleDb.OleAdapter – a class that can populate a DataSet class from a datasource


System.Data.OleDb.OleDbCommand – Class containing an SQL command to be executed on a datasource (this class does not cache results in a DataSet). Each OleDbCommand must be given a OleDbConnection to connect to the datasource

Example simple connection and retrieval

Connect to a DB2 datasource, execute the SQL entered in a text box and display the results to a DataGrid View.

 

 

Public Class DB2Example

    Friend WithEvents db2Connection As System.Data.OleDb.OleDbConnection

    Friend WithEvents db2DataAdapter As System.Data.OleDb.OleDbDataAdapter

    Friend WithEvents db2Command As System.Data.OleDb.OleDbCommand

    Friend WithEvents db2DataSet As System.Data.DataSet

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

       

        db2Connection = New System.Data.OleDb.OleDbConnection("Provider=IBMDADB2.1;User id=xxxxx;Password=yyyyy;Data Source=DSNP")

        db2DataAdapter = New System.Data.OleDb.OleDbDataAdapter()

        db2DataSet = New System.Data.DataSet()

 

    End Sub

 

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim table As New DataTable

 

        Try

            db2Connection.Open()

            db2Command = New System.Data.OleDb.OleDbCommand(tbSQL.Text.ToString)

            db2Command.Connection = db2Connection

            db2DataAdapter.SelectCommand = db2Command

         ‘ execute the command and fill the DataTable with results

            db2DataAdapter.Fill(table)

            dgvDataGrid.DataSource = table

 

  ‘ alternatively could have filled dataset and then passed first table to

  ‘ datagrid

    ‘db2DataSet.Clear()

    ‘db2DataAdapter.Fill(db2DataSet)

    ‘dgvDataGrid.DataSource = db2DataSet.Tables(0)

 

        Catch oleDbException As System.Data.OleDb.OleDbException

            MsgBox("Exception caught.")

 

        Finally

            db2Connection.Close()

 

        End Try

 

    End Sub

End Class

SQL commands and parameters

Much of this can be generated via the GUI tool

 

 

A simple select statement

 

Friend WithEvents oleDbSelectCommand  As System.Data.OleDb.OleDbCommand

 

oleDbSelectCommand = New System.Data.OleDb.OleDbCommand()

 

oleDbSelectCommand.CommandText = “Select emplname from employees where emplid =  ?”

 

oleDbSelectCommand.Parameters.Add (New System.Data.OleDb.DbParameter(“emplid”, System.Data.OleDb.OleDbType.Char, 10, System.Data.ParameterDirection.Input, False, CType(0,Byte), Ctype(0,Byte), “emplid”, System.Data.DataRowVersion.Current, Nothing))

 

A simple update statement

 

Friend WithEvents updateSQL  As System.Data.OleDb.OleDbCommand

 

updateSQL = New System.Data.OleDb.OleDbCommand()

 

updateSQL.CommandText = “Update employees Set emplid = ? , emplname = ? where emplid = ?”

 

updateSQL.Add (New System.Data.OleDb.DbParameter(“emplid”, System.Data.OleDb.OleDbType.Char, 10, System.Data.ParameterDirection.Input, False, CType(0,Byte), Ctype(0,Byte), “emplid”, System.Data.DataRowVersion.Current, Nothing))

 

updateSQL.Add (New System.Data.OleDb.DbParameter(“emplname”, System.Data.OleDb.OleDbType.Char, 30, System.Data.ParameterDirection.Input, False, CType(0,Byte), Ctype(0,Byte), “emplname”, System.Data.DataRowVersion.Current, Nothing))

 

updateSQL.Add (New System.Data.OleDb.DbParameter(“original_emplid”, System.Data.OleDb.OleDbType.Char, 10, System.Data.ParameterDirection.Input, False, CType(0,Byte), Ctype(0,Byte), “emplid”, System.Data.DataRowVersion.Original, Nothing))

 

dataAdapter.UpdateCommand = updateSQL

 

‘ ExecuteNonQuery for insert/update/delete

dataAdapter.UpdateCommand.ExecuteNonQuery()

 

Problem with a DataSet?

After a DataSet has been Filled, make sure to call DataSet.Clear before calling DataSet.Fill() again

Iterating through a DataSet

Assuming DataSet.Fill has been previously called.

Remember a DataSet looks and acts like a table(or set of tables, relationships, etc.) but is disconnected from the original datasource. So you can iterate through it to as you wish.

 

‘ Get the first table in the DataSet

Dim dataTable as DataTable = dataSet.Tables(0)

Dim recordNumber As Integer

 

For recordNumber = 0 to dataTable.Rows.Count

   Try

‘ indexing is 0 based in  dataTable.Rows(rowNumber)(columnNumber)

     Emplname  = Convert.ToString(dataTable.Rows(0)(0))

Next

 

Iterating through a DataTable and updating datasource with changes

A DataSet was filled from a datasource and the table in the DataSet was displayed in a DataGridView for viewing and updating. The following code updates the datasource when a change is found in the table

 

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim row As System.Data.DataRow

        Dim i As Integer

        Try

            dbUpdateCommand = New System.Data.OleDb.OleDbCommand("update employees set name = ? where emplid = ?")

            dbConnection = getdbConnection()

            dbConnection.Open()

            dbUpdateCommand.Connection = dbConnection

            For i = 0 To table.Rows.Count - 1

                row = table.Rows(i)

                If (row.RowState = DataRowState.Modified) Then

                    dbUpdateCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("name", System.Data.OleDb.OleDbType.Char, 30))

                    dbUpdateCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("emplid", System.Data.OleDb.OleDbType.Char, 11))

                    dbUpdateCommand.Parameters.Item(0).Value = row(1).ToString

                    dbUpdateCommand.Parameters.Item(1).Value = row(0).ToString

                    dbUpdateCommand.ExecuteNonQuery()

 

                End If

 

            Next

 

        Catch oleDbException As System.Data.OleDb.OleDbException

            MsgBox("Exception caught." & oleDbException.ToString)

        Finally

            dbConnection.Close()

        End Try

    End Sub

Writing XML from a DataSet 

‘ The following assumes the Dataset has already been filled.

 

myDataSet.WriteXML(“xmlfilename.xml”)

 

 

‘ More often you would write the data using XSLT Transform

‘ Add dataset has been filled with results of 2 queries, emloyees and dependents.

‘ The tables in the DataSet are joined by emplid

dataSet.Relations.Add("EmpDeps", _

dataSet.Tables("Employees").Columns("EmplId"), _

dataSet.Tables("Dependents").Columns("EmplidID")).Nested = true

 

Dim xmlDataDocument As XmlDataDocument = New XmlDataDocument(dataSet)

Dim xslTransform  As XslTransform = New XslTransform.xslTran.Load("transform.xsl")

 

Dim xmlTextWriter As XmlTextWriter = New XmlTextWriter( _

  "xslt_employee_dependents.html", System.Text.Encoding.UTF8)

 

xslTransform.Transform(xmlDataDocument, Nothing, writer)

xmlTextWriter.Close()