ado.net数据操作全接触三(存储过程,datasets)

原创 2005年03月02日 10:51:00
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: %>

相关文章推荐

ADO.NET操作存储过程 教程

  • 2010年01月31日 16:59
  • 16KB
  • 下载

ADO.NET之利用存储过程录入数据

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; ...
  • liheao
  • liheao
  • 2016年02月08日 16:10
  • 110

ADO.NET存储过程参数生成-SQL版

  • 2008年08月04日 08:51
  • 163KB
  • 下载

[Ado.net+存储过程] 员工管理系统

  • 2012年12月08日 10:24
  • 240KB
  • 下载

C#二十六 使用Ado.Net调用存储过程

存储过程是连接式访问数据库的一种延伸,主要是通过命令对象调用数据库系统中的存储过程来完成的。存储过程可以带参数,也可以不带参数,可以返回结果页可以没有返回结果。存储过程执行速度快、允许模块化程序设计并...

Ado.net+存储过程] 员工管理系统

  • 2012年11月15日 07:50
  • 259KB
  • 下载

用ADO.NET调用存储过程.ppt

  • 2009年08月03日 19:53
  • 126KB
  • 下载

使用 ADO.NET 访问 Oracle 9i 存储过程

本文讨论了如何使用 ADO.NET 访问 Oracle 存储过程(称为 SQL 编程块)和函数(返回单个值的编程块)。您可以使用以下托管数据提供程序连接到 Oracle 数据库:Microsoft ....
  • qiupuge
  • qiupuge
  • 2011年05月26日 10:00
  • 190

根据存储过程名字生成ADO.NET数据库访问代码

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER  PROCEDURE [dbo].[sqltoolforexcuteandadapter](@ob...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ado.net数据操作全接触三(存储过程,datasets)
举报原因:
原因补充:

(最多只允许输入30个字)