存储过程demo

 

用ASP.NET与SQL SERVER可是缘份最好了，稍大的程序一般第一先考虑的是SQL SERVER，只是一些很考虑经济的才使用ACCESS等了。用SQL SERVER，为了使数据库的效率更好，一般都会才取存储过程，因存储过程执行速度快，并且可以实现一些高级的查询等功能。比如传入一些数据参数，但执行的SQL过程可能不同等。

 

下面就来个例子，建立一新的角色，要求角色的名字不能重复，以下是一存储过程。

 

CREATE PROCEDURE sp_AccountRole_Create

@CategoryID int,

@RoleName nvarchar(10),

@Description nvarchar(50),

@RoleID int output ●↑dfsfe▲※fefs

AS

DECLARE Count int

-- 查找是否有相同名称的记录

SELECT Count = Count(RoleID) FROM Account_Role WHERE

RoleName = RoleName

IF Count = 0

INSERT INTO Account_Role

(CategoryID, RoleName, Description) valueS

(@CategoryID, RoleName, Description)

SET RoleID = IDENTITY

RETURN 1

GO

 

 

 

执行存储过程的C#过程：

 

SqlConnection DbConnection = new SqlConnection(mConnectionString);

SqlCommand command = new SqlCommand( "sp_AccountRole_Create", DbConnection );

DbConnection.Open(connectString);

okimg.comヒフdfチタ▲▲チタネヌ

// 废置SqlCommand的属性为存储过程

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@CategoryID", SqlDbType.Int, 4);

command.Parameters.Add("@RoleName", SqlDbType.NVarChar, 10);

command.Parameters.Add("@Description", SqlDbType.NVarChar, 50);

command.Parameters.Add("@RoleID", SqlDbType.Int, 4);

// 返回值

command.Parameters.Add("Returnvalue",

SqlDbType.Int, 

 

 

运行情况：数据部分写入表中，有一字段（MSISDN）值被意外截掉第六位后的数据，另一字段(SHORTMSG)值全部丢失，其余三个(ID,RECVER,CREATETIME)值正常写入. 

========================================================================== 
C#代码： 
//存储过程名称 
   string spName = "ADDSMS"; 

   //建立连接 
   OracleConnection conn = new OracleConnection(this.oConnectionString); 

   //这存储过程建立cmd 
   OracleCommand cmd = new OracleCommand(); 
   cmd.Connection = conn; 
   cmd.CommandText = spName; 
   cmd.CommandType = CommandType.StoredProcedure; 

   //增加参数 
   cmd.Parameters.Add("MSISDN",OracleType.VarChar,12); 
   cmd.Parameters.Add("SHORTMSG",OracleType.VarChar,160); 
   cmd.Parameters.Add("ID",OracleType.Number); 

   //增加参数值 
   cmd.Parameters[0].value = MSISDN; 
   cmd.Parameters[1].value = ShortMSG; 
    
   //存储过程输出值 
   cmd.Parameters[2].Direction = ParameterDirection.Output; 

   conn.Open(); 
   cmd.ExecuteNonQuery(); 
   conn.Close(); 

   //返回 
   return (int)cmd.Parameters[2].Direction; 
========================================================================== 
存储过程： 
PROCEDURE ADDSMS 
( 
   MSISDN IN VARCHAR2, 
   SHORTMSG IN VARCHAR2, 
   ID OUT NUMBER 
) 
AS 

   CURRENTTIME DATE; 
   RECVER VARCHAR2(12); 
BEGIN 
   SELECT SEQ_SMS.NEXTVAL INTO ID FROM DUAL; 
   CURRENTTIME := SYSDATE; 
   RECVER := ’5’; 

   INSERT INTO RECEIVER (ID,RECVER,MSISDN,SHORTMSG,CREATETIME) 
      valueS (ID,RECVER,MSISDN,SHORTMSG,CURRENTTIME); 
   EXCEPTION 
   WHEN OTHERS THEN 
      ROLLBACK; 
END ADDSMS; 
========================================================================== 
ID         RECVER      MSISDN         SHORTMSG      CREATETIME 
正常写入     正常写入    第六位后丢失   全部丢失      正常写入 

 

CREATE PROCEDURE Prc_Ad_Login
@AdName varchar(32),
@AdPwd nchar(32),
@Count int output,
@Err varchar(64) output
 AS

if exists(select intsuid from sysuser where varsuname=@AdName)
begin
 declare @suid int
 declare @dteErrs datetime
 declare @npwd nchar(32)
 declare @Lock bit
 declare @intErr int
 declare @Errpart int
 declare @ErrCount int
 select @suid=intsuid, @dteErrs=dteErr,@intErr=intErrCount,@npwd=narsupwd,@lock=bitLock from sysuser where varsuname=@Adname
 select @ErrCount=intsuerrcount,@Errpart=intsuerrminute from sysbasic
 if(@npwd=@Adpwd)
 begin
  if(@Lock=1)
  begin
   select @Count=2
   select @Err='你的帐户已被锁定,请联系站长!'
  end
  else
  begin
   
   select @Count=0
  end
 end
 else
 begin
  if(@Lock=0)
  begin
   if(datediff(n,@dteErrs,getdate())<@Errpart)
   begin
    if(@intErr<@ErrCount)
    begin
     update sysuser set intErrCount=intErrCount+1,dteErr=getdate() where intsuid=@suid
     if(@intErr=(@ErrCount-1))
     begin
      select @Count=4
      select @Err='登录错误过多,你的帐号被系统暂时锁定!'
     end
     else
     begin
      select @Count=1
      select @Err='用户名或密码错误!'
     end
    end
    else
    begin
     update sysuser set dteErr=getdate() where intsuid=@suid
     select @Count=3
     select @Err='你的帐号在一定时间内不允许登录!'
    end
   end
   else
   begin
    update sysuser set dteErr=getdate(),intErrCount=1 where intsuid=@suid
    select @Count=1
    select @Err='用户名或密码错误!'
   end
  end  
 end
end
else
begin
 select @Count=1
 select @Err='用户名或密码错误!'
end
GO

存储过程 p_sys_Login 定义如下： 

CREATE PROCEDURE p_sys_Login 

@argUserID varchar[color=#0000ff](20), --用户名 

@argPassword varchar(20), --密码 

@argResult varchar(50) OUTPUT --登录结果 

AS 

/* 

... ... 

*/ 


下面演示如何在C#中用最简洁有效的代码执行该存储过程并返回数据： 


/// <summary> 

/// 用户登录验证 

/// </summary> 

/// <param name="userID">用户名</param> 

/// <param name="password">密码</param> 

public void Login(string userID, string password) 

{ 

//数据库连接字符串存储在 Web.config 中 

string cnnString = ConfigurationSettings.AppSettings["ConnectionString"]; 

SqlConnection cnn = new SqlConnection(cnnString); 


// 


string sql = string.Format("EXEC p_sys_Login '{0}', '{1}', @Result OUTPUT", 

userID, password); 

SqlCommand cmd = new SqlCommand(sql, cnn); 


//建立并添加和“@Result OUTPUT”对应的参数 

SqlParameter paramResult = new SqlParameter("@Result", SqlDbType.VarChar, 50); 

paramResult.Direction = ParameterDirection.Output; 

cmd.Parameters.Add(paramResult); 


cnn.Open(); 

cmd.ExecuteNonQuery(); 

cnn.Close(); 


//获取存储过程返回的结果 

string result = paramResult.Value.ToString(); 


//... ... 

}