在.net中调用存储过程写法

偶尔会忘记在.net中调用存储过程写法，这里记录一下

 

---创建一个只有输入参数的存储过程

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

在.net中的调用

    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();
    }