asp.net 使用(sql server)带输入参数的存储过程

ADO.NET 执行带参数的存储过程之前,要将参数传递给存储过程,这个工作通过SqlParameter类来完成.SqlParameter类位于System.Data.SqlClient命名空间中, 常用的属性为,Direction,ParameterName,Size,SqlDbType,Value

SqlDbType属性是SqlDbType类型枚举值,,使用方法如: SqlDbType.VarChar
同样,Direction属性是ParameterDirection类型枚举值.使用方法如: ParameterDirection.Input

//创建SqlParameter对象,并指定参数名,参数数据类型及长度.
   SqlParameter para=new SqlParameter("@country",SqlDbType.VarChar,15);
//指定参数为输出类型
  para.Direction=ParameterDirection.Output;
//给参数赋值
  para.Value="中国";

---

下面看一个简单的示例:

  在Sql Server 中创建的存储过程:
    

 create procedure people_select
  @country varchar(15) 
as
  select * from people where country=@country
go

前台页面的代码:

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class procInput : System.Web.UI.Page
{
     string strConn = System.Configuration.ConfigurationManager.AppSettings["sqlConnectionStringusertest"];
     SqlConnection conn = null;
     SqlCommand cmd = null;
    
    
    protected void Page_Load(object sender, EventArgs e)
    {

      
        if (!IsPostBack)
        {
            conn = new SqlConnection(strConn);
            conn.Open();
            cmd = new SqlCommand("select country from people",conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds,"country");
            DropDownList1.DataSource=ds.Tables["country"];
            DropDownList1.DataTextField = "country";
            DropDownList1.DataValueField = "country";
            DropDownList1.DataBind();
            if (conn.State== ConnectionState.Open)
            {
                conn.Close();
            }

           

        }
     }

    protected void Button1_Click(object sender, EventArgs e)
    {
        conn = new SqlConnection(strConn);
        conn.Open();
       cmd = new SqlCommand("people_select", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter para = new SqlParameter("@country", SqlDbType.VarChar, 15);
        para.Direction = ParameterDirection.Input;
        para.Value = DropDownList1.SelectedItem.Value;
        cmd.Parameters.Add(para);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "tbpeople");
        GridView1.DataSource = ds.Tables["tbpeople"];
        GridView1.DataBind();
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }
}