摘自：http://www.cnblogs.com/smhy8187/articles/677742.html
|
ASP.NET技巧(二)-在asp.net中执行存储过程
2006-12-18 20:27:15
大中小
声明:本例用的数据库是系统提供的pubs数据库,表是是employee,编程语言用C#
1.执行不带参数的存储过程存储过程：
create proc example1
as
begin
select top 6 emp_id,fname,lname
from employee
end
执行代码：
SqlConnection Conn = new SqlConnection("server=.;database=pubs;uid=sa;pwd=");
Conn.Open();
SqlCommand cmd = new SqlCommand("example1", Conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
//输出执行结果
string table = "<table border='1'><tr>";
int field = dr.FieldCount;
for (int i = 0; i <= field - 1; i++)
{
table += "<TD>" + dr.GetName(i) + "</td>";
}
table += "</tr>";
while (dr.Read())
{
table += "<tr>";
for (int i = 0; i <= field - 1; i++)
{
table += "<TD>" + dr.GetValue(i) + "</td>";
}
table += "</tr>";
}
table += "</table>";
Conn.Close();
Response.Write(table);
2.执行带普通参数的存储过程
存储过程：
create proc example2
@empid varchar(9)
as
begin
select emp_id,fname,lname from employee
where emp_id=@empid
end
执行代码：
SqlConnection Conn = new SqlConnection("server=.;database=pubs;uid=sa;pwd=");Conn.Open(); SqlCommand cmd = new SqlCommand("example2", Conn); cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@empid", SqlDbType.VarChar, 9);
cmd.Parameters["@empid"].Value = "PMA42628M";
SqlDataReader dr = cmd.ExecuteReader();
//显示执行结果
string table = "<table border='1'><tr>";
int field = dr.FieldCount;
for (int i = 0; i <= field - 1; i++)
{
table += "<TD>" + dr.GetName(i) + "</td>";
}
table += "</tr>";
while (dr.Read())
{
table += "<tr>";
for (int i = 0; i <= field - 1; i++)
{
table += "<TD>" + dr.GetValue(i) + "</td>";
}
table += "</tr>";
}
table += "</table>";
Conn.Close();
Response.Write(table);
3.执行带output型参数的存储过程
存储过程：
create proc example3 @Count int output as begin select @Count=(select count(*) from employee) end
执行代码：
SqlConnection Conn = new SqlConnection("server=.;database=pubs;uid=sa;pwd=");
Conn.Open();
SqlCommand cmd = new SqlCommand("example3", Conn); cmd.CommandType =andType.StoredProcedure;
cmd.Parameters.Add("@Count", SqlDbType.VarChar, 9);
cmd.Parameters["@count"].Direction = ParameterDirection.Output;
SqlDataReader dr = cmd.ExecuteReader();
Conn.Close();
int c =Convert.ToInt32(cmd.Parameters["@count"].Value); Response.Write(c.ToString());
4.执行用return返回的存储过程
存储过程：
create proc example4as
Begin
declare @Count int
select @Count=(select count(*) from employee)
return @Countend
执行代码：
SqlConnection Conn = new SqlConnection("server=.;database=pubs;uid=sa;pwd=");
Conn.Open();
SqlCommand cmd = new SqlCommand("example4", Conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("Count", SqlDbType.VarChar, 9); cmd.Parameters["count"].Direction = ParameterDirection.ReturnValue;
SqlDataReader dr = cmd.ExecuteReader();
Conn.Close();
int c =Convert.ToInt32(cmd.Parameters["count"].Value); Response.Write(c.ToString());