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
