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: Next19:20: %>21:7.2使用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自动显示一个表1: <%@ Import Namespace="System.Data" %>2: <%@ Import NameSpace="System.Data.SqlClient" %>3:4: <%5: Dim myConnection As SqlConnection6: Dim myDataAdapter As SQLDataAdapter7: Dim myDataSet As DataSet8: Dim myDataTable As DataTable9:10: Dim RowCount As Integer11: Dim ColCount 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.Count20: ColCount = myDataSet.Tables( "Authors" ).Columns.Count21:22: Response.Write( "<table border=1>" )23: For i = 0 To RowCount - 124: Response.Write( "<tr>" )25: For k = 0 To ColCount - 126: Response.WRite( "<td>" )27: Response.Write( myDataSet.Tables( "Authors" ).Rows( i ).Item( k, DataRowVersion.Current ).toString() )28: Response.Write( "</td>" )29: Next30: Response.WRite( "</tr>" )31: Next32: Response.Write( "</table>" )33: %>34:7.4建立一个datatable1: <%@ Import Namespace="System.Data" %>2: <%3: Dim myDataTable 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 Column21: myColumn = myDataTable.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 = myDataTable.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.Rows50: Response.Write( "<hr>" )51: For each myColumn in myDataTable.Columns52: Response.Write( myRow.Item( myColumn ).toString() & " / " )53: Next54: Next55: %>56:57:7.5在datatable中过滤数据1: <%@ Import Namespace="System.Data" %>2: <%@ Import NameSpace="System.Data.SqlClient" %>3:4: <%5: Dim myConnection As SqlConnection6: Dim myDataAdapter As SqlDataAdapter7: Dim myDataSet As DataSet8: Dim myDataTable As DataTable9: Dim myRow As DataRow10: 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", DataViewRowState.CurrentRows )17:18: For each myRow in selectRows19: Response.Write( myRow.Item( "title" ) )20: Next21: %>7.6 DataView过滤1: <%@ Import Namespace="System.Data" %>2: <%@ Import NameSpace="System.Data.SqlClient" %>3: <%4: Dim myConnection As SqlConnection5: Dim myDataAdapter As SqlDataAdapter6: Dim myDataSet As DataSet7: Dim myDataTable As DataTable8: Dim myDataView As DataView9: Dim myRow As DataRowView10: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" ).DefaultView16: myDataView.RowFilter = "type='popular_comp'"17: myDataView.Sort = "title DESC"18:19: For each myRow in myDataView20: Response.Write( myRow( "title" ) )21: Next22: %>