答案:使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
上一个:带图片的,多列的DropDownList的实现
下一个:轻松搞定数据访问层续2