1. procedure 有輸入參數，無返回值
procedure ：
USE [biyi]
GO
create proc TEST2 (@v1 nvarchar(50),@v2 nvarchar(50))
as
SELECT * FROM PROCE
WHERE @v1>0 AND @v2>0
GO
C#中調用
String ConnStr = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "TEST2";//Procedure Name is TEST2
cmd.CommandType = CommandType.StoredProcedure;
IDataParameter[] parameters = {
new SqlParameter("@v1", c) , //Procedure 的第壹個輸入參數
new SqlParameter("@v2", SqlDbType.NVarChar,50), //Procedure 的第二個輸入參數
};
parameters[0].Value = "1";
parameters[1].Value = "2";
cmd.Parameters.Add(parameters[0]); //或者 直接 cmd.Parameters.Add( "@v1",SqlDbType.NVarChar,50);
cmd.Parameters.Add(parameters[1]);
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
this.GridView1.DataSource = ds.Tables[0];
this.GridView1.DataBind();
}
2. procedure 有輸入參數，也有返回值
Procedure:
USE [biyi]
GO
create proc TEST3 (@v1 nvarchar(50),@v2 nvarchar(50))
as
if @v1 > @v2
return 1
else return 2
GO
C#中調用:
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "TEST3";//Procedure Name
cmd.CommandType = CommandType.StoredProcedure;
IDataParameter[] parameters = {
new SqlParameter("@v1", SqlDbType.NVarChar,50) , //Procedure 的第壹個輸入參數
new SqlParameter("@v2", SqlDbType.NVarChar,50), //Procedure 的第二個輸入參數
new SqlParameter("return", SqlDbType.NVarChar,50), //返回值
};
parameters[0].Value = "1";
parameters[1].Value = "2";
parameters[2].Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
cmd.Parameters.Add(parameters[2]);
conn.Open();
cmd.ExecuteNonQuery();
Label1.Text = parameters[2].Value.ToString();
}