|
使DataAdapter自動(dòng)創(chuàng)建更新語句
前頭的那個(gè)我不小心將問號(hào)刪去了.
做這個(gè)用了一天時(shí)間，比較倉促，還沒有全面檢驗(yàn)過。
使用過程中如發(fā)現(xiàn)不足之處，請(qǐng)?jiān)谖业腂LOG上留一句話。
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
'是否支持批量更新，默認(rèn)不支持
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只有一個(gè)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有兩個(gè)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只有一個(gè)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有兩個(gè)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))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
End With
_Odapter.InsertCommand.Parameters.Add(p)
Next
'再加key
For i = 0 To mInfo.ColumnCount - 1
If mInfo.Keys(i) Then
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)
End With
_Odapter.InsertCommand.Parameters.Add(p1)
End If
Next
End If
Case "OleDB"
_Edapter.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 OleDb.OleDbParameter
With p
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
End With
_Edapter.InsertCommand.Parameters.Add(p)
Next
For i = 0 To mInfo.ColumnCount - 1
If mInfo.Keys(i) Then
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)
End With
_Edapter.InsertCommand.Parameters.Add(p1)
End If
Next
End Select
End Sub
Private Sub GetUpdate()
Const UPDATE_TEXT As String = "UPDATE {0} SET {1} WHERE {2}; SELECT {3} FROM {0} WHERE {4}"
Select Case AdapterType
Case "SQL"
_Adapter.UpdateCommand.CommandText = String.Format(UPDATE_TEXT, mTableName, mSet, mOrigWhere, 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.UpdateCommand.Parameters.Add(p)
Next
Const Para1 As String = "@Original_{0}"
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(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Adapter.UpdateCommand.Parameters.Add(p)
Next
Case "ODBC"
If Me.mOdbcLow Then
Const UPDATE_TEXT1 As String = "UPDATE {0} SET {1} WHERE {2}"
_Odapter.UpdateCommand.CommandText = String.Format(UPDATE_TEXT1, mTableName, mSet, mOrigWhere)
'
Const Para As String = "{0}"
Const Para1 As String = "Original_{0}"
Const Para11 As String = "Original_{0}1"
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.UpdateCommand.Parameters.Add(p)
Next
'Key
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(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.UpdateCommand.Parameters.Add(p)
End If
Next
'非Key有兩個(gè)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(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.UpdateCommand.Parameters.Add(p)
Dim p1 As New Odbc.OdbcParameter
With p1
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para11, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.UpdateCommand.Parameters.Add(p1)
End If
Next
Else
_Odapter.UpdateCommand.CommandText = String.Format(UPDATE_TEXT, mTableName, mSet, mOrigWhere, mColumns, mKeyWhere)
'
Const Para As String = "{0}"
Const Para1 As String = "Original_{0}"
Const Para11 As String = "Original_{0}1"
Const Para2 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))
.ParameterName = String.Format(Para, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
End With
_Odapter.UpdateCommand.Parameters.Add(p)
Next
'Key
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(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.UpdateCommand.Parameters.Add(p)
End If
Next
'非Key有兩個(gè)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(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.UpdateCommand.Parameters.Add(p)
Dim p1 As New Odbc.OdbcParameter
With p1
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para11, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Odapter.UpdateCommand.Parameters.Add(p1)
End If
Next
For i = 0 To mInfo.ColumnCount - 1
If mInfo.Keys(i) Then
Dim p1 As New Odbc.OdbcParameter
With p1
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para2, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
End With
_Odapter.UpdateCommand.Parameters.Add(p1)
End If
Next
End If
Case "OleDB"
_Edapter.UpdateCommand.CommandText = String.Format(UPDATE_TEXT, mTableName, mSet, mOrigWhere, mColumns, mKeyWhere)
'
Const Para As String = "{0}"
Const Para1 As String = "Original_{0}"
Const Para11 As String = "Original_{0}1"
Const Para2 As String = "Select_{0}"
Dim i As Integer
For i = 0 To mInfo.ColumnCount - 1
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)
End With
_Edapter.UpdateCommand.Parameters.Add(p)
Next
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(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Edapter.UpdateCommand.Parameters.Add(p)
End If
Next
'非Key有兩個(gè)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(Para1, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Edapter.UpdateCommand.Parameters.Add(p)
Dim p1 As New OleDb.OleDbParameter
With p1
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para11, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
.SourceVersion = DataRowVersion.Original
End With
_Edapter.UpdateCommand.Parameters.Add(p1)
End If
Next
For i = 0 To mInfo.ColumnCount - 1
If mInfo.Keys(i) Then
Dim p1 As New OleDb.OleDbParameter
With p1
.SourceColumn = mInfo.ColumnNames(i)
.DbType = GetDbType(mInfo.DataTypes(i))
.ParameterName = String.Format(Para2, mInfo.ColumnNames(i))
.Size = mInfo.ColumnSize(i)
End With
_Edapter.UpdateCommand.Parameters.Add(p1)
End If
Next
End Select
End Sub
Private Sub GetMapping()
Dim ColMaps(mInfo.ColumnCount - 1) As System.Data.Common.DataColumnMapping
Dim i As Integer
For i = 0 To mInfo.ColumnCount - 1
Dim cmp As New System.Data.Common.DataColumnMapping
With cmp
.SourceColumn = mInfo.ColumnNames(i)
.DataSetColumn = mInfo.ColumnNames(i)
End With
ColMaps(i) = cmp
Next
Dim TableMap As New System.Data.Common.DataTableMapping
With TableMap
.SourceTable = "Table"
.DataSetTable = mInfo.TableName
.ColumnMappings.AddRange(ColMaps)
End With
Select Case AdapterType
Case "SQL"
_Adapter.TableMappings.Clear()
_Adapter.TableMappings.Add(TableMap)
Case "ODBC"
_Odapter.TableMappings.Clear()
_Odapter.TableMappings.Add(TableMap)
Case "OleDB"
_Edapter.TableMappings.Clear()
_Edapter.TableMappings.Add(TableMap)
End Select
End Sub
Private Function GetDbType(ByVal mDataType As String) As DbType
Dim a As Integer
If mDataType.IndexOf("String") > 0 Then
Return DbType.String
End If
If mDataType.IndexOf("Int32") > 0 Then
Return DbType.Int32
End If
If mDataType.IndexOf("Boolean") > 0 Then
Return DbType.Boolean
End If
If mDataType.IndexOf("Byte") > 0 Then
Return DbType.Byte
End If
If mDataType.IndexOf("Currency") > 0 Then
Return DbType.Currency
End If
If mDataType.IndexOf("DateTime") > 0 Then
Return DbType.DateTime
End If
If mDataType.IndexOf("Date") > 0 Then
Return DbType.Date
End If
If mDataType.IndexOf("Decimal") > 0 Then
Return DbType.Decimal
End If
If mDataType.IndexOf("Double") > 0 Then
Return DbType.Double
End If
If mDataType.IndexOf("Int16") > 0 Then
Return DbType.Int16
End If
If mDataType.IndexOf("Int32") > 0 Then
Return DbType.Int32
End If
If mDataType.IndexOf("Int64") > 0 Then
Return DbType.Int64
End If
If mDataType.IndexOf("Object") > 0 Then
Return DbType.Object
End If
If mDataType.IndexOf("Single") > 0 Then
Return DbType.Single
End If
If mDataType.IndexOf("Time") > 0 Then
Return DbType.Time
End If
If mDataType.IndexOf("VarNumeric") > 0 Then
Return DbType.VarNumeric
End If
End Function
Private Overloads Function GetColumnTable(ByVal mDataAdapter As System.Data.SqlClient.SqlDataAdapter) As System.Data.DataTable
Dim mTable As System.Data.DataTable
If mDataAdapter Is Nothing OrElse mDataAdapter.SelectCommand.CommandText Is Nothing Then Exit Function
If mDataAdapter.SelectCommand.Connection.State <> ConnectionState.Open Then mDataAdapter.SelectCommand.Connection.Open()
mTable = mDataAdapter.SelectCommand.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable
mDataAdapter.SelectCommand.Connection.Close()
Return mTable
End Function
Private Overloads Function GetColumnTable(ByVal mDataAdapter As System.Data.Odbc.OdbcDataAdapter) As System.Data.DataTable
Dim mTable As System.Data.DataTable
If mDataAdapter Is Nothing OrElse mDataAdapter.SelectCommand.CommandText Is Nothing Then Exit Function
If mDataAdapter.SelectCommand.Connection.State <> ConnectionState.Open Then mDataAdapter.SelectCommand.Connection.Open()
mTable = mDataAdapter.SelectCommand.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable
mDataAdapter.SelectCommand.Connection.Close()
Return mTable
End Function
Private Overloads Function GetColumnTable(ByVal mDataAdapter As System.Data.OleDb.OleDbDataAdapter) As System.Data.DataTable
Dim mTable As System.Data.DataTable
If mDataAdapter Is Nothing OrElse mDataAdapter.SelectCommand.CommandText Is Nothing Then Exit Function
If mDataAdapter.SelectCommand.Connection.State <> ConnectionState.Open Then mDataAdapter.SelectCommand.Connection.Open()
mTable = mDataAdapter.SelectCommand.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable
mDataAdapter.SelectCommand.Connection.Close()
Return mTable
End Function
End Class
使用方法：
Private Const DBaseCnnString As String = "DBQ={0};FIL=dBase 5.0;DefaultDir={0};Driver={{Driver do Microsoft dBase (*.dbf)}}"
Dim cn As New Odbc.OdbcConnection(String.Format(DBaseCnnString, "C:\Psms_db1"))
Dim ad As New Odbc.OdbcDataAdapter("SELECT * FROM Password", cn)
Dim ds As DataSet
Dim MyAd As New LzmTW.Data.AdatperUpdate '這里應(yīng)為AdatperUpdate,LzmTW.Data是我本人使用的命名
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Keys(0) As String
Keys(0) = "P1" '這是其中一個(gè)字段名，作為主鍵
MyAd.GetAdapter(ad, Keys) '這里使DataAdapter的其它三個(gè)Command賦給了相應(yīng)的字串
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
ds = New DataSet
cn.Open()
ad.Fill(ds, MyAd.DataSetTable) '一定要其返回的Table名來填充
cn.Close()
Me.DataGrid1.DataSource = ds.Tables(MyAd.DataSetTable)
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
cn.Open()
ad.Update(ds) '現(xiàn)在按往常一樣來更新
cn.Close()
End Sub
|
