ASP.NET中使用存储过程

使用存储过程的服务service
<%@ WebService Language="C#" Class="VideoLiveWS" %>

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Configuration;

using System.IO;
using System.Text;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
//若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。 
// [System.Web.Script.Services.ScriptService]
public class VideoLiveWS  : System.Web.Services.WebService {

   
    
        //默认密钥向量
    private static byte[] Keys = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };

    //密钥
    public string KeyValue = "55555555";
    
    
    public string HelloWorld() {
        return "Hello World";
    }

   

    /// DES加密
    /// <param >待加密的字符串</param>
    /// <param >加密密钥,要求为8位</param>
    /// <returns>加密成功返回加密后的字符串，失败返回源串</returns>

    public string EncryptDES(string encryptString, string encryptKey)
    {
        try
        {
            byte[] rgbKey = Encoding.UTF8.GetBytes(encryptKey.Substring(0, 8));
            byte[] rgbIV = Keys;
            byte[] inputByteArray = Encoding.UTF8.GetBytes(encryptString);
            DESCryptoServiceProvider dCSP = new DESCryptoServiceProvider();
            MemoryStream mStream = new MemoryStream();
            CryptoStream cStream = new CryptoStream(mStream, dCSP.CreateEncryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
            cStream.Write(inputByteArray, 0, inputByteArray.Length);
            cStream.FlushFinalBlock();
            return Convert.ToBase64String(mStream.ToArray());
        }
        catch
        {
            //return encryptString;
            return "Encrypt Failed!";
        }
    }


    /// DES解密
    /// <param >待解密的字符串</param>
    /// <param >解密密钥,要求为8位,和加密密钥相同</param>
    /// <returns>解密成功返回解密后的字符串，失败返源串</returns>

    public string DecryptDES(string decryptString, string decryptKey)
    {
        try
        {
            byte[] rgbKey = Encoding.UTF8.GetBytes(decryptKey);
            byte[] rgbIV = Keys;
            byte[] inputByteArray = Convert.FromBase64String(decryptString);
            DESCryptoServiceProvider DCSP = new DESCryptoServiceProvider();
            MemoryStream mStream = new MemoryStream();
            CryptoStream cStream = new CryptoStream(mStream, DCSP.CreateDecryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
            cStream.Write(inputByteArray, 0, inputByteArray.Length);
            cStream.FlushFinalBlock();
            return Encoding.UTF8.GetString(mStream.ToArray());
        }
        catch
        {
            //return decryptString;
            return "Decrypt Failed!";
        }
    }

    [WebMethod]
    public int Log(string userid, string username,string ip, string op, string cont,string vurlid,string tim,int money)
    {

        DataAccess da = new DataAccess();
       da.RunProc("declare @d int "
                     + " select @d=count(*) from user_action"
                     + " insert into user_action(id,userid,username,ip,oper,cont,vurlid,tim,money) values( @d,"
                           + "'" + userid + "',"
                           + "'" + username + "',"
                           + "'" + ip   + "',"
                           + "'" + op   + "',"
                           + "'" + cont + "',"
                           + "'" + vurlid + "',"
                           + "'" + tim + "',"

                           + "" + money + ")");

        return 0;
    }
    
   
    [WebMethod]

    public string[] Login(string UserName, string UserPass,string IP)
    {
        DataAccess da = new DataAccess();
        

        string[] res = new string[3];
       
        res[0] = "-1";

        SqlParameter[] p = new SqlParameter[3];
        p[0] = new SqlParameter("UserName", SqlDbType.VarChar, 20);
        p[0].Value = UserName;

        p[1] = new SqlParameter("UserPass", SqlDbType.VarChar, 40);
        p[1].Value = UserPass;

        p[2] = new SqlParameter("IP", SqlDbType.VarChar, 20);
        p[2].Value = IP;

      
        SqlDataReader Dr;
        SqlCommand Cmd = da.CreateCmd("procLogin", p);
        Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        
        if (Dr.Read())
        {
            res[0] = Dr.GetValue(0).ToString();  //是否成功
            res[1] = Dr.GetValue(1).ToString(); //level
            
            res[2] = Dr.GetValue(2).ToString(); //登陆描述
        }


        return res;
        

    }

    [WebMethod]
    public int GetUseMoney(string UserId, string NameId,string VideoId, int Used)
    {


        DataAccess da = new DataAccess();
        int res ;

        res  = -1;

        SqlParameter[] p = new SqlParameter[4];
        p[0] = new SqlParameter("UserId", SqlDbType.VarChar, 20);
        p[0].Value = UserId;

        p[1] = new SqlParameter("NameId", SqlDbType.VarChar, 40);
        p[1].Value = NameId;

        p[2] = new SqlParameter("UrlId", SqlDbType.VarChar, 40);
        p[2].Value = VideoId;

        p[3] = new SqlParameter("Used", SqlDbType.VarChar, 40);   
        p[3].Value = Used;
 


        SqlDataReader Dr;
        SqlCommand Cmd = da.CreateCmd("procGetUseMoney", p);
        Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

        if (Dr.Read())
        {
            res  = Convert.ToInt16(Dr.GetValue(0).ToString());
           
        }

        return res;


    }
    [WebMethod]
    public string  GetServerURL()
    {
        
        return ConfigurationSettings.AppSettings["ServerURL"].Trim();

    }
    
    [WebMethod]

    public DataSet GetListDS(string Level1, string Level2, string Level3, string Level4)//添加用户验证？
    {
        //相关的数据库：主要涉及的表有user（验证用户）, video, video_url, jk_type(一级分类名称), jk_class（二级分类名称）, jk_sort（三级）




        string sql;// 


        if (Level1 == "")
            sql = "select DISTINCT jk_type.t_name,jk_type.t_id"
                    + " from video,jk_type"
                    + " where video.t_id = jk_type.t_id"
                    + " order by jk_type.t_name";
        else if (Level2 == "")
        {

            sql = "select distinct jk_class.c_name,jk_class.c_id"

               + " from jk_class,video,jk_type"

               + " where jk_class.c_id = video.c_id and video.t_id = jk_type.t_id and jk_type.t_id =" + Level1 + "";
        }
        else if (Level3 == "")
        {

            sql = "select distinct jk_sort.s_name,jk_sort.s_id"

 + " from jk_sort,video,jk_class"

+ "  where video.c_id = jk_class.c_id  and jk_sort.s_id = video.s_id and  jk_class.c_id = " + Level2 + "";
        }
        else if (Level4 == "")
        {

            sql = "select  (video.name   + case video.isfree when 1 then '(免费)' else ' ' end    ) as name,video.id from jk_sort,video,jk_type,jk_class where jk_class.c_id = video.c_id and video.t_id = jk_type.t_id and jk_sort.s_id = video.s_id"
                + " and jk_sort.s_id =" + Level3 + "";

        }
        else
        {
            sql = "select  video.*, video_url.url,video_url.id as urlid,video_url.urlname from  video,video_url where video.id = " + Level4 + " and video.id = video_url.nameid order by video_url.urlname";

        }

        DataSet ds = new DataSet();


        DataAccess da = new DataAccess();
        da.RunProc(sql,ds);


        DataTable dt = ds.Tables[0];

        if (Level4 != "")
        {

            foreach (DataRow dr in dt.Rows)
            {
                dr["url"] = EncryptDES(dr["url"].ToString(), KeyValue);

            }
        }


        return ds;

    }
    
}

 

 

 

后台存储过程LOGIN（X，Y，Z）

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[procLogin]
    -- Add the parameters for the stored procedure here
@UserName varchar(20),
@UserPass Varchar(40),
@IP Varchar(20)
 
AS
BEGIN
declare @sid varchar(20)
declare @count int
declare @ktype int
declare @kmoney int
declare @mylevel int
declare @mylevel1 int
declare @stim Datetime
declare @sotim Datetime
declare @userip varchar(20)    -- 数据库中当前登陆用户名保存的ip
declare @lastime smalldatetime -- 数据库中当前登陆用户名保存的最后刷新网页的时间，是计算用户是否在线的重要数据。
declare @userip1 varchar(20)    -- 记录当前用户登陆ip，用来区分是否为同一用户的标示
 
 
declare @Myuserid int
declare @otim smalldatetime
declare @guoqi varchar(10)

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

select @count = count(*) from user_data where username= @UserName and password= @UserPass and hidden=0

if (@count < 1) --没有该用户, 查找卡、
begin
  

    select  -1 as returnVal,  0 as UserID ,'无此用户' as prescript

end

else
begin
                set @userip1 = @IP

                Select @lastime=l_tim_login,@userip = l_ip FROM user_login WHERE l_username = @UserName
                 
                if(@lastime = null) --如果数据库没有此登陆用户纪录则执行下面的语句
                begin
                    delete from user_login where DateDiff(mi,l_tim_end,getdate()) > 20
                    select @count = count(*) from user_login where l_username=@UserName
                     
                    if(@count > 0)
                       update user_login set l_ip = @IP,l_tim_end = getdate() where  l_username=@UserName
                    else
                        insert into user_login(l_ip,l_username,l_tim_login,l_tim_end) values(@IP,@UserName,getdate(),getdate())
                   
                    set @UserIp = @IP
                end




                if ( @userip<>@userip1 and DateDiff(ss ,@Lastime,getdate()) < 1200     )
                begin
                  select  -1 as returnVal,  0 as UserID ,'系统已记录到您使用的用户在别处登录，请检查并修改您的密码！' as prescript

                end
                else
                begin
                        
                        update user_data  set passwd = rand(),last_tim = getdate() where username=@UserName and password=@UserPass
                        select @Mylevel = user_data.level , @Myuserid = user_data.id, @otim = otim        

                        from user_data  where username = @UserName and password = @UserPass

                       if(datediff(day,@otim,getdate()) > 0 and @Mylevel < 90)
                       begin
                          set @guoqi = '过期'
                          set @Mylevel1 = 0
                       end

                        select @Mylevel1  as returnVal, @myuserid as userid,

                        case @Mylevel 
                                     when 0 then '普通会员'+@guoqi 
                                     when 1 then '包月会员' +@guoqi 
                                     when 2 then '包季会员'  +@guoqi 
                                     when 3 then '包年会员'+@guoqi   
                                     when 4 then '终身会员'  +@guoqi 
                                     when 90 then '高级管理员'
                                     when 99 then '系统管理员'
                                     else '未知身份'
                         end as  prescript

                end
end  -- end of 有此用户
   
END

转载于:https://www.cnblogs.com/kwhei/archive/2009/04/06/1430138.html