2.1使用“提供者类”传输数据
2.1.1 DbConnection对象
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Imports System.Data.SqlClient
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = "server=.;database=pubs;trusted_connection=true"
connection.Open()
'做许多很酷的工作
connection.Close()
2.1.1.1 配置ODBC连接字符串
2.1.1.2 配置OLEDB连接字符串
2.1.1.3 配置SQLSERVER连接字符串
persist security info=false;
integrated security=sspi;
database=northwind;
server=localhost;
network library=dbmssocn;
data source=10.1.2.3,1433;
initial catalog=MydbName;
user id=myusername;
password=xlddd;
data source=.\sqlexpress;
attachdbfilename=c:\pubs.mdf;
user instance=true;
data source=.\sqlexpress;
attachdbfilename=|dataDirectory|\pubs.mdf;
integrated security=true;
user instance=true;
If path = String.Empty Then
path = AppDomain.CurrentDomain.BaseDirectory
End If
return path
2.1.1.4 将连接字符串保存到应用程序配置文件中
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim name As String = pubs.Name
Dim provider As String = pubs.ProviderName
Dim cnString As String = pubs.ConnectionString
2.1.1.5 GetSchema方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Imports System.Data.Common
Imports System.Data.SqlClient
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
connection.Open()
Dim schema As DataTable = connection.GetSchema
connection.Close()
DataGridView1.DataSource = schema
在选中不同行时,用数据填充第二个DataGridView
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Dim schema As DataTable = DataGridView1.DataSource
Dim currentRow As DataRow = schema.Rows(DataGridView1.CurrentCell.RowIndex)
Dim collectionName As String = currentRow("CollectionName")
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
connection.Open()
Dim schema2 As DataTable = connection.GetSchema(collectionName)
connection.Close()
DataGridView2.DataSource = schema
End Sub
2.1.2 DbCommand对象
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Imports System.Data.Common
Imports System.Data.SqlClient
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "uspGetCustomerById"
2.1.2.1 DbParameter对象
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Imports System.Data.Common
Imports System.Data.SqlClient
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "uspGetCustomerById"
Dim parm As DbParameter = cmd.CreateParameter
parm.ParameterName = "@Id"
parm.Value = "AROUT"
cmd.Parameters.Add(parm)
2.1.2.2 ExecuteNonQuery方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE sales SET qty = qty + 1 WHERE qty > 50"
connection.Open()
Dim count As Integer = cmd.ExecuteNonQuery
connection.Close()
2.1.2.3 ExecuteScalar方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT COUNT(*) FROM Sales"
connection.Open()
Dim count As Integer = cmd.ExecuteScalar
connection.Close()
MessageBox.Show(count.ToString)
2.1.2.4 ExecuteReader方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT stor_id, ord_num FROM Sales"
connection.Open()
Dim rdr As DbDataReader = cmd.ExecuteReader
While rdr.Read
MessageBox.Show(rdr("stor_id") & ": " & rdr("ord_num"))
End While
connection.Close()
2.1.3 DbDataReader 对象
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT pub_id, pub_name FROM publishers"
connection.Open()
Dim rdr As DbDataReader = cmd.ExecuteReader
Dim publishers As New DataTable
publishers.Load(rdr, LoadOption.Upsert)
connection.Close()
2.1.4 使用多个活动结果集对单个连接执行多条命令
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
pubs = ConfigurationManager.ConnectionStrings("PubsDataMars")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT stor_id, stor_name FROM stores"
connection.Open()
Dim rdr As DbDataReader = cmd.ExecuteReader
While rdr.Read
Dim salesCmd As DbCommand = connection.CreateCommand
salesCmd.CommandType = CommandType.Text
salesCmd.CommandText = "SELECT SUM(qty) FROM sales where (stor_id = @storeId)"
Dim parm As DbParameter = cmd.CreateParameter
parm.ParameterName = "@storeId"
parm.Value = rdr("stor_id")
salesCmd.Parameters.Add(parm)
Dim qtySales As Object = salesCmd.ExecuteScalar
MessageBox.Show(rdr("stor_name").ToString() & ": " & qtySales.ToString())
End While
connection.Close()
2.1.5 使用SqlBulkCopy对象执行批量复制操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim bulkCopy As ConnectionStringSettings
bulkCopy = ConfigurationManager.ConnectionStrings("BulkCopy")
Dim bulkCopyConnection As DbConnection = New SqlConnection
bulkCopyConnection.ConnectionString = bulkCopy.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT stor_name FROM stores"
connection.Open()
bulkCopyConnection.Open()
Dim rdr As DbDataReader = cmd.ExecuteReader
Dim bc As New SqlBulkCopy(bulkCopyConnection)
bc.DestinationTableName = "StoreList"
bc.WriteToServer(rdr)
connection.Close()
bulkCopyConnection.Close()
MessageBox.Show("Done with bulk copy")
End Sub
2.1.6 DbDataAdapter对象
2.1.6.1 使用Fill方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As SqlCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT pub_id,pub_name FROM publishers"
Dim pubsDataSet As New DataSet("Pubs")
Dim da As New SqlDataAdapter(cmd)
da.Fill(pubsDataSet, "publishers")
2.1.6.2 使用Update方法将修改结果保存到数据库中
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As SqlCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM publishers"
Dim pubsDataSet As New DataSet("Pubs")
Dim da As New SqlDataAdapter(cmd)
Dim bldr As New SqlCommandBuilder(da)
da.Fill(pubsDataSet, "publishers")
'modify data here
pubsDataSet.Tables("publishers").Rows(0)("pub_name") = "Hello"
pubsDataSet.Tables("publishers").Rows.Add("9911", "tailspin Toys", "Paris", Nothing, "France")
da.Update(pubsDataSet, "publishers")
MessageBox.Show("Update Complete")
2.1.6.3 将修改结果批量保存到数据库中
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Public WithEvents da As New SqlDataAdapter
Public sb As New StringBuilder
Private Sub da_RowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles da.RowUpdated
sb.Append("Rows: " & e.RecordsAffected.ToString & vbCrLf)
End Sub
Private Sub btnUpdateBatch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateBatch.Click
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim connection As DbConnection = New SqlConnection
connection.ConnectionString = pubs.ConnectionString
Dim cmd As SqlCommand = connection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM publishers"
Dim pubsDataSet As New DataSet("Pubs")
da.SelectCommand = cmd
Dim bldr As New SqlCommandBuilder(da)
da.Fill(pubsDataSet, "publishers")
'modify data here
For Each dr As DataRow In pubsDataSet.Tables("publishers").Rows
dr("pub_name") = "Updated Toys"
Next
da.UpdateBatchSize = 3
da.Update(pubsDataSet, "publishers")
MessageBox.Show(sb.ToString)
End Sub
End Class
2.1.7 DbProviderFactory类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Imports System.Data.Common
Imports System.Data.SqlClient
Public Class DbProviderFactoryExample
Public Enum DbProvider
SqlClient
OleDb
Odbc
Oracle
End Enum
Public Function GetConnection() As IDbConnection
'Get the provider from the config file
Dim provider As DbProvider = System.Enum.Parse(GetType(DbProvider), ConfigurationManager.AppSettings("provider").ToString)
Dim connection As IDbConnection = Nothing
Select Case (provider)
Case DbProvider.SqlClient
connection = New System.Data.SqlClient.SqlConnection
Case DbProvider.OleDb
connection = New System.Data.OleDb.OleDbConnection
Case DbProvider.Odbc
connection = New System.Data.Odbc.OdbcConnection
Case DbProvider.Oracle
connection = New System.Data.OracleClient.OracleConnection
End Select
Return connection
End Function
'Get the singleton instance
Dim factory As DbProviderFactory = SqlClientFactory.Instance
Function GetProviderConnection() As DbConnection
Dim connection As DbConnection = factory.CreateConnection
connection.ConnectionString = "Data Source=.\ sqlexpress;" _
& "AttachDbFilename=|DataDirectory|\pubs.mdf;" _
& "Integrated Security=true;user instance=true"
Return connection
End Function
Function GetData(ByVal commandText As String, ByVal commandType As CommandType) As DataTable
'Get SqlDbCommand
Dim cmd As DbCommand = factory.CreateCommand
cmd.Connection = GetProviderConnection()
If (cmd.Connection Is Nothing) Then
Return Nothing
End If
cmd.CommandText = commandText
cmd.CommandType = commandType
cmd.Connection.Open()
Dim dt As New DataTable
'Get SqlDataReader and populate data table
dt.Load(cmd.ExecuteReader)
cmd.Connection.Close()
Return dt
End Function
End Class
2.1.8 DbProviderFactories类
2.1.9 枚举数据源
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Public Class frmProviderFactories
Dim providersList As DataTable = Nothing
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
providersList = DbProviderFactories.GetFactoryClasses
DataGridView1.DataSource = providersList
End Sub
Sub DataGridView1_RowHeaderMouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) _
Dim providerRow As DataRow = providersList.DefaultView(e.RowIndex).Row
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(providerRow)
Dim sources As DataTable = factory.CreateDataSourceEnumerator.GetDataSources
Dim f As frmSources = New frmsources
f.DataSources = sources
If f.ShowDialog <> Windows.Forms.DialogResult.OK Then
Return
End If
Dim selectedSource As DataRow = f.SelectedSource
End Sub
End Class
数据源列表对话框
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Dim _DataSources As DataTable
Public Property DataSources() As DataTable
Get
Return _DataSources
End Get
Set(ByVal value As DataTable)
_DataSources = value
End Set
End Property
Dim _SelectedSource As DataRow
Public Property SelectedSource() As DataRow
Get
Return _SelectedSource
End Get
Set(ByVal value As DataRow)
_SelectedSource = value
End Set
End Property
Sub DataGridView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) _
Handles DataGridView1.MouseDoubleClick
SelectedSource = DirectCast(DataGridView1.DataSource, DataTable).DefaultView(DataGridView1.CurrentCell.RowIndex).Row
DialogResult = Windows.Forms.DialogResult.OK
End Sub
End Class