使用ADO.NET操作SQL Server存储过程

1、调用无输入输出参数的存储过程
创建无输入输出参数的存储过程

create   procedure  testProc 
as  
select  pub_id,title_id,price,pubdate
from  titles
where  price  is   not   null
order   by  pub_id

使用下面的程序来调用这个存储过程。在调用该存储过程时，需要告诉Command对象要调用的是存储过程。

< form id = " form1 "  runat = " server " >
     < div >
     < asp:DataGrid ID = " dg "  runat = " server "   />
     </ div >
</ form >

SqlConnection Conn;
protected   void  Page_Load( object  sender, EventArgs e)
    {
        Conn  =   new  SqlConnection( " server=localhost;database=pubs;uid=sa;pwd='' " );
        SqlCommand Comm  =   new  SqlCommand( " testProc " , Conn);
        Comm.CommandType  =  CommandType.StoredProcedure;
        Conn.Open();
        SqlDataReader dr  =  Comm.ExecuteReader();
        dg.DataSource  =  dr;
        dg.DataBind();
        Conn.Close();
    }

2、调用带输入输出参数的存储过程

CREATE   PROCEDURE  sp_CheckPass 
( @CHKName   VARCHAR ( 30 ), @CHKPass   VARCHAR ( 30 ), @ISValid   VARCHAR ( 12 ) OUTPUT)
  AS
IF   EXISTS ( SELECT  UserName  from  WebUsers  WHERE  UserName = @CHKName   and
UserPass = @CHKPass )
     SELECT   @ISValid = ' GOOD '
ELSE
     SELECT   @ISValid = ' BAD '

SqlConnection Conn;
     protected   void  Page_Load( object  sender, EventArgs e)
    {
        Conn  =   new  SqlConnection( " server=localhost;database=pubs;uid=sa;pwd='' " );
        SqlCommand Comm  =   new  SqlCommand( " sp_CheckPass " , Conn);
        Comm.CommandType  =  CommandType.StoredProcedure;
        SqlParameter parm  =  Comm.Parameters.Add( " @CHKName " , SqlDbType.VarChar,  30 );
        parm.Value  =   " aa " ;
        parm  =  Comm.Parameters.Add( " @CHKPass " , SqlDbType.VarChar,  30 );
        parm.Value  =   " aa " ;
        parm  =  Comm.Parameters.Add( " @ISValid " , SqlDbType.VarChar,  12 );
        parm.Direction  =  ParameterDirection.Output;

        Conn.Open();
        SqlDataReader dr  =  Comm.ExecuteReader();
        Response.Write(Comm.Parameters[ " @ISValid " ].Value);
        Conn.Close();
    }