从串联到参数的SQl

问题描述

我有这个带有连接字符串查询的sql连接，我想将其更改为参数.我自己开始编码，没有太多参考.尝试谷歌搜索，但是我还没有发现足够清楚的东西.

I have this sql connection with concatenated strings query and I would like to change it to parameters. I am starting coding on my own and I do not have many reference. Tried googling around but I have not found something clear enough to me.

public bool DBAuthenticate(string strUsername, string strPassword)
{
    string sqlstring;
    sqlstring = "SELECT * FROM credentials WHERE [Username]='" + strUsername + "' AND [Password]='" + strPassword + "'";
    string getconnstring = ConfigurationManager.ConnectionStrings["WTAConnectionString"].ConnectionString;
    SqlConnection conn = new SqlConnection(getconnstring);
    System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring,conn);
    System.Data.SqlClient.SqlDataReader reader;
    conn.Open();
    reader = comm.ExecuteReader();
    if (reader.Read())
        return true;
    else
       return false;
}

有人可以告诉我如何修改代码以将查询从串联更改为参数吗?非常感谢.

can someone please show me how to modify the code to change the query from concatenation to parameters? Thank you a lot.

推荐答案

要使用参数，您需要按以下方式更改语句:

In order to use parameters, you need to change your statement as follows:

SELECT * FROM credentials WHERE [Username]=@username AND [Password]=@password

然后它包含两个参数(@username和@password)，您需要在命令中为其提供值.您可以使用 AddWithValue 方法:

It then contains two parameters (@username and @password) that you need to provide the values for in the command. You can do this with the AddWithValue method:

// ...
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring,conn);
comm.Parameters.AddWithValue("@username", strUsername);
comm.Parameters.AddWithValue("@password", password);
System.Data.SqlClient.SqlDataReader reader;
// ...

请注意，您应始终可靠地布置连接，命令和读取器，因此添加一些 using 块将有所帮助:

Please also note that you should always dispose the connection, commands and readers reliably, so adding some using blocks will help:

using(SqlConnection conn = new SqlConnection(getconnstring))
{
    conn.Open();
    using(SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring,conn))
    {
        // ...
        using(SqlDataReader reader = comm.ExecuteReader())
        {
            // ...
        }
    }
}

这篇关于从串联到参数的SQl的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！