Visual Studio 2005 初體驗之三:使用DBFactory class連接配接資料庫
在以前開發的項目中,連接配接到不同的資料源需要使用不同的代碼,VS2005中的DBFactory class則可以完成對不同資料源的連接配接使用相同的代碼。是以,如果在一個項目中需要使用多個資料源(如Access、SQLServer、Foxpro等),那麼VS2005為開發者提供了很好的途徑。 執行個體: 1、建立一個工程,按照圖示的要求布置界面。
2、GetData button click事件的代碼: 2.1、獲得使用的資料庫名稱: Dim myName As String = providerComboBox.SelectedItem.ToString() 2.2、定義連接配接資料庫對象: Dim myConnectionSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(myName) Dim myProvider As DbProviderFactory = DbProviderFactories.GetFactory(myConnectionSettings.ProviderName) Dim myConnection As DbConnection = myProvider.CreateConnection() myConnection.ConnectionString = myConnectionSettings.ConnectionString 2.3、打開資料庫連接配接對象: myConnection.Open() 2.4、為datagrid定義一個資料集: Dim myAdapter As DbDataAdapter = myProvider.CreateDataAdapter()
Dim myCommand As DbCommand = myProvider.CreateCommand() Dim myQuery As String = "SELECT * FROM SampleData"
Dim myDataSet As DataSet = New DataSet() myCommand.Connection = myConnection
myCommand.CommandText = myQuery myAdapter.SelectCommand = myCommand myAdapter.Fill(myDataSet) 2.5、綁定datagrid: displayDataGridView.DataSource = myDataSet.Tables(0) 3、獲得伺服器和資料庫連接配接字元串資訊: providerNameTextLabel.Text = "Provider: " & myConnectionSettings.ProviderName.ToString()
connectionStringLabel.Text = "ConnectString:" & myConnectionSettings.ConnectionString.ToString() 4、完整的代碼如下: Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.Common
Imports System.Configuration
Imports System.Diagnostics Public Class factoryClassesForm Private Sub getDataButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles getDataButton.Click
Try '' Get the name of the database we are going to use
Dim myName As String = providerComboBox.SelectedItem.ToString() '' Get the connection settings from the configuration file based on the name selected
'' in the ComboBox
Dim myConnectionSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(myName) '' Create an instance of the DbProviderFactory by using the Provider name in the config file
Dim myProvider As DbProviderFactory = DbProviderFactories.GetFactory(myConnectionSettings.ProviderName) '' Create the connection from the DbProvider factory, this code does not need to change for
'' for different providers
Dim myConnection As DbConnection = myProvider.CreateConnection() '' Get the connection string from the connectionsettings
'' This gets us the specific provider by which we will connect
myConnection.ConnectionString = myConnectionSettings.ConnectionString '' Open the connection
myConnection.Open() '' now we can create the DataAdapter and then create a command
Dim myAdapter As DbDataAdapter = myProvider.CreateDataAdapter()
Dim myCommand As DbCommand = myProvider.CreateCommand() '' Create the DataSet so that we can populate the datagrid
Dim myQuery As String = "SELECT * FROM SampleData"
Dim myDataSet As DataSet = New DataSet()
myCommand.Connection = myConnection
myCommand.CommandText = myQuery
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myDataSet) displayDataGridView.DataSource = myDataSet.Tables(0) '' with other provider and connection string information
providerNameTextLabel.Text = "Provider: " & myConnectionSettings.ProviderName.ToString()
connectionStringLabel.Text = "ConnectString:" & myConnectionSettings.ConnectionString.ToString()
Catch ex As Exception
MessageBox.Show("There was an error retrieving data. Please try again.", "Alert")
End Try
End Sub Private Sub factoryClassesForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
providerComboBox.SelectedIndex = 0
End Sub
End Class 5、運作後的效果如圖: