小弟第一次用存储过程来写程序 是一个登录的页面 但是我运行后却报错 先把代码给贴出来 供大虾们看下存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[up_SelLog]
as
declare @UserId varchar(50),
@Password varchar(50)
select * from UserInfo where UserId=@UserId and Password=@Password
----------------------------------------------------------------------
数据访问层:
public List<UserInfoInfo> SelLog(string uname, string upwd)
{
string proc = "up_SelLog";
SqlParameter[] parm = new SqlParameter[]{
new SqlParameter("@UserId",uname),
new SqlParameter("@Password",upwd)
};
return getList(getDS("1", proc, parm));
} public List<UserInfoInfo> getList(System.Data.DataSet ds)
{
List<UserInfoInfo> list = new List<UserInfoInfo>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserInfoInfo uii = new UserInfoInfo();
uii.UserId = dr["UserId"].ToString();
uii.Password = dr["Password"].ToString();
list.Add(uii);
}
return list;
}
-----------------------------------------------------------
业务逻辑层: public Boolean SelLog(string uname, string upwd)
{
bool bl = false;
List<UserInfoInfo> list = iUserInfo.SelLog(uname, upwd);
if (list !=null && list.Count > 0)
{
bl = true;
}
else
{
bl = false;
}
return bl;
}
------------------------------------------------------------
表示层:
protected void Button1_Click(object sender, EventArgs e)
{
UserInfoManager uim = new UserInfoManager();
UserInfoInfo uii = new UserInfoInfo();
bool bl = uim.SelLog(TextBox1.Text.Trim(), TextBox2.Text.Trim());
if (bl)
{
Response.Redirect("WebForm1.aspx");
}
else
{
this.Page.ClientScript.RegisterStartupScript(GetType(),"","<script>alert('用户名或密码错误!');</script>");
}
}
-------------------------------------------------------------
但是当我运行程序后 点击登录按钮就报错,错误是说:‘SelLog’附近有语法错误 请大虾们指教
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[up_SelLog]
as
declare @UserId varchar(50),
@Password varchar(50)
select * from UserInfo where UserId=@UserId and Password=@Password
----------------------------------------------------------------------
数据访问层:
public List<UserInfoInfo> SelLog(string uname, string upwd)
{
string proc = "up_SelLog";
SqlParameter[] parm = new SqlParameter[]{
new SqlParameter("@UserId",uname),
new SqlParameter("@Password",upwd)
};
return getList(getDS("1", proc, parm));
} public List<UserInfoInfo> getList(System.Data.DataSet ds)
{
List<UserInfoInfo> list = new List<UserInfoInfo>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
UserInfoInfo uii = new UserInfoInfo();
uii.UserId = dr["UserId"].ToString();
uii.Password = dr["Password"].ToString();
list.Add(uii);
}
return list;
}
-----------------------------------------------------------
业务逻辑层: public Boolean SelLog(string uname, string upwd)
{
bool bl = false;
List<UserInfoInfo> list = iUserInfo.SelLog(uname, upwd);
if (list !=null && list.Count > 0)
{
bl = true;
}
else
{
bl = false;
}
return bl;
}
------------------------------------------------------------
表示层:
protected void Button1_Click(object sender, EventArgs e)
{
UserInfoManager uim = new UserInfoManager();
UserInfoInfo uii = new UserInfoInfo();
bool bl = uim.SelLog(TextBox1.Text.Trim(), TextBox2.Text.Trim());
if (bl)
{
Response.Redirect("WebForm1.aspx");
}
else
{
this.Page.ClientScript.RegisterStartupScript(GetType(),"","<script>alert('用户名或密码错误!');</script>");
}
}
-------------------------------------------------------------
但是当我运行程序后 点击登录按钮就报错,错误是说:‘SelLog’附近有语法错误 请大虾们指教
ALTER proc [dbo].[up_SelLog] (@UserId varchar(50),@Password varchar(50))
as
select * from UserInfo where UserId=@UserId and Password=@Password
as
declare @UserId varchar(50),
@Password varchar(50)
我用了你这种方法还是报我这个错误ALTER proc [dbo].[up_SelLog] (@UserId varchar(50),@Password varchar(50))
我的dbheper:
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;namespace CN.ACCP.MyOfficeDAL
{
public class DBHelper
{
#region 读取数据库连接字符串
string constr = string.Empty; public string Constr
{
get { return ConfigurationManager.ConnectionStrings["con"].ConnectionString; } }
#endregion SqlConnection con = new SqlConnection(); #region "打开一个可用的连接"
/// <summary>
/// 打开一个可用的连接
/// </summary>
/// <returns></returns>
public SqlConnection getcon()
{
try
{
con.ConnectionString = this.Constr;
con.Open();
}
catch (Exception ex)
{
throw ex;
}
return con;
}
#endregion #region "关闭连接"
/// <summary>
/// 关闭连接
/// </summary>
public void CloseDb()
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
#endregion #region "执行增加、删除、修改"
/// <summary>
/// 执行增加、删除、修改
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parm">SQL语句参数</param>
/// <returns></returns>
public int ExcuteCommand(string Type, string sql, params SqlParameter[] parm)
{
SqlCommand cmd = getcon().CreateCommand();
if (Type == "0")
{
cmd.CommandType = CommandType.Text;
}
else if (Type == "1")
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.CommandText = sql;
//if (parm != null)
//{
// cmd.Parameters.AddRange(parm);
//}
int result = -1;
try
{
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ throw ex;
}
finally
{
cmd.Dispose();
CloseDb();
}
return result;
}
#endregion #region "执行查询"
/// <summary>
/// 执行查询
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parm">SQL语句参数</param>
/// <returns></returns>
public DataSet getDS(string Type, string sql, params SqlParameter[] parm)
{
SqlDataAdapter sda = new SqlDataAdapter(sql, this.Constr);
DataSet ds = new DataSet();
SqlCommand cmd = getcon().CreateCommand();
if (parm != null)
{
sda.SelectCommand.Parameters.AddRange(parm);
}
if (Type == "0")
{
cmd.CommandType = CommandType.Text;
}
else if (Type == "1")
{
cmd.CommandType = CommandType.StoredProcedure;
}
try
{
sda.Fill(ds);
}
catch (Exception ex)
{ throw ex;
}
finally
{
sda.Dispose();
}
return ds;
}
#endregion
}
}
试想我这么登录
用户名:'' or 1=1
密码也同上~
这样会发生什么事那?
在试想,我做成一个子查询语句,又会怎么样那?