DAO

Imports System.Data.SqlClient
Imports System.Runtime.CompilerServices

Public MustInherit Class BaseDAO
Private connection As SqlConnection

Public Sub Execute()
Me.connection = New SqlConnection(GetConfigParameters.GetMSSQLConnectString)
Using Me.connection
Me.ExecuteImp()
End Using
End Sub

Protected Function GetSQLDataReader(ByVal sql As String, ByVal Optional cmdType As CommandType = CommandType.Text) As SqlDataReader
Using cmd As New SqlCommand(sql)
cmd.CommandType = cmdType
Return Me.GetSqlDataReaderBase(cmd)
End Using
End Function

Protected Function GetSQLDataReader(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As SqlDataReader
cmd.CommandType = cmdType
Return Me.GetSqlDataReaderBase(cmd)
End Function

Private Function GetSqlDataReaderBase(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As SqlDataReader
cmd.CommandType = cmdType
Try
Me.OpenConnect(cmd)
'---SQL実行
Dim dr As SqlDataReader = cmd.ExecuteReader
Return dr
Catch ex As Exception
If cmd.CommandType = CommandType.StoredProcedure Then Utils.WriteSystemErrorLog(String.Format(ERR_SQL0005, cmd.CommandText), ex)
If cmd.CommandType = CommandType.Text Then Utils.WriteSystemErrorLog(String.Format(ERR_SQL0007, cmd.CommandText), ex)
Throw
End Try
End Function

Protected Function GetSQLDataTable(ByVal sql As String, ByVal Optional cmdType As CommandType = CommandType.Text) As DataTable
Using cmd As New SqlCommand(sql)
cmd.CommandType = cmdType
Return Me.GetSQLDataTableBase(cmd)
End Using
End Function

Protected Function GetSQLDataTable(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As DataTable
cmd.CommandType = cmdType
Return Me.GetSQLDataTableBase(cmd)
End Function

Private Function GetSQLDataTableBase(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As DataTable
cmd.CommandType = cmdType
Try
Dim dt As New DataTable
Me.SetConnect(cmd)
Using adapter As New SqlDataAdapter(cmd)
adapter.Fill(dt)
End Using
'---SQL実行
Return dt
Catch ex As Exception
If cmd.CommandType = CommandType.StoredProcedure Then Utils.WriteSystemErrorLog(String.Format(ERR_SQL0006, cmd.CommandText), ex)
If cmd.CommandType = CommandType.Text Then Utils.WriteSystemErrorLog(String.Format(ERR_SQL0008, cmd.CommandText), ex)
Throw
End Try
End Function

Protected Function Insert(ByVal sql As String, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
Using cmd As New SqlCommand(sql)
cmd.CommandType = cmdType
Return Me.InsertBase(cmd)
End Using
End Function

Protected Function Insert(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
cmd.CommandType = cmdType
Return Me.InsertBase(cmd)
End Function

Private Function InsertBase(ByVal cmd As SqlCommand) As Integer
Try
Me.OpenConnect(cmd)
Return cmd.ExecuteNonQuery
Catch ex As Exception
Utils.WriteSystemErrorLog(String.Format(ERR_SQL0002, cmd.CommandText), ex)
Throw
End Try
End Function

Protected Function Update(ByVal sql As String, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
Using cmd As New SqlCommand(sql)
cmd.CommandType = cmdType
Return Me.UpdateBase(cmd)
End Using
End Function

Protected Function Update(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
cmd.CommandType = cmdType
Return Me.UpdateBase(cmd)
End Function

Private Function UpdateBase(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
cmd.CommandType = cmdType
Try
Me.OpenConnect(cmd)
Return cmd.ExecuteNonQuery
Catch ex As Exception
Utils.WriteSystemErrorLog(String.Format(ERR_SQL0003, cmd.CommandText), ex)
Throw
End Try
End Function

Protected Function Delete(ByVal sql As String, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
Using cmd As New SqlCommand(sql)
cmd.CommandType = cmdType
Return Me.DeleteBase(cmd)
End Using
End Function

Protected Function Delete(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
cmd.CommandType = cmdType
Return Me.DeleteBase(cmd)
End Function

Private Function DeleteBase(ByVal cmd As SqlCommand, ByVal Optional cmdType As CommandType = CommandType.Text) As Integer
cmd.CommandType = cmdType
Try
Me.OpenConnect(cmd)
Return cmd.ExecuteNonQuery
Catch ex As Exception
Utils.WriteSystemErrorLog(String.Format(ERR_SQL0004, cmd.CommandText), ex)
Throw
End Try
End Function

Private Sub OpenConnect(ByVal cmd As SqlCommand)
Try
cmd.Connection = Me.connection
cmd.Connection.Open()
Catch ex As Exception
Utils.WriteSystemErrorLog(ERR_SQL1001, ex)
Throw
End Try
End Sub

Private Sub SetConnect(ByVal cmd As SqlCommand)
cmd.Connection = Me.connection
End Sub

Protected MustOverride Sub ExecuteImp()
Protected MustOverride Sub SetSQLParameters(ByVal cmd As SqlCommand)

End Class