通用資料庫訪問

Ⅰ DBFactory一個通用的資料庫操作類，可以訪問SQL,Oracle資料庫。

Public Function ConnectionString(ByVal ProviderName As String) As String
Dim ConnStr As String = ""
Select Case ProviderName
Case "System.Data.OleDb"
ConnStr = "Provider=SQLOLEDB;Server=" & My.Settings.ServerName & ";Database=" & My.Settings.DBName & ";UID=" & My.Settings.DBUser & ";PWD=" & My.Settings.DBPWD
Case "System.Data.SqlClient"
ConnStr = "Data Source=" & My.Settings.ServerName & ";Initial Catalog=" & My.Settings.DBName & ";Persist Security Info=True;User ID=" & My.Settings.DBUser & ";Password=" & My.Settings.DBPWD
Case "System.Data.OracleClient"
ConnStr = "Data Source=" & My.Settings.ServerName & ";Password=" & My.Settings.DBPWD & ";User ID=" & My.Settings.DBUser
End Select
Return ConnStr
End Function
Public Function GetData(ByVal SqlString As String) As DataTable
Try
Dim mProvider As DbProviderFactory = DbProviderFactories.GetFactory(My.Settings.Provider)
Dim mConnection As DbConnection = mProvider.CreateConnection
mConnection.ConnectionString = ConnectionString(My.Settings.Provider)
Dim mCmd As DbCommand = mProvider.CreateCommand
mCmd.CommandText = SqlString
mCmd.Connection = mConnection
Dim mAda As DbDataAdapter = mProvider.CreateDataAdapter
mAda.TableMappings.Add("table", "temp")
mAda.SelectCommand = mCmd
mConnection.Open()
mCmd.ExecuteNonQuery()
mConnection.Close()
Dim dst As New DataSet
mAda.Fill(dst, "temp")
Return dst.Tables("temp")
Catch ex As Exception
Return New DataTable
End Try
End Function