VB.Net by API/System.Data.OleDb/OleDbDataAdapter
Содержание
New OleDbDataAdapter
<source lang="vbnet"> 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
</source>
OleDbDataAdapter.Fill
<source lang="vbnet">
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
</source>
OleDbDataAdapter.SelectCommand
<source lang="vbnet"> 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
</source>
OleDbDataAdapter.TableMappings.Add
<source lang="vbnet">
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
</source>
OleDbDataAdapter.Tables
<source lang="vbnet"> 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
</source>