Java技术
2005: 03 04 05 06 07 08
09 10 11 12
2006: 01 02
Asp.net
2005: 07 08 09 10 11 12
2006: 01 02
使DataAdapter自动创建更新语句
前头的那个我不小心将问号删去了.
做这个用了一天时间，比较仓促，还没有全面检验过。
使用过程中如发现不足之处，请在我的BLOG上留一句话。
BLOG：http://blog.csdn.net/lzmtw
Public NotInheritable Class TableColumnInfo
Private Const [BaseTableName] As String = "BaseTableName"
Private Const [ColumnName] As String = "ColumnName"
Private Const [DataType] As String = "DataType"
Private Const iColumnSize As String = "ColumnSize"
Private Const [IsKey] As String = "IsKey"
Private mTableName As String
Private mColumnNames() As String
Private mDataTypes() As String
Private mColumnSize() As Integer
Private mKeys() As Boolean
Private mColumnCount As Integer = 0
Private mKeyCount As Integer = 0
Public ReadOnly Property [TableName]() As String
Get
Return mTableName
End Get
End Property
Public ReadOnly Property ColumnNames() As String()
Get
Return mColumnNames
End Get
End Property
Public ReadOnly Property DataTypes() As String()
Get
Return mDataTypes
End Get
End Property
Public ReadOnly Property ColumnSize() As Integer()
Get
Return mColumnSize
End Get
End Property
Public ReadOnly Property Keys() As Boolean()
Get
Return mKeys
End Get
End Property
Public ReadOnly Property ColumnCount() As Integer
Get
Return mColumnCount
End Get
End Property
Public ReadOnly Property KeyCount() As Integer
Get
Return mKeyCount
End Get
End Property
Public Sub GetColumnTable(ByVal mColumnTable As DataTable)
If mColumnTable Is Nothing OrElse mColumnTable.Rows.Count = 0 Then Exit Sub
Dim m As DataRow
For Each m In mColumnTable.Rows
ReDim Preserve mColumnNames(mColumnCount)
ReDim Preserve mDataTypes(mColumnCount)
ReDim Preserve mColumnSize(mColumnCount)
ReDim Preserve mKeys(mColumnCount)
ColumnNames(mColumnCount) = m.Item(ColumnName).ToString
mDataTypes(mColumnCount) = m.Item(DataType).ToString
mColumnSize(mColumnCount) = CType(m.Item(iColumnSize), Integer)
mKeys(mColumnCount) = CType(m.Item(IsKey), Boolean)
If mKeys(mColumnCount) Then
mKeyCount += 1
End If
mColumnCount += 1
Next
mTableName = mColumnTable.Rows(0).Item(BaseTableName).ToString
End Sub
Public Sub AddKey(ByVal mColumnName As String)
Dim mIndex As Integer = Array.IndexOf(ColumnNames, mColumnName)
If mIndex <> -1 AndAlso Not mKeys(mIndex) Then
mKeys(mIndex) = True
mKeyCount += 1
End If
End Sub
End Class
Public NotInheritable Class AdatperUpdate
Private mInfo As TableColumnInfo
Private _Adapter As SqlClient.SqlDataAdapter
Private _Odapter As Odbc.OdbcDataAdapter
Private _Edapter As OleDb.OleDbDataAdapter
Private mTableName As String
Private mColumns As String
Private m_Columns As String
Private mKeyWhere As String
Private mOrigWhere As String
Private mSet As String
Private AdapterType As String = ""
Private mOdbcLow As Boolean = True
´是否支持批量更新，默认不支持
Public WriteOnly Property OdbcLow() As Boolean
Set(ByVal Value As Boolean)
mOdbcLow = Value
End Set
End Property
Public Overloads Sub GetAdapter(ByVal mAdapter As SqlClient.SqlDataAdapter, ByVal KeyColumns As String())
_Adapter = mAdapter
mInfo = New TableColumnInfo
mInfo.GetColumnTable(Me.GetColumnTable(mAdapter))
Me.AddKey(KeyColumns)
AdapterType = "SQL"
ResetAdapter()
End Sub
Public Overloads Sub GetAdapter(ByVal mAdapter As Odbc.OdbcDataAdapter, ByVal KeyColumns As String())
_Odapter = mAdapter
mInfo = New TableColumnInfo
mInfo.GetColumnTable(Me.GetColumnTable(mAdapter))
Me.AddKey(KeyColumns)
AdapterType = "ODBC"
ResetAdapter()
End Sub
Public Overloads Sub GetAdapter(ByVal mAdapter As OleDb.OleDbDataAdapter, ByVal KeyColumns As String())
_Edapter = mAdapter
mInfo = New TableColumnInfo
mInfo.GetColumnTable(Me.GetColumnTable(mAdapter))
Me.AddKey(KeyColumns)
AdapterType = "OleDB"
ResetAdapter()
End Sub
Private Sub AddKey(ByVal KeyColumns As String())
Dim s As String
For Each s In KeyColumns
Me.mInfo.AddKey(s)
Next
End Sub
Private Sub ResetAdapter()
Select Case AdapterType
Case "SQL"
If _Adapter.DeleteCommand Is Nothing Then
Dim cm As New SqlClient.SqlCommand
cm.Connection = _Adapter.SelectCommand.Connection
_Adapter.DeleteCommand = cm
End If
If _Adapter.InsertCommand Is Nothing Then
Dim cm As New SqlClient.SqlCommand
cm.Connection = _Adapter.SelectCommand.Connection
_Adapter.InsertCommand = cm
End If
If _Adapter.UpdateCommand Is Nothing Then
Dim cm As New SqlClient.SqlCommand
cm.Connection = _Adapter.SelectCommand.Connection
_Adapter.UpdateCommand = cm
End If
Case "ODBC"
If _Odapter.DeleteCommand Is Nothing Then
Dim cm As New Odbc.OdbcCommand
cm.Connection = _Odapter.SelectCommand.Connection
_Odapter.DeleteCommand = cm
End If
If _Odapter.InsertCommand Is Nothing Then
Dim cm As New Odbc.OdbcCommand
cm.Connection = _Odapter.SelectCommand.Connection
_Odapter.InsertCommand = cm
End If
If _Odapter.UpdateCommand Is Nothing Then
Dim cm As New Odbc.OdbcCommand
cm.Connection = _Odapter.SelectCommand.Connection
_Odapter.UpdateCommand = cm
End If
Case "OleDB"
If _Edapter.DeleteCommand Is Nothing Then
Dim cm As New OleDb.OleDbCommand
cm.Connection = _Edapter.SelectCommand.Connection
_Edapter.DeleteCommand = cm
End If
If _Edapter.InsertCommand Is Nothing Then
Dim cm As New OleDb.OleDbCommand
cm.Connection = _Edapter.SelectCommand.Connection
_Edapter.InsertCommand = cm
End If
If _Edapter.UpdateCommand Is Nothing Then
Dim cm As New OleDb.OleDbCommand
cm.Connection = _Edapter.SelectCommand.Connection
_Edapter.UpdateCommand = cm
End If
End Select
GetGeneralString()
Me.GetSelect()
Me.GetDelete()
Me.GetInsert()
Me.GetUpdate()
Me.GetMapping()
End Sub
Public ReadOnly Property DataSetTable() As String
Get
Return mTableName
End Get
End Property
Private Sub GetGeneralString()
Dim i As Integer
mTableName = mInfo.TableName
mColumns = Join(mInfo.ColumnNames, ", ")
Select Case AdapterType
Case "SQL"
m_Columns = "@" & Join(mInfo.ColumnNames, ", @")
Case Else
Dim mStr(mInfo.ColumnCount - 1) As String
For i = 0 To mInfo.ColumnCount - 1
mStr(i) = "?"
Next
m_Columns = Join(mStr, ", ")
End Select
´
Dim mK As New ArrayList
Dim s As String
If mInfo.KeyCount > 0 Then
For i = 0 To mInfo.ColumnCount - 1
If mInfo.Keys(i) Then
s = mInfo.ColumnNames(i)
Select Case AdapterType
Case "SQL"
mK.Add(String.Format("({0} = @{0})", s))
Case Else
mK.Add(String.Format("({0} = ?)", s))
End Select
End If
Next
mKeyWhere = Join(mK.ToArray, " AND ")
End If
´
mK.Clear()
For Each s In mInfo.ColumnNames
Select Case AdapterType
Case "SQL"
mK.Add(String.Format("{0} = @{0}", s))
Case Else
mK.Add(String.Format("{0} = ?", s))
End Select
Next
mSet = Join(mK.ToArray, ", ")
´
mK.Clear()
For i = 0 To mInfo.ColumnCount - 1
s = mInfo.ColumnNames(i)
If mInfo.Keys(i) Then
Select Case AdapterType
Case "SQL"
mK.Add(String.Format("({0} = @Original_{0})", s))
Case Else
mK.Add(String.Format("({0} = ?)", s))
End Select
Else
Select Case AdapterType
Case "SQL"
mK.Add(String.Format("({0} = @Original_{0} OR @Original_{0} IS NULL AND {0} IS NULL)", s))
Case Else
mK.Add(String.Format("({0} = ? OR ? IS NULL AND {0} IS NULL)", s))
End Select
End If
Next
mOrigWhere = Join(mK.ToArray, " AND ")
´
mK.Clear()
End Sub
Private Sub GetSelect()
Const SELECT_TEXT As String = "SELECT {0} FROM {1}"
Const UPDATE_TEXT As String = ""
Select Case AdapterType
Case "SQL"
_Adapter.SelectCommand.CommandText = String.Format(SELECT_TEXT, mColumns, mTableName)
Case "ODBC"
_Odapter.SelectCommand.CommandText = String.Format(SELECT_TEXT, mColumns, mTableName)
Case "OleDB"
_Edapter.SelectCommand.CommandText = String.Format(SELECT_TEXT, mColumns, mTableName)
End Select
End Sub
Private Sub GetDelete()
Const DELETE_TEXT As String = "DELETE FROM {0} WHERE {1}"
Select Case AdapterType
Case "SQL"
_Adapter.DeleteCommand.CommandText = String.Format(DELETE_TEXT, mTableName, mOrigWhere)
´
Const Para As String = "@Original_{0}"
Dim i As Integer
For i = 0 To mInfo.ColumnCount - 1
Dim p As New SqlClient.SqlParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Adapter.DeleteCommand.Parameters.Add(p)
Next
Case "ODBC"
_Odapter.DeleteCommand.CommandText = String.Format(DELETE_TEXT, mTableName, mOrigWhere)
´
Const Para As String = "Original_{0}"
Const Para1 As String = "Original_{0}1"
Dim i As Integer
´Key只有一个Parameter
For i = 0 To mInfo.ColumnCount - 1
If mInfo.Keys(i) Then
Dim p As New Odbc.OdbcParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.DeleteCommand.Parameters.Add(p)
End If
Next
´非Key有两个Parameter
For i = 0 To mInfo.ColumnCount - 1
If Not mInfo.Keys(i) Then
Dim p As New Odbc.OdbcParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.DeleteCommand.Parameters.Add(p)
Dim p1 As New Odbc.OdbcParameter
With p1
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.DeleteCommand.Parameters.Add(p1)
End If
Next
Case "OleDB"
_Edapter.DeleteCommand.CommandText = String.Format(DELETE_TEXT, mTableName, mOrigWhere)
´
Const Para As String = "Original_{0}"
Const Para1 As String = "Original_{0}1"
Dim i As Integer
´Key只有一个Parameter
For i = 0 To mInfo.ColumnCount - 1
If mInfo.Keys(i) Then
Dim p As New OleDb.OleDbParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Edapter.DeleteCommand.Parameters.Add(p)
End If
Next
´非Key有两个Parameter
For i = 0 To mInfo.ColumnCount - 1
If Not mInfo.Keys(i) Then
Dim p As New OleDb.OleDbParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Edapter.DeleteCommand.Parameters.Add(p)
Dim p1 As New OleDb.OleDbParameter
With p1
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Edapter.DeleteCommand.Parameters.Add(p1)
End If
Next
End Select
End Sub
Private Sub GetInsert()
Const INSERT_TEXT As String = "INSERT INTO {0} ( {1} ) VALUES ( {2} ); SELECT {1} FROM {0} WHERE {4}"
Select Case AdapterType
Case "SQL"
_Adapter.InsertCommand.CommandText = String.Format(INSERT_TEXT, mTableName, mColumns, m_Columns, mColumns, mKeyWhere)
´
Const Para As String = "@{0}"
Dim i As Integer
For i = 0 To mInfo.ColumnCount - 1
Dim p As New SqlClient.SqlParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
End With
_Adapter.InsertCommand.Parameters.Add(p)
Next
Case "ODBC"
If Me.mOdbcLow Then
Const INSERT_TEXT1 As String = "INSERT INTO {0} ( {1} ) VALUES ( {2} )"
_Odapter.InsertCommand.CommandText = String.Format(INSERT_TEXT1, mTableName, mColumns, m_Columns)
´
Const Para As String = "{0}"
Dim i As Integer
For i = 0 To mInfo.ColumnCount - 1
Dim p As New Odbc.OdbcParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
End With
_Odapter.InsertCommand.Parameters.Add(p)
Next
Else
_Odapter.InsertCommand.CommandText = String.Format(INSERT_TEXT, mTableName, mColumns, m_Columns, mColumns, mKeyWhere)
´
Const Para As String = "{0}"
Const Para1 As String = "Select_{0}"
Dim i As Integer
´先加全部
For i = 0 To mInfo.ColumnCount - 1
Dim p As New Odbc.OdbcParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
&nbs