VB.Net by API/System.Data.OleDb/OleDbDataAdapter

New OleDbDataAdapter

  
Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.rumon
Imports System.Data

Public Class MainClass
    
    Shared Sub Main()
        Dim dsUsers As New DataSet("Users")
        Try
            Dim dbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Employee.mdb")
            Dim daUsers As New OleDbDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)
            Dim dcmUserID As New DataColumnMapping("ID", "EmployeeID")
            Dim dcmFirstName As New DataColumnMapping("FirstName", "FirstName")
            Dim dcmLastName As New DataColumnMapping("LastName", "LastName")
            Dim dtmUsers As New DataTableMapping("Table", "Employee")
            dtmUsers.ColumnMappings.Add(dcmUserID)
            dtmUsers.ColumnMappings.Add(dcmFirstName)
            dtmUsers.ColumnMappings.Add(dcmLastName)
            daUsers.TableMappings.Add(dtmUsers)
            daUsers.Fill(dsUsers)
            Dim cb As New OleDbCommandBuilder(daUsers)
            Dim r As DataRow = dsUsers.Tables(0).NewRow()
            r("FirstName") = "Eddie"
            r("LastName") = "Robinson"
            dsUsers.Tables(0).Rows.Add(r)
            daUsers.Update(dsUsers.GetChanges())
            dsUsers.AcceptChanges()
        Catch ex As Exception
            dsUsers.RejectChanges()
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Class

OleDbDataAdapter.Fill

  

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Windows.Forms
Imports System.Resources
Public Class MainClass
    Shared Sub Main()
        Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employee.mdb;"
        Dim objConnection As New OleDbConnection(strConnectionString)
        Dim strSQL As String = "SELECT FirstName, LastName FROM Employee"
        Dim objCommand As New OleDbCommand(strSQL, objConnection)
        Dim objDataAdapter As New OleDbDataAdapter(objCommand)
        Dim objDataTable As New Data.DataTable("Employee")
        Dim objDataRow As DataRow
        Try
            objConnection.Open()
            objDataAdapter.Fill(objDataTable)
            For Each objDataRow In objDataTable.Rows
                Console.WriteLine(objDataRow.Item("FirstName") & " " & objDataRow.Item("LastName"))
            Next
        Catch OleDbExceptionErr As OleDbException
            Console.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Console.WriteLine(InvalidOperationExceptionErr.Message)
        End Try
        objConnection.Close()
        objDataRow = Nothing
        objDataTable.Dispose()
        objDataTable = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing
    End Sub
End Class

