使DataAdapter自动创建更新语句

2024-07-21 02:23:20

字体：大中小

来源：转载

供稿：网友

使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))

.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有两个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有两个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有两个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 '这里应为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" '这是其中一个字段名，作为主键

myad.getadapter(ad, keys) '这里使dataadapter的其它三个command赋给了相应的字串

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) '现在按往常一样来更新

cn.close()

end sub

收集最实用的网页特效代码！

上一篇：带图片的，多列的dro&#112;DownList的实现

下一篇：轻松搞定数据访问层&#091;续1&#093;