运用储存过程,减少sql注入,数据库写法。
CREATE PROCEDURE AddUser(----表名---)
@USERNAME varchar(20),
@PASSWORD varchar(20)
AS
IF EXISTS(SELECT * FROM tb_link WHERE link_name = @USERNAME)
begin
SELECT * FROM tb_link WHERE link_name = @USERNAME
end
网页端的判断
UserClass ucObj = new UserClass();
if (this.txtValid.Text.Trim() == this.labValid.Text.Trim())//验证码判断
{
//调用UserClass类的UserLogin方法判断用户是否为合法用户
DataTable dsTable = ucObj.UserLogin(this.txtName.Text.Trim(), this.txtPassword.Text.Trim());
if (dsTable!=null) //判断用户是否存在
{
Session["UserID"] = Convert.ToInt32(dsTable.Rows[0][0].ToString()); //保存用户ID
Session["Username"] = dsTable.Rows[0][1].ToString(); //保存用户登录名
//Response.Redirect(Request.CurrentExecutionFilePath); //跳转到当前请求的虚拟路径
Response.Redirect("Default.aspx"); //跳转到当前请求的虚拟路径
}
else
{
Response.Write(ccObj.MessageBoxPage("您的登录有误,请核对后再重新登录!"));
}
}
UserClass
public DataTable UserLogin(string strName,string strPwd)
{
SqlCommand myCmd = dbObj.GetCommandProc("proc_UserLogin");
//添加参数(用户名)
SqlParameter Name = new SqlParameter("@UserName",SqlDbType.VarChar,50);
Name.Value = strName;
myCmd.Parameters.Add(Name);
//添加参数(密码)
SqlParameter Pwd = new SqlParameter("@Password",SqlDbType.VarChar,50);
Pwd.Value = strPwd;
myCmd.Parameters.Add(Pwd);
dbObj.ExecNonQuery(myCmd);
DataTable dsTable = dbObj.GetDataSet(myCmd, "tbUser");
return dsTable;
}
DBClass
public SqlCommand GetCommandProc(string strProcName)
{
SqlConnection myConn = GetConnection();
SqlCommand myCmd = new SqlCommand();
myCmd.Connection = myConn;
myCmd.CommandText = strProcName;
myCmd.CommandType = CommandType.StoredProcedure;
return myCmd;
}
CommandType是SqlCommand对象的一个属性,用于指定执行动作的形式,它告诉.net接下来执行的是一个文本(text)、存储过程(StoredProcedure)还是表名称(TableDirect). 而CommandType是一个枚举类型。有三个值:text、StoredProcedure、TableDirect用于表示SqlCommand对象CommandType的执行形式。
public void ExecNonQuery(SqlCommand myCmd)
{
try
{
if (myCmd.Connection.State != ConnectionState.Open)
{
myCmd.Connection.Open(); //打开与数据库的连接
}
//使用SqlCommand对象的ExecuteNonQuery方法执行SQL语句,并返回受影响的行数
myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myCmd.Connection.State == ConnectionState.Open)
{
myCmd.Connection.Close(); //关闭与数据库的连接
}
}
}
public DataTable GetDataSet(SqlCommand myCmd, string TableName)
{
SqlDataAdapter adapt;
DataSet ds = new DataSet();
try
{
if (myCmd.Connection.State != ConnectionState.Open)
{
myCmd.Connection.Open();
}
adapt = new SqlDataAdapter(myCmd);
adapt.Fill(ds,TableName);
return ds.Tables[TableName];
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myCmd.Connection.State == ConnectionState.Open)
{
myCmd.Connection.Close();
}
}