62,046
社区成员
发帖
与我相关
我的任务
分享
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
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 SqlDataReader ExecuteSQL(string sql)
{ //执行sql返回datareader,这个肯定有问题,因为在函数里不能关闭连接,到调用的时候,使用完了datareader之后才能关闭链接,很不方便,但我不知道怎么改
SqlDataReader rd;
SqlCommand cmd = new SqlCommand(sql,conn);
OpenConn();
if (sqlerror != null)
{
return null;
}
try
{
rd = cmd.ExecuteReader();
}
catch (SqlException e2)
{
rd = null;
sqlerror = e2;
}
return rd;
}
public int ExecuteSQLNoneQuery(string sql)
{//执行sqlNoneQuery
int i = 0;
SqlCommand cmd = new SqlCommand(sql, conn);
OpenConn();
if (sqlerror != null)
{
return i;
}
try
{
i = cmd.ExecuteNonQuery();
}
catch (SqlException e2)
{
sqlerror = e2;
}
finally
{
CloseConn();
}
return i;
}
public string ExecuteSQLScalar(string sql)
{//获取第一行第一列的值
string str="";
SqlCommand cmd = new SqlCommand(sql, conn);
OpenConn();
if (sqlerror != null)
{
return null;
}
try
{
str = cmd.ExecuteScalar().ToString();
}
catch (SqlException e2)
{
str = null;
sqlerror = e2;
}
return str;
}
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;
}
}
protected void Application_Start(Object sender, EventArgs e)
{
Application["tag"]=1;
}
protected void Application_Error(Object sender, EventArgs e)
{
try
{
if(Convert.ToInt32(Application["tag"])>2)
{
Application["tag"]=1;
return;
}
Application["tag"]=Convert.ToInt32(Application["tag"])+1;
Exception objErr = Server.GetLastError().GetBaseException();
Application["errorPage"] = Request.Url.ToString();
Application["errorMsg"] =objErr.Message;
Server.ClearError();
Response.Redirect("Error.aspx");
}
catch{}
}
Error.aspx.cs:private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
try
{
Application["tag"]=1;
lblError.Text=this.lblErrorPage.Text+Application["errorPage"].ToString()+"<br>"+this.lblErrorMsg.Text+Application["errorMsg"].ToString();
//写日志
WriterLog log = new WriterLog( Server.MapPath(@"./Log/Error.log"));
log.WriterError(Application["errorPage"].ToString(),Application["errorMsg"].ToString());
}
catch{}
}
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, SqlConnection connection)
{
DataSet ds = new DataSet();
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}