Vb教程 Vb.net教程 Vfp教程 C/C++教程 Vc/Vc++教程 Delphi教程 Java教程 Powerbuilder
  杀毒频道 | 短信频道 | 网络电视 | 论文中心 | 学上网 | 学软件 | 网页特效 | 电脑基础 | 论坛  
  NCRE | 软考 | CET | 职称英语 | 司法考试 | 报关员 | 公务员 | CATTI | CPA考试  
  Html教程 | Css教程 | Xml教程 | Asp教程 | Asp.net | Php教程 | Jsp教程 | Linux教程 | QQ技巧  
Photoshop Illustrator ImageReady Maya教程 3D Max教程 Lightscape Coredraw教程 Authorware Autocad教程 Freehand教程
Access教程 Mysql教程 Sql server Oracle教程 Word教程 Excel教程 Powerpoint Frontpage Asp.net源码 Php源代码
Flash教程 Fireworks Dreamweaver C#教程 outlook教程 系统安装 vbscript教程 Javascript Jsp源代码 Asp源代码
您的位置:首页 >> Vb.net教程 >> 正文

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

[返回]

编程语言 web开发 数据库 网络技术 操作系统 服务器 网页设计 图形设计 办公软件 常用软件 学电脑

Copyright© www.bianceng.cn Powered by 编程入门网 All Rights Reserved.
关于本站 | 版权声明 | 联系我们 | 友情链接 |
编程入门网 版权所有