如何从两个表中获取数据进行登录

Question

我有两个表在我的SQL Server数据库。

表1:Login_User

列

Unam, Pswd

表#2 - Add_User 列

fName, lName, mobile, Eid, Unam, Pswd, Yob

两个表都有数据

我的查询是每当用户想要登录时，登录凭据将从表1或表2中获取。我编写了一个内部连接的SQL查询，如下所示:

string s = " select * from Login_User inner join Add_User on Login_User.Unam = Add_User.Unam where Login_User.Unam='" + txtUser.Text + "' and Login_User.Pswd='" + txtPswd.Text + "'";
SqlConnection con = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Product.mdf;Integrated Security=True;User Instance=True");

SqlDataAdapter da = new SqlDataAdapter(s, con);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
this.Hide();
Home hm = new Home();
hm.ShowDialog();
}
else
{
MessageBox.Show("Invalid User Name or Password!.");
}

我得到一个错误

位置0没有行

请帮帮我

Answer 1

严格避免通过连接输入数据和sql代码来创建普通的sql查询。这对SQL注入来说是非常危险的!

您可以使用一些ADO。. NET特性，使您的代码可读性更好，内存处理更清晰，数据库工作更安全。

在ADO中执行sql查询。. NET可以像这样:

void login (string txtUser, string txtPassword) {
// Always hash login-informations (at least the password):
// therefore you hash the input-data and compare it 
// to an also hashed value inside a database
// with this you never transfer 1:1 login-informations from your service to the db
...

// use "using" - this clears the memory after the object runs out of scope
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();

using (DbCommand dbCmd = connection.CreateCommand())
{
// don't use "*", only select what you need
string query = @"SELECT t.Unam, t.Pswd FROM 
(
SELECT Unam, Pswd FROM User_Login
UNION 
SELECT Unam, Pswd FROM Add_User
) t
WHERE t.Unam = @name AND t.Pswd = @pwd";
dbCmd.CommandText = query;
// a StringBuilder is possible as well, but always use parameters!
// hashed txtName and txtPassword!
dbCmd.Parameters.Add(new SqlParameter("@name", txtName));
dbCmd.Parameters.Add(new SqlParameter("@pwd", txtPassword));

using (DbDataAdapter adapter = DbProviderFactories.GetFactory(connection).CreateDataAdapter())
{
adapter.SelectCommand = dbCmd;
DataTable dt = new DataTable();
adapter.Fill(dt);
// here you can do your stuff
}
}
}
catch (Exception)
{
throw;
}
finally
{
connection.Close(); 
// close the connection after using to minimize database traffic
}
}
}

您可以阅读更多关于如何正确使用ADO的数据库功能的信息。

始终记住:特别是在处理登录信息和用户数据时，确保干净可靠的代码以提供安全是很重要的。