為了將資料操作的部分獨立開來，所以整理了這個類別來處理與資料庫的動作，也提供給需要的人參考
為了將資料操作的部分獨立開來，所以整理了這個類別來處理與資料庫的動作，也提供給需要的人參考
Public Class DataProvider
Implements IDisposable
Private con As System.Data.SqlClient.SqlConnection
Private _SqlConnectionString As String = Nothing
Private DT As System.Data.DataTable
Public ReadOnly Property SqlConnectionString() As String
Get
Return _SqlConnectionString
End Get
End Property
'建構時傳入連線字串
Public Sub New(ByRef connectionstring As String)
_SqlConnectionString = Nothing
con = New System.Data.SqlClient.SqlConnection(connectionstring)
_SqlConnectionString = connectionstring
End Sub
'GetDataTable用在讀取資料到DataTable,提供三種多載方式，可使用參數化的SQL語法
Public Function GetDataTable(ByRef SQLString As String, ByRef type As CommandType) As System.Data.DataTable
Try
con.Open()
Dim DA As New System.Data.SqlClient.SqlDataAdapter(SQLString, con)
DA.SelectCommand.CommandType = type
DT = New System.Data.DataTable
DA.Fill(DT)
Return DT
Catch ex As SqlClient.SqlException
Throw ex
Finally
DT.Dispose()
Dispose()
End Try
End Function
Public Function GetDataTable(ByRef SQLString As String, ByRef type As CommandType, ByRef Parameters As System.Data.SqlClient.SqlParameter) As System.Data.DataTable
Try
con.Open()
Dim DA As New System.Data.SqlClient.SqlDataAdapter(SQLString, con)
DA.SelectCommand.CommandType = type
DA.SelectCommand.Parameters.Add(Parameters)
DT = New System.Data.DataTable
DA.Fill(DT)
DA.SelectCommand.Parameters.Clear()
Return DT
Catch ex As SqlClient.SqlException
Throw ex
Finally
DT.Dispose()
Dispose()
End Try
End Function
Public Function GetDataTable(ByRef SQLString As String, ByRef type As CommandType, ByRef Parameters As System.Data.SqlClient.SqlParameter()) As System.Data.DataTable
Try
con.Open()
Dim DA As New System.Data.SqlClient.SqlDataAdapter(SQLString, con)
DA.SelectCommand.CommandType = type
DA.SelectCommand.Parameters.AddRange(Parameters)
DT = New System.Data.DataTable
DA.Fill(DT)
DA.SelectCommand.Parameters.Clear()
Return DT
Catch ex As SqlClient.SqlException
Throw ex
Finally
DT.Dispose()
Dispose()
End Try
End Function
'Command用於新增、修改、刪除等對資料操作的SQL語法，提供6種多載，可使用參數化的SQL語法及TransactionScope交易
Public Function Command(ByRef SQLString As String, ByRef type As CommandType) As Integer
Try
Dim intt As Int32 = 0
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
COM.CommandText = SQLString
COM.Connection = con
COM.CommandType = type
intt = COM.ExecuteNonQuery()
Return intt
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Function
Public Function Command(ByRef SQLString As String, ByRef type As CommandType, ByRef Parameters As System.Data.SqlClient.SqlParameter)As Integer
Try
Dim intt As Int32 = 0
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
COM.CommandText = SQLString
COM.Connection = con
COM.CommandType = type
COM.Parameters.Add(Parameters)
intt = COM.ExecuteNonQuery()
COM.Parameters.Clear()
Return intt
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Function
Public Function Command(ByRef SQLString As String, ByRef type As CommandType, ByRef Parameters As System.Data.SqlClient.SqlParameter()) As Integer
Try
Dim intt As Int32 = 0
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
COM.CommandText = SQLString
COM.Connection = con
COM.CommandType = type
COM.Parameters.AddRange(Parameters)
intt = COM.ExecuteNonQuery()
COM.Parameters.Clear()
Return intt
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Function
Public Function Command(ByRef SQLString As List(Of String), ByRef type As CommandType) As Integer
Dim o As Int32 = 0
Dim y As Int32 = 0
Using TS As New System.Transactions.TransactionScope
Try
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
For i As Integer = 0 To SQLString.Count - 1
COM.CommandText = SQLString.Item(i).ToString
COM.Connection = con
COM.CommandType = type
y = 0
y = COM.ExecuteNonQuery()
o = o + y
Next
TS.Complete()
Return o
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Using
End Function
Public Function Command(ByRef SQLString As List(Of String), ByRef type As CommandType, ByRef ParametersList As List(Of System.Data.SqlClient.SqlParameter)) As Integer
Dim o As Int32 = 0
Dim y As Int32 = 0
Using TS As New System.Transactions.TransactionScope
Try
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
For i As Integer = 0 To SQLString.Count - 1
COM.CommandText = SQLString.Item(i).ToString
COM.Connection = con
COM.CommandType = type
COM.Parameters.Add(ParametersList.Item(i))
y = 0
y = COM.ExecuteNonQuery()
COM.Parameters.Clear()
o = o + y
Next
TS.Complete()
Return o
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Using
End Function
Public Function Command(ByRef SQLString As List(Of String), ByRef type As CommandType, ByRef ParametersList As List(Of System.Data.SqlClient.SqlParameter())) As Integer
Dim o As Int32 = 0
Dim y As Int32 = 0
Using TS As New System.Transactions.TransactionScope
Try
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
For i As Integer = 0 To SQLString.Count - 1
COM.CommandText = SQLString.Item(i).ToString
COM.Connection = con
COM.CommandType = type
COM.Parameters.AddRange(ParametersList.Item(i))
y = 0
y = COM.ExecuteNonQuery()
COM.Parameters.Clear()
o = o + y
Next
TS.Complete()
Return o
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Using
End Function
'ExecuteScalar為取第一行第一列的資料時使用，提供三個多載，可使用參數化的SQL語法
Public Function ExecuteScalar(ByRef SQLString As String, ByRef type As CommandType) As Object
Try
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
COM.CommandText = SQLString
COM.Connection = con
COM.CommandType = type
Return COM.ExecuteScalar()
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Function
Public Function ExecuteScalar(ByRef SQLString As String, ByRef type As CommandType, ByRef Parameters As System.Data.SqlClient.SqlParameter) As Object
Try
Dim obj As Object = Nothing
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
COM.CommandText = SQLString
COM.Connection = con
COM.CommandType = type
COM.Parameters.Add(Parameters)
obj = COM.ExecuteScalar()
COM.Parameters.Clear()
Return obj
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Function
Public Function ExecuteScalar(ByRef SQLString As String, ByRef type As CommandType, ByRef Parameters As System.Data.SqlClient.SqlParameter()) As Object
Try
Dim obj As Object = Nothing
con.Open()
Dim COM As New System.Data.SqlClient.SqlCommand
COM.CommandText = SQLString
COM.Connection = con
COM.CommandType = type
COM.Parameters.AddRange(Parameters)
obj = COM.ExecuteScalar()
COM.Parameters.Clear()
Return obj
Catch ex As SqlClient.SqlException
Throw ex
Finally
Dispose()
End Try
End Function
'SqlBulkCopy用於複製大量資料時使用，可自行調整資料對應欄位
Public Sub SqlBulkCopy(ByRef SourceSqlstring As String, ByRef DestinationTableName As String, ByRef SourceItemList As List(Of String), ByRef DestinationItemList As List(Of String))
Using sourceConnection As System.Data.SqlClient.SqlConnection = _
New System.Data.SqlClient.SqlConnection(SqlConnectionString)
sourceConnection.Open()
Dim commandSourceData As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SourceSqlstring, sourceConnection)
Dim reader As System.Data.SqlClient.SqlDataReader = commandSourceData.ExecuteReader
Using destinationConnection As System.Data.SqlClient.SqlConnection = _
New System.Data.SqlClient.SqlConnection(SqlConnectionString)
destinationConnection.Open()
Using bulkCopy As System.Data.SqlClient.SqlBulkCopy = _
New System.Data.SqlClient.SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = DestinationTableName
'設定一個批次量寫入多少筆資料
bulkCopy.BatchSize = 1000
'設定逾時的秒數
bulkCopy.BulkCopyTimeout = 60
'設定 NotifyAfter 屬性，以便在每複製 10000 個資料列至資料表後，呼叫事件處理常式。
'sqlBC.NotifyAfter = 10000;
For i As Integer = 0 To SourceItemList.Count - 1
For x As Integer = 0 To DestinationItemList.Count - 1
bulkCopy.ColumnMappings.Add(SourceItemList.Item(i).ToString(), DestinationItemList.Item(x).ToString())
Next
Next
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Throw ex
Finally
reader.Close()
End Try
End Using
End Using
End Using
End Sub
'檢查資料庫中是否有此資料表存在
Public Function IsSQLTableExist(ByVal TableName As String) As Boolean
Try
con.Open()
Dim DT As DataTable
Dim restrictions() As String = {Nothing, Nothing, TableName, Nothing}
DT = con.GetSchema("Tables", restrictions)
If DT.Rows.Count > 0 Then
Return True
Else
Return False
End If
Catch ex As Exception
MsgBox(ex)
Finally
Dispose()
End Try
End Function
#Region "IDisposable Support"
Private disposedValue As Boolean ' 偵測多餘的呼叫
' IDisposable
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
' TODO: 處置 Managed 狀態 (Managed 物件)。
End If
' TODO: 釋放 Unmanaged 資源 (Unmanaged 物件) 並覆寫下面的 Finalize()。
' TODO: 將大型欄位設定為 null。
End If
Me.disposedValue = True
End Sub
' TODO: 只有當上面的 Dispose(ByVal disposing As Boolean) 有可釋放 Unmanaged 資源的程式碼時，才覆寫 Finalize()。
'Protected Overrides Sub Finalize()
' ' 請勿變更此程式碼。在上面的 Dispose(ByVal disposing As Boolean) 中輸入清除程式碼。
' Dispose(False)
' MyBase.Finalize()
'End Sub
' 由 Visual Basic 新增此程式碼以正確實作可處置的模式。
Public Sub Dispose() Implements IDisposable.Dispose
' 請勿變更此程式碼。在以上的 Dispose 置入清除程式碼 (ByVal 視為布林值處置)。
con.Close()
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region
End Class