C调用存储过程简单完整例子剖析.doc

C#调用存储过程简单完整例子?? ??/itblog/article/details/752869 创建存储过程Create Proc dbo.存储过程名存储过程参数AS执行语句RETURN执行存储过程GO DECLARE @iRet INT, @PKDisp VARCHAR(20) SET @iRet = 1 Select @iRet = CASE WHEN @PKDisp = 一 THEN 1 WHEN @PKDisp = 二 THEN 2 WHEN @PKDisp = 三 THEN 3 WHEN @PKDisp = 四 THEN 4 WHEN @PKDisp = 五 THEN 5 ELSE 100 END DECLARE @i INT SET @i = 1 WHILE @i10 BEGIN set @i=@i+1 PRINT @i END DECLARE @d INT set @d = 1 IF @d = 1 BEGIN -- 打印 PRINT 正确 END ELSE BEGIN PRINT 错误 END ?CREATE PROC P_TEST@Name VARCHAR(20),@Rowcount INT OUTPUTASBEGIN?SELECT * FROM T_Customer WHERE?NAME=@Name?SET? @Rowcount=@@ROWCOUNTENDGO--存储过程调用如下:DECLARE @i INTEXEC P_TEST A,@i OUTPUTSELECT @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