asp.net中怎樣調用存儲過程和存儲過程的寫法

2019-11-17 04:06:16

字體：大中小

來源：轉載

供稿：網友

----創建一個只有輸入參數的存儲過程

create PRocedure proc_user
@name varchar(20),
@PassWord varchar(100)
as
select * from loginuser
where name like @name

---創建一個有輸入和輸出參數的存儲過程

create procedure proc_usertext
@name varchar(20),
@blog varchar(100) output
as
select @blog = blog from loginuser where name = @name

---創建一個有輸入和返回參數的存儲過程

create PROCEDURE returnval
@name varchar(100),
@blog varchar(100) output
AS
       begin
       select @blog = blog from loginuser where name = @name
       if(@blog is null)
          set @blog = ''你還沒有申請博客''
       return execute(@blog )  --數據數型的轉換
       end

在 asp.net中的調用

protected void Page_Load(object sender, EventArgs e)
    {
        DataBind();
    }

    private void DataBind()
    {
        //只帶輸入參數的存儲過程的寫法。
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
        SqlCommand cmd = new SqlCommand("proc_user", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@name", "l%");
        cmd.Parameters.AddWithValue("@Password", "lcs9702");
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        DropDownList1.DataSource = dr;
        DropDownList1.DataTextField = "name";
        DropDownList1.DataValueField = "use_id";
        DropDownList1.DataBind();
    }

    private void textDataBind()
    {
        //帶輸入，輸出參數的存儲過程的寫法。
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
        SqlCommand cmd = new SqlCommand("proc_usertext", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@name", "love");
        cmd.Parameters.Add("@blog", SqlDbType.VarChar,100);
        cmd.Parameters["@blog"].Direction = ParameterDirection.Output;
        //cmd.Parameters.Add(new SqlParameter("@blog",SqlDbType.VarChar,100,ParameterDirection.Output,false,0,0,string.Empty,DataRowVersion.Default,null));
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        cmd.ExecuteNonQuery();
        TextBox1.Text = cmd.Parameters["@blog"].Value.ToString();
        con.Close();

    }

    private void returnDataBind()
    {
        //帶輸入，返回參數的存儲過程的寫法。
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
        SqlCommand cmd = new SqlCommand("returnval", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@name", "lcs9702");
        cmd.Parameters.Add(new SqlParameter("@blog", SqlDbType.VarChar, 100, ParameterDirection.Output, false, 0, 0, string.Empty, DataRowVersion.Default, null));
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        cmd.ExecuteNonQuery();
        TextBox2.Text = cmd.Parameters["@blog"].Value.ToString();
        con.Close();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        textDataBind();
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        returnDataBind();
    }

上一篇：ASP.NET防SQL注入腳本程序 v2.0

下一篇：C#獲取完整的CPU信息