C#預存程序簡單應用

1.建立預存程序

create procedure pr_login

@userid char(50),
@passwd char(50),
@out char(1)  output

AS
begin
select @out=usertype from logintb where username=@userid and userpass=@passwd
end
go

2.調用制行它

  SqlConnection con = new SqlConnection("Datasource=.;database=mydb;uid=sa;pwd=;");            
               con.Open();
               string uid=Convert.ToString ( this.textBox1.Text);
               string pwd=Convert.ToString ( this.textBox2.Text);

                SqlCommand mycmd = new SqlCommand("pr_login ", con);
                mycmd.CommandType = CommandType.StoredProcedure;
                mycmd.Parameters .Add ("@userid",SqlDbType.Char, 50);

               // may use:  mycmd.Parameters .Add (new SqlParameter      ("@userid",SqlDbType.Char, 50));

                mycmd.Parameters .Add ("@passwd",SqlDbType.Char  ,50);
                mycmd .Parameters ["@userid"].Value =uid;
                mycmd.Parameters ["@passwd"].Value =pwd;
                mycmd.Parameters.Add ("@out",SqlDbType.Char ,1);

               //輸入參數可以不申明,但輸出一定要申明

                mycmd.Parameters ["@out"].Direction =ParameterDirection .Output ;

                mycmd.ExecuteNonQuery();
                string result=mycmd.Parameters["@out"].Value.ToString();

                this.Close();