ado.net資料操作全接觸三(預存程序,datasets)

來源:互聯網
上載者:User
ado|預存程序|資料 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",
ccc.gifSqlDbType.Varchar, 30 ))
16: myCommand.Parameters( "@FirstName" ).Value = FirstName
17:
18: myCommand.Parameters.Add( New SqlParameter( "@LastName",
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
ccc.gifSqlParameter( "RETURN VALUE", SqlDbType.INT ))
16: myParam.Direction = ParameterDirection.ReturnValue
17:
18: myParam = myCommand.Parameters.Add( New
ccc.gifSqlParameter( "@FirstName", SqlDbType.Varchar, 50 ))
19: myParam.Direction = ParameterDirection.Input
20: myParam.Value = "Robert"
21:
22: myParam = myCommand.Parameters.Add( New
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
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",
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",
ccc.gifSystem.Type.GetType("System.Int32") )
22: myColumn.AllowDBNull = false
23:
24: ' Add an Integer Column
25: myColumn = myDataTable.Columns.Add("ProductID",
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",
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'",
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:
 

相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。