<% @ Page Language="VB" Debug="true" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>第三十例  Sql Server数据库中的存储过程</title>
<Script Language="VB" Runat="Server">

'定义myProStr字符串用以联结Sql数据库
Dim myProStr As String = "server=cm\cm;Trusted_Connection=yes;database=model"
'创建数据联结对象实例
Dim myConn As SqlConnection

'页面提交更新时执行的代码
public Sub page_load(sender as Object,e as EventArgs)
	'if语句用来控制显示数据控件的可见性
	if not page.ispostback then
	div1.visible = false
	else
	div1.visible = true
	Button1.visible = false
	end if
End Sub

'按钮控件的事件
public Sub btn1_click(sender as Object,e as EventArgs)
'创建命令集对象，数据集对象
Dim myAda As SqlDataAdapter
Dim myDataSet as DataSet = new DataSet()
'定义整型变量mid为DropDownList控件被选项的值
Dim mid as integer = 0

'创建同数据库的联结
myConn = New SqlConnection(myProStr)
myAda = New SqlDataAdapter("pro_all", myConn)
'设置命令对象类型为存储过程
myAda.SelectCommand.CommandType = CommandType.StoredProcedure

'在命令集中定义参数，并接收数据库返回的存储过程的输出
myAda.SelectCommand.Parameters.Add(New SqlParameter("@count", SqlDbType.Int))
myAda.SelectCommand.Parameters("@count").Direction = ParameterDirection.Output

'将查询结果存入DataSet
myAda.Fill(myDataSet,"member1")

'显示数据记录条数
Label1.Text = System.Math.Ceiling(myAda.SelectCommand.Parameters("@count").Value)

'自定义一个数据表，用于绑定DropDownlist
Dim i as integer
Dim myDataTable = new DataTable
Dim myColumn as DataColumn
Dim myRow as DataRow

myColumn = new DataColumn
myColumn.DataType = System.Type.GetType("System.Int32")
myColumn.ColumnName = "Mid"
myColumn.Unique = true
myDataTable.Columns.Add(myColumn)

myColumn = new DataColumn
myColumn.DataType = System.Type.GetType("System.Int32")
myColumn.ColumnName = "ID"
myColumn.Unique = true
myDataTable.Columns.Add(myColumn)

for i = 1 to myAda.SelectCommand.Parameters("@count").Value
       myRow = myDataTable.NewRow()
       myRow("Mid") = i
       myRow("ID") = myDataSet.Tables("member1").Rows(i-1)("id")
       myDataTable.Rows.Add(myRow)
Next

'将DropDownlist控件选项值先赋给mid变量
mid=DropDownList1.Selecteditem.value-1
'DropDownList控件数据绑定
DropDownList1.DataSource = myDataTable.DefaultView
DropDownList1.DataTextField="ID"
DropDownList1.DataValueField="MID"
DropDownList1.DataBind()
'重新修改DropDownList控件的被选项为mid
DropDownList1.SelectedIndex = mid

'将指定记录数据再次存入DataSet
myAda.fill(myDataset,mid,1,"member2")

'两个DataGrid控件的数据绑定
DataGrid1.DataSource=myDataSet.Tables("member1").DefaultView
DataGrid1.DataBind()
DataGrid2.DataSource=myDataSet.Tables("member2").DefaultView
DataGrid2.DataBind()


End Sub



</script>

</head>
<body>
	<h3><font face="Verdana">第三十例  Sql Server数据库中的存储过程</font></h3>
	<form runat=server>
	<Asp:button id="Button1" runat="server" text="执行存储过程>>" onClick="btn1_click"/><br><br>
	<div id=div1 runat=server>
	本表中存储了<font size=+1 color=red><asp:Label id="Label1"  runat="server" /></font>条记录<br><br>
	全表中的记录显示如下：<br>
	<ASP:DataGrid id="DataGrid1" runat="server" BorderColor="black" BorderWidth="1" GridLines="Both" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" AlternatingItemStyle-BackColor="#eeeeee" /><br><br>
	其中ID=<asp:DropDownList id=DropDownList1 AutoPostBack=true runat=server OnSelectedIndexChanged=btn1_click>
	<asp:ListItem value=1 Selected=true></asp:ListItem>
	</asp:DropDownlist>的记录为：
	<ASP:DataGrid id="DataGrid2" runat="server" BorderColor="black" BorderWidth="1" GridLines="Both" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" AlternatingItemStyle-BackColor="#eeeeee" />
	</div>
	</form>
</body>
</html>





