①為什么要使用存儲過程?
因為它比sql語句執行快.

②存儲過程是什么?
把一堆sql語句羅在一起,還可以根據條件執行不通sql語句.(ax寫作本文時觀點)

③來一個最簡單的存儲過程
create procedure dbo.testprocedure_ax
as
select userid from users order by userid desc

注:dbo.testprocedure_ax是你創建的存儲過程名,可以改為:axzhz等,別跟關鍵字沖突就行了.as下面就是一條sql語句,不會寫sql語句的請回避.

④我怎么在asp.net中調用這個存儲過程?
下面黃底的這兩行就夠使了.
        public static string getcustomercname(ref arraylist arraycname,ref arraylist arrayid)
        {
            sqlconnection con=adconnection.createconnection();
            sqlcommand cmd=new sqlcommand("testprocedure_ax",con);
            cmd.commandtype=commandtype.storedprocedure;
            con.open();
            try
            {
                sqldatareader dr=cmd.executereader();
                while(dr.read())
                {
                    if(dr[0].tostring()=="")
                    {
                        arraycname.add(dr[1].tostring());
                    }
                }
                con.close();
                return "ok!";
            }
            catch(exception ex)
            {
                con.close();
                return ex.tostring();
            }
        }
注:其實就是把以前
sqlcommand cmd=new sqlcommand("select userid from users order by userid desc",con);
中的sql語句替換為存儲過程名,再把cmd的類型標注為commandtype.storedprocedure(存儲過程)

⑤寫個帶參數的存儲過程吧,上面這個簡單得有點慘不忍睹,不過還是蠻實用的.
參數帶就帶兩,一個的沒面子,太小家子氣了.

create procedure dbo.axzhz
/*
這里寫注釋
*/
@startdate varchar(16),
@enddate varchar(16)
as
 select id  from table_ax where commentdatetime>@startdate and commentdatetime<@enddate order by contentownerid desc

注:@startdate varchar(16)是聲明@startdate 這個變量,多個變量名間用【,】隔開.后面的sql就可以使用這個變量了.

⑥我怎么在asp.net中調用這個帶參數的存儲過程?

 public static string getcustomercnamecount(string startdate,string enddate,ref dataset ds)
{
            sqlconnection con=adconnection.createconnection();
//-----------------------注意這一段--------------------------------------------------------------------------------------------------------
            sqldataadapter da=new sqldataadapter("axzhz",con);

            para0=new sqlparameter("@startdate",startdate);
            para1=new sqlparameter("@enddate",enddate);
            da.selectcommand.parameters.add(para0);
            da.selectcommand.parameters.add(para1);
            da.selectcommand.commandtype=commandtype.storedprocedure;
//-------------------------------------------------------------------------------------------------------------------------------

            try
            {
                con.open();
                da.fill(ds);
                con.close();
                return "ok";
            }
            catch(exception ex)
            {
                return ex.tostring();
            }           
        }

注:把命令的參數添加進去,就ok了
鳥的,改字體顏色的東西太垃圾了,改不好,大家湊活著看.

⑦我還想看看sql命令執行成功了沒有.
注意看下面三行紅色的語句

create procedure dbo.axzhz
/*
  @parameter1 用戶名
  @parameter2 新密碼
*/
@password nvarchar(20),
@username nvarchar(20)
as
declare @err0 int
update wl_user set [email protected] where [email protected]
set @[email protected]@error
select  @err0 as err0

注:先聲明一個整型變量@err0,再給其賦值為@@error(這個是系統自動給出的語句是否執行成功,0為成功,其它為失敗),最后通過select把它選擇出來,某位高人說可以通過return返回,超出本人的認知范圍,俺暫時不會,以后再補充吧

⑧那怎么從后臺獲得這個執行成功與否的值呢?
下面這段代碼可以告訴你答案:
    public static string getcustomercname()
        {
            sqlconnection con=adconnection.createconnection();
           
            sqlcommand cmd=new sqlcommand("axzhz",con);
            cmd.commandtype=commandtype.storedprocedure;
            para0=new sqlparameter("@startdate","2006-9-10");
            para1=new sqlparameter("@enddate","2006-9-20");
            da.selectcommand.parameters.add(para0);
            da.selectcommand.parameters.add(para1);
            con.open();
            try
            {
               int32 re=(int32)cmd.executescalar();
                con.close();
                if (re==0)
                 return "ok!";
                else
                 return "false";
            }
            catch(exception ex)
            {
                con.close();
                return ex.tostring();
            }
        }
注:就是通過sqlcommand的executescalar()方法取回這個值,這句話是從msdn上找的,俺認為改成:
     int re=(int)cmd.executescalar();  99%正確,現在沒時間驗證,期待您的測試!!!

⑨我要根據傳入的參數判斷執行哪條sql語句!!~
下面這個存儲過程可以滿足我們的要求,竟然是pascal/vb的寫法,begin----end ,不是{},,,對使用c#的我來說,這個語法有點惡心.........

alter procedure dbo.selectcustomercnamecount
@customerid int
as
if @customerid=-1
 begin
 select contentownerid ,usercname,count(*) as countall from view_usercomment group by contentownerid,usercname order by contentownerid desc
 end
else
 begin
 select contentownerid ,usercname,count(*) as countall from view_usercomment where [email protected] group by contentownerid,usercname order by contentownerid desc
 end

好了,俺的水平只止于此,也夠菜鳥們喝一壺的了,還有更多東西等著我們去發現,無盡的征途!!!!!!!!!!!

最大的網站源碼資源下載站，