ADO.NET data operation full contact three (stored procedure, datasets)

6.1 Using the stored procedure

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 Re-return parameters and return values

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.storedProcedure14:

15: myparam = mycommand.parameters.add (New

http://aspfree.com/chapters/sams/graphics/ccc.gifsqlparameter ("Return Value", SqldbType.In))))

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"

twenty one:

22: myparam = mycommand.parameters.add (New

http://aspfree.com/chapters/sams/graphics/ccc.gifsqlparameter ("@lastname", SqldbType.varchar, 50))

23: myparam.direction = parameterdirection.output

twenty four:

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 Using 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") .Rows17: Response.write (MyRow ("au_lname") DataTable

18: NEXT19: 20:%> 21: 7.2 Using DATATABLE (Access) 1: <% @ Import Namespace = "System.data"%> 2: <% @ Import namespace = "system.data.oledb"%> 3: 4: <% 5: Dim myConnection As OleDbConnection6: Dim myDataAdapter As OleDbDataAdapter7: Dim myDataSet As DataSet8: Dim myDataTable As DataTable9: Dim myRow As DataRow10: 11: myConnection = New OleDbConnection ( "PROVIDER = Microsoft.Jet.OLEDB.4.0; DATAhttp : //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") .Rows17: Response.write (MyRow ("Author") 18: Next19: 20:%> 21 : 7.3 Automatic display of a table 1: <% @ Import namespace = "system.data"%> 2: <% @ Import namespace = "system.data.sqlclient"%> 3: 4: <% 5: DIM MyConnection As SqlConnection6 : Dim MyDataAdapter7: Dim MyDataSet As DataSet8: Dim MyDataTable As DataTable9: 10: Dim Rowcount AS INTEGER11: DIM CO lCount As Integer12: Dim i, k As Integer13: 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. Count21: 22: Response.write ("

") 23: for i = 0 To Rowcount - 124: Response.write ("<

Tr> ") 25: fork = 0 to Colcount - 126: Response.write ("

") 27: Response.write (MyDataSet.Tables (" Authors "). Rows (i) .Item (K, DataRowVersion .Current) .tostring ()) 28: Response.write ("") 29: Next30: Response.write ("") 31: Next32: Response.write ("" 33:%> 34: 7.4 Create a DataTable1: <% @ Import namespace = "system.data"%> 2: <% 3: Dim MyDataBLE AS DATATABLE4: DIM MyColumn As Datacolumn5: Dim Myrow AS DataRow6: Dim i as integer7 : Dim myRand As System.Random8: Dim productID As Integer9: 10: 'Create a DataTable11: myDataTable = new DataTable ( "ShoppingCart") 12: myDataTable.MinimumCapacity = 5013: myDataTable.CaseSensitive = False14: 15:' Add an AutoIncrement ( Identity) Column16: myColumn = myDataTable.Columns.Add ( "ID", http://aspfree.com/chapters/sams/graphics/ccc.gifSystem.Type.GetType("System.Int32 ")) 17: myColumn.AutoIncrement = True18: mycolumn.allowdbnull = false19: 20: 'add an integer color column21: mycolumn = mydataable.columns.add ("userid", http://aspfree.com/chapters/ sams / graphics / ccc.gifSystem.Type.GetType ( "System.Int32")) 22: myColumn.AllowDBNull = false23: 24: 'Add an Integer Column25: myColumn = myDataTable.Columns.Add ( "ProductID", http: / /aspfree.com/chapters/sams/graphics/ccc.gifSystem.Type.GetType("System.Int32 ")) 26: myColumn.AllowDBNull = false27: 28: 'Add a String Column29: myColumn = myDataTable.Columns.Add ( "ProductName", System.Type.gettype ("System.String")) 30: mycolumn.allowdbnull = false31: 32: 'Add a decimal column33: mycolumn = myDataTable.Columns.Add ( "ProductPrice", http: //aspfree.com/chapters/sams/graphics/ccc.gifSystem.Type.GetType ( "System.Decimal")) 34: myColumn.AllowDBNull = false35: 36: ' Add Some Data37: myrand = new random38: for i = 0 to 2039: ProductID = myrand.next (5) 40: myrow = mydataable.newrow () 41: MyRow ("userid") = myrand.next (3) 42: myRow ( "ProductID") = productID43: myRow ( "ProductName") = "Product" & productID.toString () 44: myRow ( "ProductPrice") = 10.2545: myDataTable.Rows.Add (myRow) 46: Next47: 48: 'Display all the rows49: for each myrow in myDataTable.Rows 50: response.write (" ") 51: for Each MyColumn in MyDataTable.Columns52: Response.write (MyRow.Itemn) .tostring () & " / ") 53: Next54: NEXT555: 1: 57: 7.5 Filter data in DataTable 1: <% @ Import namespace =" system.data "%> 2: <% @ Import namespace =" system.data.sqlclient "%> 3: 4: <% 5: DIM MyConnection As SqlConnection6: Dim MyDataAdapter as Sqldataadapter7: Dim MyDataTASet8: Dim MyDataTable As DataTable9: Dim Myrow AS Dataro w10: Dim selectRows () As DataRow11: 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 转载请注明原文地址:https://www.9cbs.com/read-51909.html 9cbs New Post(0)