EN ...我们这样来作个简单的操作来理解这个问题
1、我们先再Northwind数据库中新建一个简单的存储过程test
代码如下:
CREATE PROCEDURE test
(
@ID int
)
as
Select CategoryName from Categories where CategoryID=@ID
GO
2、然后我们在页面中放上Textbox1,Button1,Datagrid1
然后在代码中首先引入import system.data.sqlclient
3、添加以下这段代码
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click, Button1.Click
Dim conn As New SqlConnection("server=localhost;user id=sa;password=xxx;database=Northwind;")
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "test"
cmd.Connection = conn
cmd.Parameters.Add("@ID", TextBox1.Text)
Dim dtr As SqlDataReader
conn.Open()
dtr = cmd.ExecuteReader
DataGrid1.DataSource = dtr
DataGrid1.DataBind()
End Sub
______________________________________________________________________________
你想知道的焦点应该是:Text文本框中输入的“字符串”是怎么传递给StoreProcedure的并能把结果计算得出的。
程序是这么运作的:在代码中,我们新建了一个cmd对象变量,我们给它的parameters添加了一个@ID变量,并且这个参数的值为Textbox1.text【cmd.Parameters.Add("@ID", TextBox1.Text)】,并且指明了cmd的命令类型为存储过程【cmd.CommandType = CommandType.StoredProcedure】,而且存储过程的名字为test【cmd.CommandText = "test"】因此在执行的时候,它会到数据库中找到名为test的存储过程,并且找到这个存储过程中@ID这个参数,并且将程序中声明的@ID这个参数传给它(即把TextBox1.Text的值传给它),然后存储过程执行Select CategoryName from Categories where CategoryID=@ID,程序通过dtr = cmd.ExecuteReader把存储过程返回的结果读出来,并且作为数据源与Datagrid1绑定
利用天天.NET的存储过程:加上这个应当就可以了。
public SqlDataReader GetEmployee(string EmployeeID)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("EmployeeDetals", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
select
@FullName=FullName,
@Password=Password,
@Email=Email,
@Department=Department,
@BirthDate=BirthDate,
@HireDate=HireDate,
@Phone=Phone
from
Employees
where
EmployeeID=@EmployeeID
GO