文章来源:http://blog.csdn.net/21aspnet/archive/2007/03/21/1535751.aspx 作者:清清月儿
CREATE
PROC
P_TEST
@Name VARCHAR ( 20 ),
@Rowcount INT OUTPUT
AS
BEGIN
SELECT * FROM T_Customer WHERE NAME = @Name
SET @Rowcount = @@ROWCOUNT
END
GO
@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 行)
*/
EXEC P_TEST ' A ' , @i OUTPUT
SELECT @i
-- 结果
/**//*
Name Address Tel
---------- ---------- --------------------
A Address Telphone
(所影响的行数为 1 行)
-----------
1
(所影响的行数为 1 行)
*/
C#代码:(用到两个测试控件,DataGrid1(用于显示绑定结果集合),Lable(用于显示存储过程返回单值)
//
添加数据库引用
using System.Data.SqlClient;
// ......
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();
}
using System.Data.SqlClient;
// ......
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();
}