Public
Class DateSetDemo
Class DateSetDemo
'定义事务
Private SqlTran As SqlTransaction
SqlTran = conn.BeginTransaction
Dim rtnBool As Boolean = UpdateTableFunc(conn, SqlTran, "M_System2", "M_System", cmdDataArgs)
If rtnBool Then
SqlTran.Commit()
Else
SqlTran.Rollback()
End If
Private M_Sys2ColName() As String = {"Shubetsu", "Code", "SeigyoJouho3"}
Private M_Sys2Key() As String = {"Shubetsu", "Code"}
Private M_Sys3ColName() As String = {"Shubetsu", "Code", "SeigyoJouho3"}
Private M_Sys3Key() As String = {"Shubetsu", "Code"}
'******************************************************************
Private Function GetColumn()Function GetColumn(ByVal tableName As String) As String()
Select Case UCase(tableName)
Case "M_SYSTEM2"
Return M_Sys2ColName
Case "M_SYSTEM3"
Return M_Sys3ColName
Case Else
Return Nothing
End Select
End Function
'********************************************************************
Private Function GetTblKey()Function GetTblKey(ByVal tableName As String) As String()
Select Case UCase(tableName)
Case "M_SYSTEM2"
Return M_Sys2Key
Case "M_SYSTEM3"
Return M_Sys2Key
Case Else
Return Nothing
End Select
End Function
Public Function UpdateTableFunc()Function UpdateTableFunc(ByVal conn As SqlConnection, ByVal SqlTran As SqlTransaction, ByVal strTblName As String, ByVal strDtsTblName As String, ByVal dtsUpdate As Data.DataSet) As Boolean
Try
Dim dataAdapter As New SqlDataAdapter
dataAdapter.UpdateCommand = CreateCommand(conn, SqlTran, "Update", strTblName, strDtsTblName, dtsUpdate)
dataAdapter.InsertCommand = CreateCommand(conn, SqlTran, "Insert", strTblName, strDtsTblName, dtsUpdate)
dataAdapter.DeleteCommand = CreateCommand(conn, SqlTran, "Delete", strTblName, strDtsTblName, dtsUpdate)
dataAdapter.Update(dtsUpdate, strDtsTblName)
dtsUpdate.Tables(strDtsTblName).AcceptChanges()
Return True
Catch ex As Exception
Return False
End Try
End Function
Private Function CreateCommand()Function CreateCommand(ByVal conn As SqlConnection, ByVal SqlTran As SqlTransaction, ByVal sqlName As String, ByVal strTblName As String, ByVal strDtsTblName As String, ByVal dtsUpdate As Data.DataSet) As SqlCommand
Dim SqlTmp As String = Nothing
Dim i As Integer
Dim columnName() As String, pKey() As String
columnName = GetColumn(strTblName)
pKey = GetTblKey(strTblName)
Select Case UCase(sqlName)
Case "UPDATE"
'UodateCommand
SqlTmp = "Update " & strTblName & " Set"
For i = 0 To columnName.Length - 1
SqlTmp = SqlTmp & " " & Trim(columnName(i)) & "= @" & Trim(columnName(i)) & ","
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 1) & " Where "
For i = 0 To pKey.Length - 1
SqlTmp = SqlTmp & " " & Trim(pKey(i)) & "= @" & Trim(pKey(i)) & " And"
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 4)
Case "INSERT"
'InsertCommand
SqlTmp = "Insert into " & strTblName & "("
For i = 0 To columnName.Length - 1
SqlTmp = SqlTmp & Trim(columnName(i)) & ","
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 1) & ") Values ("
For i = 0 To columnName.Length - 1
SqlTmp = SqlTmp & "@" & Trim(columnName(i)) & ","
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 1) & ")"
Case "DELETE"
'DeleteCommand
SqlTmp = "Delete " & strTblName & " Where"
For i = 0 To pKey.Length - 1
SqlTmp = SqlTmp & " " & Trim(pKey(i)) & " = @" & Trim(pKey(i)) & " And"
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 4)
End Select
Dim cmd As New SqlCommand(SqlTmp, conn)
cmd.CommandType = CommandType.Text
cmd.Transaction = SqlTran
For i = 0 To columnName.Length - 1
cmd.Parameters.Add("@" & columnName(i), getSqlDbType(dtsUpdate.Tables(strDtsTblName).Columns(columnName(i)).DataType.ToString), -1, columnName(i))
Next
Return cmd
End Function
Private Function getSqlDbType()Function getSqlDbType(ByVal TypeName As String) As SqlDbType
Select Case TypeName()
Case "System.Boolean"
Return SqlDbType.Bit
Case "System.Byte"
Return SqlDbType.TinyInt
Case "System.Char"
Return SqlDbType.Char
Case "System.DateTime"
Return SqlDbType.DateTime
Case "System.Decimal"
Return SqlDbType.Decimal
Case "System.Double"
Return SqlDbType.Float
Case "System.Int16"
Return SqlDbType.SmallInt
Case "System.Int32"
Return SqlDbType.Int
Case "System.Int64"
Return SqlDbType.BigInt
Case "System.SByte"
Return SqlDbType.SmallInt
Case "System.Single"
Return SqlDbType.Real
Case "System.String"
Return SqlDbType.Char
Case "System.TimeSpan"
Return SqlDbType.Timestamp
Case "System.UInt16"
Return SqlDbType.SmallInt
Case "System.UInt32"
Return SqlDbType.Int
Case "System.UInt64"
Return SqlDbType.BigInt
Case Else
Return SqlDbType.Variant
End Select
End Function
End Class
'定义事务
Private SqlTran As SqlTransaction
SqlTran = conn.BeginTransaction
Dim rtnBool As Boolean = UpdateTableFunc(conn, SqlTran, "M_System2", "M_System", cmdDataArgs)
If rtnBool Then
SqlTran.Commit()
Else
SqlTran.Rollback()
End If
Private M_Sys2ColName() As String = {"Shubetsu", "Code", "SeigyoJouho3"}
Private M_Sys2Key() As String = {"Shubetsu", "Code"}
Private M_Sys3ColName() As String = {"Shubetsu", "Code", "SeigyoJouho3"}
Private M_Sys3Key() As String = {"Shubetsu", "Code"}
'******************************************************************
Private Function GetColumn()Function GetColumn(ByVal tableName As String) As String()
Select Case UCase(tableName)
Case "M_SYSTEM2"
Return M_Sys2ColName
Case "M_SYSTEM3"
Return M_Sys3ColName
Case Else
Return Nothing
End Select
End Function
'********************************************************************
Private Function GetTblKey()Function GetTblKey(ByVal tableName As String) As String()
Select Case UCase(tableName)
Case "M_SYSTEM2"
Return M_Sys2Key
Case "M_SYSTEM3"
Return M_Sys2Key
Case Else
Return Nothing
End Select
End Function
Public Function UpdateTableFunc()Function UpdateTableFunc(ByVal conn As SqlConnection, ByVal SqlTran As SqlTransaction, ByVal strTblName As String, ByVal strDtsTblName As String, ByVal dtsUpdate As Data.DataSet) As Boolean
Try
Dim dataAdapter As New SqlDataAdapter
dataAdapter.UpdateCommand = CreateCommand(conn, SqlTran, "Update", strTblName, strDtsTblName, dtsUpdate)
dataAdapter.InsertCommand = CreateCommand(conn, SqlTran, "Insert", strTblName, strDtsTblName, dtsUpdate)
dataAdapter.DeleteCommand = CreateCommand(conn, SqlTran, "Delete", strTblName, strDtsTblName, dtsUpdate)
dataAdapter.Update(dtsUpdate, strDtsTblName)
dtsUpdate.Tables(strDtsTblName).AcceptChanges()
Return True
Catch ex As Exception
Return False
End Try
End Function
Private Function CreateCommand()Function CreateCommand(ByVal conn As SqlConnection, ByVal SqlTran As SqlTransaction, ByVal sqlName As String, ByVal strTblName As String, ByVal strDtsTblName As String, ByVal dtsUpdate As Data.DataSet) As SqlCommand
Dim SqlTmp As String = Nothing
Dim i As Integer
Dim columnName() As String, pKey() As String
columnName = GetColumn(strTblName)
pKey = GetTblKey(strTblName)
Select Case UCase(sqlName)
Case "UPDATE"
'UodateCommand
SqlTmp = "Update " & strTblName & " Set"
For i = 0 To columnName.Length - 1
SqlTmp = SqlTmp & " " & Trim(columnName(i)) & "= @" & Trim(columnName(i)) & ","
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 1) & " Where "
For i = 0 To pKey.Length - 1
SqlTmp = SqlTmp & " " & Trim(pKey(i)) & "= @" & Trim(pKey(i)) & " And"
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 4)
Case "INSERT"
'InsertCommand
SqlTmp = "Insert into " & strTblName & "("
For i = 0 To columnName.Length - 1
SqlTmp = SqlTmp & Trim(columnName(i)) & ","
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 1) & ") Values ("
For i = 0 To columnName.Length - 1
SqlTmp = SqlTmp & "@" & Trim(columnName(i)) & ","
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 1) & ")"
Case "DELETE"
'DeleteCommand
SqlTmp = "Delete " & strTblName & " Where"
For i = 0 To pKey.Length - 1
SqlTmp = SqlTmp & " " & Trim(pKey(i)) & " = @" & Trim(pKey(i)) & " And"
Next
SqlTmp = Mid(SqlTmp, 1, SqlTmp.Length - 4)
End Select
Dim cmd As New SqlCommand(SqlTmp, conn)
cmd.CommandType = CommandType.Text
cmd.Transaction = SqlTran
For i = 0 To columnName.Length - 1
cmd.Parameters.Add("@" & columnName(i), getSqlDbType(dtsUpdate.Tables(strDtsTblName).Columns(columnName(i)).DataType.ToString), -1, columnName(i))
Next
Return cmd
End Function
Private Function getSqlDbType()Function getSqlDbType(ByVal TypeName As String) As SqlDbType
Select Case TypeName()
Case "System.Boolean"
Return SqlDbType.Bit
Case "System.Byte"
Return SqlDbType.TinyInt
Case "System.Char"
Return SqlDbType.Char
Case "System.DateTime"
Return SqlDbType.DateTime
Case "System.Decimal"
Return SqlDbType.Decimal
Case "System.Double"
Return SqlDbType.Float
Case "System.Int16"
Return SqlDbType.SmallInt
Case "System.Int32"
Return SqlDbType.Int
Case "System.Int64"
Return SqlDbType.BigInt
Case "System.SByte"
Return SqlDbType.SmallInt
Case "System.Single"
Return SqlDbType.Real
Case "System.String"
Return SqlDbType.Char
Case "System.TimeSpan"
Return SqlDbType.Timestamp
Case "System.UInt16"
Return SqlDbType.SmallInt
Case "System.UInt32"
Return SqlDbType.Int
Case "System.UInt64"
Return SqlDbType.BigInt
Case Else
Return SqlDbType.Variant
End Select
End Function
End Class