ado.net数据操作全接触三（存储过程，datasets）

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: %>