问题,想做一个简单的用户验证, 两个TextBOX的值付给两个string 变量.
在数据库里进行查询,返回一个值来判断两个值与数据库中的字段值相同.请问一下用一个存储过程,如何实现例如数据库表为:
id user password
1 admin admin高手给个简单实例.存储过程怎么写? C#里如何调用? 谢谢了~!
在数据库里进行查询,返回一个值来判断两个值与数据库中的字段值相同.请问一下用一个存储过程,如何实现例如数据库表为:
id user password
1 admin admin高手给个简单实例.存储过程怎么写? C#里如何调用? 谢谢了~!
--id user password
--1 admin admin
create procedure proc_test
as
declare @exist int output,
declare @userName varchar(10) input,
declare @password varchar(10) input
begin
select @exist=count(1) from A where user=@userName and password=@password
end
SqlConnection conn = new SqlConnection("");//略
SqlCommand cmd = new SqlCommand();
cmd.Type = CommandType.Procedure;//IDE中看一下
cmd.Connection = conn;
cmd.Text="proc_test";
//记得不是很清,查一下用法
SqlParameters[] param = {
new SqlParameter("@userName",TxtUserName.Text,SqlDbType.String);
new SqlParameter("@password",TxtPassword.Text,SqlDbType.String);
}
foreach(SqlParameter sp in param)
{
cmd.SqlParameters.Add(sp);
}
conn.Open();
int rows = cmd.ExecuteNonquery();
if(rows > 0)
{
//用户存在;
}
if exists (select 1 from sys.objects where name = 'ValidateLogin' and type = 'P')
drop procedure [dbo].[ValidateLogin]
GO
CREATE PROCEDURE [dbo].[ValidateLogin]
@user varchar(16),
@password varchar(16)
AS
SELECT id
FROM Users
WHERE [user] = @user AND [password] = @password
GO
protected void btnLogin_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
lblMessage.Text = string.Empty; SqlConnection cn = new SqlConnection(@"server=.\sqlExpress;uid=sa;pwd=;database=Wish");
SqlCommand cmd = new SqlCommand("ValidateLogin", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@user", SqlDbType.VarChar).Value = txtUserName.Text.Trim();
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = txtPassword.Text.Trim(); int userId = 0;
try
{
cn.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
userId = Convert.ToInt32(o);
}
}
catch (Exception)
{
lblMessage.Text = "服务器错误,请稍候再试!";
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
} if (lblMessage.Text != string.Empty)
return; if (userId <= 0)
{
lblMessage.Text = "用户名或密码不正确";
}
else
{
Response.Redirect("default.aspx");
}
}
}