22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE UserLogin
(
CardId int IDENTITY(1,1), --Identity makes the column Not Null internally
UserName varchar(10) Not Null, --Name value is a must and can't be Null
LoginPwd varchar(10),
LoginType int
)
Insert into UserLogin (UserName,LoginPwd,LoginType )
Values('Jack','111111',1),
('User','222222',0)
use SQL2012Db
go
create proc proc_UserLogin
@p_User varchar(10),
@p_Password varchar(10),
@p_Type int
as
insert into UserLogin(UserName,LoginPwd,LoginType)values(@p_User,@p_Password,@p_Type)
go
private void Btn_Login_Click(object sender, EventArgs e)
{
string strIP = TBox_IP.Text;
string strDb = TBox_DB.Text;
string strUser = TBox_User.Text;
string strPwd = TBox_Pwd.Text;
int iType = 1;
string errmsg = "";
if (!m_DbAction.Login(strIP, strDb, ref strUser, strPwd, iType, out errmsg))
{
MessageBox.Show(errmsg);
return;
}
}
public bool Login(string str_ip, string str_db, ref string str_user, string str_pwd, int i_type, out string errmsg)
{
SQLServerAPI api = new SQLServerAPI(str_ip, str_db, m_User, m_Pwd);
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("p_User", str_user);
param[1] = new SqlParameter("p_Password", str_pwd);
param[2] = new SqlParameter("p_Type", i_type);
DataTable result;
int err = api.ExecQueryStoreProc("proc_UserLogin", ref param, out result);
if (err != 0)
{
switch (err)
{
case -1:
errmsg = MyRe.MSS_NOUSER;
break;
case -2:
errmsg = MyRe.MSS_WRONGPASSWORD;
break;
default:
errmsg = MyRe.MSS_UNKNOWNERROR;
break;
}
return false;
}
DataRow dr = result.Rows[0];
str_user = dr["CardID"].ToString();
errmsg = MyRe.MSS_SUCCESS;
return true;
}
public int ExecQueryStoreProc(string procname, ref SqlParameter[] param, out DataTable result)
{
if (!Connect())
{
result = null;
return -1;
}
try
{
SqlCommand command = new SqlCommand(procname, m_SqlConnection);
command.CommandType = CommandType.StoredProcedure;
if (m_Transaction != null)
command.Transaction = m_Transaction;
SqlParameter rvalue = command.Parameters.Add(new SqlParameter("RETURN_VALUE", SqlDbType.Int));
rvalue.Direction = ParameterDirection.ReturnValue;
if (param != null)
command.Parameters.AddRange(param);
result = new DataTable();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
result.Load(reader);
return Convert.ToInt32(command.Parameters["RETURN_VALUE"].Value);
}
catch (Exception)
{
result = null;
return -1;
}
finally
{
DisConnect();
}
}
USE [SQL2012Db]
GO
/****** Object: StoredProcedure [dbo].[proc_UserLogin] Script Date: 02/15/2019 09:37:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[proc_UserLogin]
@p_User varchar(100),
@p_Password varchar(200),
@p_Type int
as
declare @CardID varchar(20), @Name varchar(50),
@Password varchar(20)
select @CardID = CardID, @Name = UserName,
@Password = LoginPwd
from UserLogin
where ((@p_Type = 0 and CardID = @p_User) or (@p_Type = 1 and UserName = @p_User))
if @@rowcount <= 0
begin
return -1;
end
if @Password != @p_Password
begin
return -2
end
select @CardID as CardID, @Name as Name, @Password as Password
return 0
select * from UserLogin where UserName=@userName and LoginPwd=@loginPwd