C# 调用存储过程简单完整的实例代码

create proc p_test@name varchar(20),
@rowcount int output
as
begin
select * from t_customer where name=@name
set @rowcount=@@rowcount
end
go
----------------------------------------------------------------------------------------
--存储过程调用如下:
----------------------------------------------------------------------------------------
declare @i int
exec p_test 'a',@i output
select @i
--结果
/*
name address tel
---------- ---------- --------------------
a address telphone

（所影响的行数为 1 行）


-----------
1

（所影响的行数为 1 行）
*/
----------------------------------------------------------------------------------------

--dotnet 部分(c#)
--webconfig 文件:
----------------------------------------------------------------------------------------
......
</system.web>

<!-- 数据库连接字符串
-->
<appsettings>
<add key="connectstring" value="server=(local);user id=sa;password=;database=test" />
</appsettings>

</configuration>
----------------------------------------------------------------------------------------
--c#代码:(用到两个测试控件,datagrid1(用于显示绑定结果集合),lable(用于显示存储过程返回单值)

代码

using system.data.sqlclient;
private void page_load(object sender, system.eventargs e)

{ // 在此处放置用户代码以初始化页面
string dbconnstr; dataset mydataset=new dataset();
system.data.sqlclient.sqldataadapter dataadapter=new system.data.sqlclient.sqldataadapter();
dbconnstr=system.configuration.configurationsettings.appsettings["connectstring"];
system.data.sqlclient.sqlconnection myconnection = new system.data.sqlclient.sqlconnection(dbconnstr);
if (myconnection.state!=connectionstate.open)
{ myconnection.open(); }
system.data.sqlclient.sqlcommand mycommand = new system.data.sqlclient.sqlcommand("p_test",myconnection);
mycommand.commandtype=commandtype.storedprocedure; //添加输入查询参数、赋予值
mycommand.parameters.add("@name",sqldbtype.varchar);
mycommand.parameters["@name"].value ="a"; //添加输出参数
mycommand.parameters.add("@rowcount",sqldbtype.int);
mycommand.parameters["@rowcount"].direction=parameterdirection.output;
mycommand.executenonquery();
dataadapter.selectcommand = mycommand;
if (mydataset!=null)
{ dataadapter.fill(mydataset,"table"); }
datagrid1.datasource=mydataset; datagrid1.databind(); //得到存储过程输出参数
label1.text=mycommand.parameters["@rowcount"].value.tostring();
if (myconnection.state == connectionstate.open) { myconnection.close();
}
}