ado.net數據操作全接觸三（存儲過程，datasets）
2024-07-10 13:02:51
供稿：網友
6.1使用存儲過程
1: <%@ import namespace="system.data" %>
2: <%@ import namespace="system.data.sqlclient" %>
3:
4: <%
5: dim myconnection as sqlconnection
6: dim mycommand as sqlcommand
7: dim firstname as string = "robert"
8: dim lastname as string = "johnson"
9:
10: myconnection = new sqlconnection( "server=localhost;uid=sa;pwd=secret;database=mydata" )
11: myconnection.open()
12: mycommand = new sqlcommand( "insertauthors", myconnection )
13: mycommand.commandtype = commandtype.storedprocedure
14:
15: mycommand.parameters.add( new sqlparameter( "@firstname",
http://aspfree.com/chapters/sams/graphics/ccc.gifsqldbtype.varchar, 30 ))
16: mycommand.parameters( "@firstname" ).value = firstname
17:
18: mycommand.parameters.add( new sqlparameter( "@lastname",
http://aspfree.com/chapters/sams/graphics/ccc.gifsqldbtype.varchar, 30 ))
19: mycommand.parameters( "@lastname" ).value = lastname
20:
21: mycommand.executenonquery()
22: myconnection.close
23: %>
24: record inserted!
25:
26:
6.2重新得到返回參數和返回值
1: <%@ import namespace="system.data" %>
2: <%@ import namespace="system.data.sqlclient" %>
3:listing 6.4.1 demonstrates
4: <%
5: dim myconnection as sqlconnection
6: dim mycommand as sqlcommand
7: dim myparam as sqlparameter
8:
9: myconnection = new sqlconnection( "server=localhost;uid=sa;database=pubs" )
10: myconnection.open()
11:
12: mycommand = new sqlcommand( "getlastname", myconnection )
13: mycommand.commandtype = commandtype.storedprocedure
14:
15: myparam = mycommand.parameters.add( new
http://aspfree.com/chapters/sams/graphics/ccc.gifsqlparameter( "return value", sqldbtype.int ))
16: myparam.direction = parameterdirection.returnvalue
17:
18: myparam = mycommand.parameters.add( new
http://aspfree.com/chapters/sams/graphics/ccc.gifsqlparameter( "@firstname", sqldbtype.varchar, 50 ))
19: myparam.direction = parameterdirection.input
20: myparam.value = "robert"
21:
22: myparam = mycommand.parameters.add( new
http://aspfree.com/chapters/sams/graphics/ccc.gifsqlparameter( "@lastname", sqldbtype.varchar, 50 ))
23: myparam.direction = parameterdirection.output
24:
25: mycommand.executenonquery()
26: if mycommand.parameters( "return value" ).value then
27: response.write( "the last name is " &
mycommand.parameters( "@lastname" ).value )
28: else
29: response.write( "no author found!" )
30: end if
31: myconnection.close()
32: %>
33:
7.1使用datatable(sqlserver)
1: <%@ import namespace="system.data" %>
2: <%@ import namespace="system.data.sqlclient" %>
3:
4: <%
5: dim myconnection as sqlconnection
6: dim mydataadapter as sqldataadapter
7: dim mydataset as dataset
8: dim mydatatable as datatable
9: dim myrow as datarow
10:
11: myconnection = new sqlconnection( "server=localhost;uid=sa;database=pubs" )
12: mydataadapter = new sqldataadapter( "select * from authors", myconnection )
13: mydataset = new dataset()
14: mydataadapter.fill( mydataset, "authors" )
15:
16: for each myrow in mydataset.tables( "authors" ).rows
17: response.write( myrow( "au_lname" ) )datatable
18: next
19:
20: %>
21:
7.2使用datatable(access)
1: <%@ import namespace="system.data" %>
2: <%@ import namespace="system.data.oledb" %>
3:
4: <%
5: dim myconnection as oledbconnection
6: dim mydataadapter as oledbdataadapter
7: dim mydataset as dataset
8: dim mydatatable as datatable
9: dim myrow as datarow
10:
11: myconnection = new oledbconnection( "provider=microsoft.jet.oledb.4.0;data
http://aspfree.com/chapters/sams/graphics/ccc.gifsource=c:/authors.mdb" )
12: mydataadapter = new oledbdataadapter( "select * from authors", myconnection )
13: mydataset = new dataset()
14: mydataadapter.fill( mydataset, "authors" )
15:
16: for each myrow in mydataset.tables( "authors" ).rows
17: response.write( myrow( "author" ) )
18: next
19:
20: %>
21:
7.3自動顯示一個表
1: <%@ import namespace="system.data" %>
2: <%@ import namespace="system.data.sqlclient" %>
3:
4: <%
5: dim myconnection as sqlconnection
6: dim mydataadapter as sqldataadapter
7: dim mydataset as dataset
8: dim mydatatable as datatable
9:
10: dim rowcount as integer
11: dim colcount as integer
12: dim i, k as integer
13:
14: myconnection = new sqlconnection( "server=localhost;uid=sa;database=pubs" )
15: mydataadapter = new sqldataadapter( "select * from authors", myconnection )
16: mydataset = new dataset()
17: mydataadapter.fill( mydataset, "authors" )
18:
19: rowcount = mydataset.tables( "authors" ).rows.count
20: colcount = mydataset.tables( "authors" ).columns.count
21:
22: response.write( "<table border=1>" )
23: for i = 0 to rowcount - 1
24: response.write( "<tr>" )
25: for k = 0 to colcount - 1
26: response.write( "<td>" )
27: response.write( mydataset.tables( "authors" ).rows( i ).item( k,
datarowversion.current ).tostring() )
28: response.write( "</td>" )
29: next
30: response.write( "</tr>" )
31: next
32: response.write( "</table>" )
33: %>
34:
7.4建立一個datatable
1: <%@ import namespace="system.data" %>
2: <%
3: dim mydatatable as datatable
4: dim mycolumn as datacolumn
5: dim myrow as datarow
6: dim i as integer
7: dim myrand as system.random
8: dim productid as integer
9:
10: ' create a datatable
11: mydatatable = new datatable("shoppingcart")
12: mydatatable.minimumcapacity = 50
13: mydatatable.casesensitive = false
14:
15: ' add an autoincrement (identity) column
16: mycolumn = mydatatable.columns.add("id",
http://aspfree.com/chapters/sams/graphics/ccc.gifsystem.type.gettype("system.int32") )
17: mycolumn.autoincrement = true
18: mycolumn.allowdbnull = false
19:
20: ' add an integer column
21: mycolumn = mydatatable.columns.add("userid",
http://aspfree.com/chapters/sams/graphics/ccc.gifsystem.type.gettype("system.int32") )
22: mycolumn.allowdbnull = false
23:
24: ' add an integer column
25: mycolumn = mydatatable.columns.add("productid",
http://aspfree.com/chapters/sams/graphics/ccc.gifsystem.type.gettype("system.int32") )
26: mycolumn.allowdbnull = false
27:
28: ' add a string column
29: mycolumn = mydatatable.columns.add( "productname",
system.type.gettype("system.string") )
30: mycolumn.allowdbnull = false
31:
32: ' add a decimal column
33: mycolumn = mydatatable.columns.add("productprice",
http://aspfree.com/chapters/sams/graphics/ccc.gifsystem.type.gettype("system.decimal") )
34: mycolumn.allowdbnull = false
35:
36: ' add some data
37: myrand = new random
38: for i = 0 to 20
39: productid = myrand.next( 5 )
40: myrow = mydatatable.newrow()
41: myrow( "userid" ) = myrand.next( 3 )
42: myrow( "productid" ) = productid
43: myrow( "productname" ) = "product " & productid.tostring()
44: myrow( "productprice" ) = 10.25
45: mydatatable.rows.add( myrow )
46: next
47:
48: ' display all the rows
49: for each myrow in mydatatable.rows
50: response.write( "<hr>" )
51: for each mycolumn in mydatatable.columns
52: response.write( myrow.item( mycolumn ).tostring() & " / " )
53: next
54: next
55: %>
56:
57:
7.5在datatable中過濾數據
1: <%@ import namespace="system.data" %>
2: <%@ import namespace="system.data.sqlclient" %>
3:
4: <%
5: dim myconnection as sqlconnection
6: dim mydataadapter as sqldataadapter
7: dim mydataset as dataset
8: dim mydatatable as datatable
9: dim myrow as datarow
10: dim selectrows() as datarow
11:
12: myconnection = new sqlconnection( "server=localhost;uid=sa;pwd=secret;database=pubs" )
13: mydataadapter = new sqldataadapter( "select * from titles", myconnection )
14: mydataset = new dataset()
15: mydataadapter.fill( mydataset, "titles" )
16: selectrows = mydataset.tables( "titles" ).select( "type='popular_comp'",
http://aspfree.com/chapters/sams/graphics/ccc.gif"title desc", dataviewrowstate.currentrows )
17:
18: for each myrow in selectrows
19: response.write( myrow.item( "title" ) )
20: next
21: %>
7.6 dataview過濾
1: <%@ import namespace="system.data" %>
2: <%@ import namespace="system.data.sqlclient" %>
3: <%
4: dim myconnection as sqlconnection
5: dim mydataadapter as sqldataadapter
6: dim mydataset as dataset
7: dim mydatatable as datatable
8: dim mydataview as dataview
9: dim myrow as datarowview
10:
11: myconnection = new sqlconnection( "server=localhost;uid=sa;pwd=secret;database=pubs" )
12: mydataadapter = new sqldataadapter( "select * from titles", myconnection )
13: mydataset = new dataset()
14: mydataadapter.fill( mydataset, "titles" )
15: mydataview = mydataset.tables( "titles" ).defaultview
16: mydataview.rowfilter = "type='popular_comp'"
17: mydataview.sort = "title desc"
18:
19: for each myrow in mydataview
20: response.write( myrow( "title" ) )
21: next
22: %>
23:
菜鳥學堂：
