Enable DataAdapter to create update statements
I accidentally cut the question mark.
Do this for a day, more rush, has not fully inspected.
In the process of use, if you find out, please leave a sentence on my blog.
BLOG: http://blog.9cbs.net/lzmtw
Public Notinheritable Class TableColumnInfo
Private const [BaseTablename] as string = "baseteablename"
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 mDatapection () 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 MDATYPES
END GET
End Property
Public Readonly Property Columnize () 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 Oralse McolumnTable.Rows.count = 0 THEN EXIT SUB
DIM M AS DATAROW
For Each M in McolumnTable.Rows
Redim Preserve McolumnNames (Mcolumncount)
Redim Preserve MDATYPES (MCOLUMNCOUNT)
Redim Preserve Mcolumnsize (MCOLUMNCOUNT)
Redim preserve mkeys (mcolumncount) ColumnNames (McOLUMNCOUNT) = m.Item (ColumnName) .tostring
MDATYPES (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
'Do you support bulk updates, not support by default?
Public Writeonly Property OdbClow () as boolean
Set (ByVal Value As Boolean)
ModbLow = 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 TableColumnInfominfo.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 keycolumn 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 thendim 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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (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 only one 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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (i)
. SourceVersion = DataRowVersion.original
End with
_Odapter.deleteCommand.Parameters.Add (p)
END IF
NEXT
'Non Key has two 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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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 only one 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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (i)
. SourceVersion = DataRowVersion.original
End with
_Edapter.deleteCommand.Parameters.Add (P)
END IF
NEXT
'Non Key has two 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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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} 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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (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
'First plus all
For i = 0 to minfo.columncount - 1
DIM P as new odbc.odbcparameter
With P
. SourceColumn = minfo.columnnames (i)
.Dbtype = getdbtype (Minfo.datatypes (i))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (i)
End with
_Odapter.insertcommand.parameters.Add (P)
NEXT
'Add 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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = 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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (i)
. SourceVersion = DataRowVersion.original
End with
_Odapter.UpdateCommand.Parameters.Add (P)
END IF
NEXT
'Non Key has two parameter
For i = 0 to minfo.columncount - 1if not minfo.keys (i) THEN
DIM P as new odbc.odbcparameter
With P
. SourceColumn = minfo.columnnames (i)
.Dbtype = getdbtype (Minfo.datatypes (i))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para11, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = string.format
Para
, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (i)
. SourceVersion = DataRowVersion.originaled with
_Odapter.UpdateCommand.Parameters.Add (P)
END IF
NEXT
'Non Key has two 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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para11, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para2, Minfo.columnnames (i))
.Size = minfo.columpnsize (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.columpnsize (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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (i)
. SourceVersion = DataRowVersion.original
End with
_Edapter.UpdateCommand.Parameters.Add (P)
END IF
NEXT
'Non Key has two 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))
.ParametersAme = string.format (Para1, Minfo.columnnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para11, Minfo.columnnames (i))
.Size = minfo.columpnsize (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))
.ParametersAme = String.Format (Para2, Minfo.columnnames (i))
.Size = minfo.columpnsize (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 MDATYPE.INDEXOF ("INT32")> 0 THEN
Return dbtype.Int32
END IF
IF MDATYPE.INDEXOF ("Boolean"> 0 THEN
Return dbtype.boolean
END IF
If MDATYPE.INDEXOF ("Byte"> 0 THEN
Return DBTYPE.BYTE
END IF
IF MDATYPE.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 MDATYPE.INDEXOF ("Decimal")> 0 THEN
Return DBTYPE.DECIMAL
END IF
IF MDATYPE.INDEXOF ("Double"> 0 THEN
Return DBTYPE.DOTOUBLE
END IF
IF MDATYPE.INDEXOF ("INT16")> 0 THEN
Return dbtype.Int16
End ifif mdattype.indexof ("int32")> 0 THEN
Return dbtype.Int32
END IF
IF MDATYPE.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.sis
END IF
IF MDATYPE.INDEXOF ("Time"> 0 THEN
Return DBTYPE.TIME
END IF
IF MDATYPE.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.dataable
DIM MTABLE As System.Data.DataTable
If MDataAdapter is nothing orelse mdataadapter.selectcommand.commandtext is nothing the exit function
If mdataadapter.selectcommand.connection.state <> connectionState.Open 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.dataable
DIM MTABLE As System.Data.DataTable
If MDataAdapter is nothing orelse mdataadapter.selectcommand.commandtext is nothing the exit function
If mdataadapter.selectcommand.connection.state <> connectionState.Open 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.dataabledim MTABLE As System.Data.DataTable
If MDataAdapter is nothing orelse mdataadapter.selectcommand.commandtext is nothing the exit function
If mdataadapter.selectcommand.connection.state <> connectionState.Open mDataAdapter.selectCommand.connection.Open ()
MTABLE = mdataadapter.selectcommand.executeReader (Commandbehavior.KeyInfo) .Getschematable
MDataAdapter.selectCommand.connection.Close ()
Return MTABLE
END FUNCTION
END CLASS
Instructions:
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 'He should be adatperupdate, lzmtw.data is named by my own
Private sub button1_click (byvale as system.object, byval e as system.eventargs) Handles Button1.click
DIM Keys (0) AS STRING
Keys (0) = "p1" "This is one of the field names, as the primary key
Myad.getadapter (ad, keys) 'This is given to the corresponding string of the other three Commands of DataAptaPter.
End Sub
Private sub Button2_click (byvalgend, byval e as system.eventargs) Handles Button2.click
DS = New DataSet
cn.open ()
Ad.fill (DS, myad.datasettable "must be filld by the Table name returned
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) 'Now updating
cn.close ()
End Sub
