asp.net(c#) 下SQL存储过程使用详细实例

记取记录集
create procedure getArticle
as
select * from Article_Content

GO
asp.net 调用方法

  SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = Data.Connstr();
        Conn.Open();
        SqlDataAdapter sdr=new SqlDataAdapter();
        sdr.SelectCommand = new SqlCommand("getArticle", Conn);
        sdr.SelectCommand.CommandType=CommandType.StoredProcedure;
        DataSet rs = new DataSet();
        sdr.Fill(rs);
        sdr.Dispose();
        Conn.Dispose();
        Response.Write(rs.Tables[0].Rows.Count);
       

-------------------------------------------------------------------------------------
删除记录(带输入参数)
create procedure DelArticle
@Id int
as
delete from Article_Content where Id=@Id

GO

asp.net调用方法
 if (Request.QueryString["Id"] != null)
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = Data.Connstr();
            Conn.Open();
            SqlCommand cmd = new SqlCommand("DelArticle", Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Id", SqlDbType.Int,4).Value = int.Parse(Request.QueryString["Id"].ToString());
            Response.Write(cmd.ExecuteNonQuery());
            cmd.Dispose();
            Conn.Dispose();
        }

-------------------------------------------------------------------------------------

添加记录（带输出参数）,修改记录和方法一样，就是用update。

设置两个输出参数，让大家对输出多参数有点理解。

create proc Add_Channel
@Channel_Title nvarchar(50),
@Channel_Name nvarchar(50),
@Channel_Ename nvarchar(50),
@Channel_Templet int,
@Keyword nvarchar(100),
@rtv int output,
@rtv1 int output,
@KDescription nvarchar(200)
as
 if exists(select * from Channel where Channel_Name = @Channel_Name)
 begin
  set @rtv=0
  set @rtv1=5
 end
 else
 if exists(select * from Channel where Channel_Ename = @Channel_Ename)
 begin
  set @rtv=1
  set @rtv1=6
 end
 else
 begin
  insert into Channel(Channel_Title,Channel_Name,Channel_Ename,Channel_Templet,Keyword,KDescription)values(@Channel_Title,@Channel_Name,@Channel_Ename,@Channel_Templet,@Keyword,@KDescription)
  set @rtv=2
  set @rtv1=7
 end

 
go

asp.net调用方法

public partial class Default2 : System.Web.UI.Page
{
    public string rvs(string str){ //根据参数值返回对应的提示信息
        if(str=="0"){
            return "名称重复";
        }
        else if(str=="1"){
            return "英文名称重复";
        }
        else{
            return "添加成功！";
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = Data.Connstr();
        Conn.Open();
        SqlCommand cmd = new SqlCommand("Add_Channel", Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Channel_Title",SqlDbType.NVarChar,50).Value=Channel_Title.Text.Trim();
        cmd.Parameters.Add("@Channel_Name",SqlDbType.NVarChar,50).Value=Channel_Name.Text.Trim();
        cmd.Parameters.Add("@Channel_Ename",SqlDbType.NVarChar,50).Value=Channel_Ename.Text.Trim();
        cmd.Parameters.Add("@Channel_Templet",SqlDbType.Int,4).Value=int.Parse(Channel_Templet.Text.Trim());
        cmd.Parameters.Add("@Keyword",SqlDbType.NVarChar,100).Value=Keyword.Text.Trim();
        cmd.Parameters.Add("@KDescription",SqlDbType.NVarChar,200).Value=KDescription.Text.Trim();
        cmd.Parameters.Add("@rtv", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@rtv1", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
        Conn.Dispose();
        Conn.Close();
        Response.Write("<script>alert(" + rvs(cmd.Parameters["@rtv"].Value.ToString()) + "[" + cmd.Parameters["@rtv1"].Value.ToString()+ "]);window.history.back();</script>");
        
    }
}