这个是我也的三层下的登录检测,用到一个检查用户名密码是否匹配的存储过程,和几个对数据库操作的类。
1.检查用户名密码是否匹配的存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: sixi
-- ALTER date: 2008,6,12
-- Description: Check Login.如果登陆成功,返回0;用户名错误,返回1;密码错误返回2
ALTER PROCEDURE [dbo].[CheckLog]
-- Add the parameters for the stored procedure here
@userID nvarchar(50),
@PWD nvarchar(50),
@rst int output
AS
if not exists
(
select * from userinfo where userID=@userID
)
BEGIN
select @rst=1
return
END
if not exists
(
select * from userinfo where userID=@userID and PWD=@PWD
)
BEGIN
SET NOCOUNT ON;
select @rst=2
return
END
BEGIN
select @rst=0
END
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: sixi
-- ALTER date: 2008,6,12
-- Description: Check Login.如果登陆成功,返回0;用户名错误,返回1;密码错误返回2
ALTER PROCEDURE [dbo].[CheckLog]
-- Add the parameters for the stored procedure here
@userID nvarchar(50),
@PWD nvarchar(50),
@rst int output
AS
if not exists
(
select * from userinfo where userID=@userID
)
BEGIN
select @rst=1
return
END
if not exists
(
select * from userinfo where userID=@userID and PWD=@PWD
)
BEGIN
SET NOCOUNT ON;
select @rst=2
return
END
BEGIN
select @rst=0
END
2.数据库操作类。
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// This class include connection to the database and Execute SQl sentence.
/// </summary>
public class S_DataExecute
{
private string dbconn;
private SqlConnection conn ;
private SqlException sqlerror;
public S_DataExecute()
{//默认构造函数
dbconn = ConfigurationManager.ConnectionStrings["iqmsSup_BSConnectionString"].ConnectionString;
conn = new SqlConnection(dbconn);
sqlerror = null;
}
public SqlConnection getConn()
{//获取连接
return conn;
}
public void OpenConn()
{//打开连接
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
}
catch (SqlException e2)
{
sqlerror = e2;
}
}
public void CloseConn()
{//关闭连接
try
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
catch (SqlException e2)
{
sqlerror = e2;
}
}
public SqlException GetSqlError()
{//获取错误
return sqlerror;
}
public SqlParameter MakeSqlParameter(string _ParameterName, int _length, SqlDbType _type,object _value,ParameterDirection direction)
{//构造sql语句参数
SqlParameter sp=new SqlParameter(_ParameterName, _type, _length);
if((direction!=ParameterDirection.Output)&&_value!=null)
sp.Value=_value;
sp.Direction = direction;
return sp;
}
public int CheckLogIn(string UserName, String PWD)
{
//如果登陆成功,返回0;用户名错误,返回1;密码错误返回2
int rst = -1;//默认为-1如果客户端检测到是-1则会查看sqlerror.message
SqlCommand cmd = new SqlCommand("checklog", this.getConn());
cmd.Parameters.Add(this.MakeSqlParameter("UserID",50,SqlDbType.NVarChar,UserName,ParameterDirection.Input));
cmd.Parameters.Add(this.MakeSqlParameter("PWD", 50, SqlDbType.NVarChar, PWD, ParameterDirection.Input));
SqlParameter sp = this.MakeSqlParameter("rst", 50, SqlDbType.Int, null, ParameterDirection.Output);
cmd.Parameters.Add(sp);
cmd.CommandType = CommandType.StoredProcedure;
try
{
this.OpenConn();
cmd.ExecuteNonQuery();
rst = int.Parse(sp.Value.ToString());
}
catch (SqlException e2)
{
this.sqlerror = e2;
}
finally
{
CloseConn();
}
return rst;
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// This class include connection to the database and Execute SQl sentence.
/// </summary>
public class S_DataExecute
{
private string dbconn;
private SqlConnection conn ;
private SqlException sqlerror;
public S_DataExecute()
{//默认构造函数
dbconn = ConfigurationManager.ConnectionStrings["iqmsSup_BSConnectionString"].ConnectionString;
conn = new SqlConnection(dbconn);
sqlerror = null;
}
public SqlConnection getConn()
{//获取连接
return conn;
}
public void OpenConn()
{//打开连接
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
}
catch (SqlException e2)
{
sqlerror = e2;
}
}
public void CloseConn()
{//关闭连接
try
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
catch (SqlException e2)
{
sqlerror = e2;
}
}
public SqlException GetSqlError()
{//获取错误
return sqlerror;
}
public SqlParameter MakeSqlParameter(string _ParameterName, int _length, SqlDbType _type,object _value,ParameterDirection direction)
{//构造sql语句参数
SqlParameter sp=new SqlParameter(_ParameterName, _type, _length);
if((direction!=ParameterDirection.Output)&&_value!=null)
sp.Value=_value;
sp.Direction = direction;
return sp;
}
public int CheckLogIn(string UserName, String PWD)
{
//如果登陆成功,返回0;用户名错误,返回1;密码错误返回2
int rst = -1;//默认为-1如果客户端检测到是-1则会查看sqlerror.message
SqlCommand cmd = new SqlCommand("checklog", this.getConn());
cmd.Parameters.Add(this.MakeSqlParameter("UserID",50,SqlDbType.NVarChar,UserName,ParameterDirection.Input));
cmd.Parameters.Add(this.MakeSqlParameter("PWD", 50, SqlDbType.NVarChar, PWD, ParameterDirection.Input));
SqlParameter sp = this.MakeSqlParameter("rst", 50, SqlDbType.Int, null, ParameterDirection.Output);
cmd.Parameters.Add(sp);
cmd.CommandType = CommandType.StoredProcedure;
try
{
this.OpenConn();
cmd.ExecuteNonQuery();
rst = int.Parse(sp.Value.ToString());
}
catch (SqlException e2)
{
this.sqlerror = e2;
}
finally
{
CloseConn();
}
return rst;
}
}