OleDbDataAdapter.SelectCommand

  
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Windows.Forms
Imports System.Resources
Public Class MainClass
    Shared Dim OleDbConnection As System.Data.OleDb.OleDbConnection
    Shared Sub Main()
       Connect()
       Dim OleDbSelectCommand As System.Data.OleDb.OleDbCommand
       OleDbSelectCommand = New System.Data.OleDb.OleDbCommand()
       OleDbSelectCommand.rumandText = "SELECT ID, FirstName, LastName,Salary FROM Employee"
       OleDbSelectCommand.Connection = OleDbConnection
       Dim AddressBookDataAdapter As System.Data.OleDb.OleDbDataAdapter
       AddressBookDataAdapter = New System.Data.OleDb.OleDbDataAdapter()
       AddressBookDataAdapter.SelectCommand = OleDbSelectCommand

       Dim AddressBookDataSet As System.Data.DataSet
       AddressBookDataSet = New System.Data.DataSet()
       CType(AddressBookDataSet, System.ruponentModel.ISupportInitialize).EndInit()
       AddressBookDataAdapter.Fill(AddressBookDataSet)

         Dim dataTable As DataTable = AddressBookDataSet.Tables(0)
         If dataTable.Rows.Count <> 0 Then
            Dim recordNumber As Integer = Convert.ToInt32(dataTable.Rows(0)(0))
            Console.WriteLine( recordNumber.ToString )
            Console.WriteLine( Convert.ToString(dataTable.Rows(0)(1)) )
            Console.WriteLine( Convert.ToString(dataTable.Rows(0)(2)) )
            Console.WriteLine( Convert.ToString(dataTable.Rows(0)(3)) )
            " display not-found message
         Else
            Console.WriteLine( "No record found" )
         End If

    End Sub
    Shared Sub Connect()
      OleDbConnection = New System.Data.OleDb.OleDbConnection()
      OleDbConnection.ConnectionString = _
         "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
         "User ID=Admin;Data Source=Employee.mdb;Mode=Sha" & _
         "re Deny None;Extended Properties="""";" & _
         "Jet OLEDB:System database="""";Jet OLEDB:Regis" & _
         "try Path="""";Jet OLEDB:Database Password="""";" & _
         "Jet OLEDB:Engine Type=5;Jet OLEDB:Dat" & _
         "abase Locking Mode=1;Jet OLEDB:Global Partial " & _
         "Bulk Ops=2;Jet OLEDB:Global Bulk T" & _
         "ransactions=1;Jet OLEDB:New Database " & _
         "Password="""";Jet OLEDB:Create System Databas" & _
         "e=False;Jet OLEDB:Encrypt Database=False;" & _
         "Jet OLEDB:Don"t Copy Locale on Compact=" & _
         "False;Jet OLEDB:Compact Without Replica " & _
         "Repair=False;Jet OLEDB:SFP=False"

      Try
         OleDbConnection.Open()
      Catch ex As Exception
         Console.WriteLine(ex.Message)
      End Try
    End Sub
    Shared Sub Disconnect()
      Try
         OleDbConnection.Close()
      Catch ex As Exception
         Console.WriteLine(ex.Message)
      End Try
    End Sub
End Class

OleDbDataAdapter.TableMappings.Add

  

Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.rumon
Imports System.Data

Public Class MainClass
    
    Shared Sub DoDataMappings(ByVal da As OleDbDataAdapter)
        Try
            " Define an array of column to map
            Dim dcmMappedColumns() As DataColumnMapping = { _
                      New DataColumnMapping("ID", "EmployeeID"), _
                      New DataColumnMapping("FirstName", "FirstName"), _
                      New DataColumnMapping("LastName", "LastName")}
            " Define the table containing the mapped columns
            Dim dtmUsers As New DataTableMapping("Table", "Employee", _
            dcmMappedColumns)
            " Activate the mapping mechanism
            da.TableMappings.Add(dtmUsers)
        Catch ex As Exception
            " An error occurred. Show the error message
            Console.WriteLine(ex.Message)
        End Try
    End Sub
    Shared Sub Main()
        Try
            " Define a connection object
            Dim dbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Employee.mdb")
            " Create a data adapter to retrieve records from db
            Dim daUsers As New OleDbDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)
            Dim dsUsers As New DataSet("Employee")
            DoDataMappings(daUsers)
            " Fill the dataset
            daUsers.Fill(dsUsers)
            " Go through the records and print them using the mapped names
            Dim r As DataRow
            For Each r In dsUsers.Tables("Employee").Rows
                Console.WriteLine("ID: {0}, FirstName: {1}, LastName: {2}", r("EmployeeID"), r("FirstName"), r("LastName"))
            Next
        Catch ex As Exception
            " An error occurred. Show the error message
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Class

OleDbDataAdapter.Tables

  
Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.OleDb
Imports System.Collections
Imports System.Data

Public Class MainClass
    
    Shared Sub Main()
        Try
            Dim dbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Employee.mdb")
            Dim strSELECT As String = "SELECT ID AS EmployeeID, FirstName AS FirstName, LastName AS LastName FROM Employee"
            Dim daUsers As New OleDbDataAdapter(strSELECT, dbConn)
            Dim dsUsers As New DataSet("Employee")
            daUsers.Fill(dsUsers)
            Dim r As DataRow
            For Each r In dsUsers.Tables(0).Rows
                Console.WriteLine("ID: {0}, FirstName: {1}, LastName: {2}", r("EmployeeID"), r("FirstName"), r("LastName"))
            Next
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Class