How to add SQL data into variable?-CodePudding

How can I add the SQL data into a variable in C#? Thanks for your help.

 string user = textBox1.Text;
    string password = textBox2.Text;
    con = new SqlConnection("...");//actually, there is a connection string but because of security, I'm not sharing it with you.
    com = new SqlCommand();
    con.Open();
    com.Connection = con;
    com.CommandText = "Select*From login where username='"   textBox1.Text  
        "'And sifre='"   textBox2.Text   "'";
    dr = com.ExecuteReader();
    if (dr.Read())
    {
      
    }

It is my login code it will open a new form but first I should find the username data of it and put it into a variable.

CodePudding user response：

Here is a pattern to follow, place your data operations in a separate class and call in your form. Here Login class could be whatever you want to call it with required properties.

public class Login
{
    public int Id { get; set; }
    . . .
}

Mocked data class, here since select * is used rather than specifying only required columns I assume the first column is the primary key which it may or may not be so adjust as needed and add other columns/properties to the Login class instance.

public class DataOperations
{
    // caller validates parameters have values
    public static Login DoSomething(string userName, string sifre)
    {
        Login login = new Login();

        var selectStatement = "SELECT * FROM login WHERE username= @userName AND sifre = @sifre";

        using (var cn = new SqlConnection("TODO"))
        {
            using (var cmd = new SqlCommand(selectStatement, cn))
            {
                cmd.Parameters.Add("@userName", SqlDbType.NVarChar).Value = userName;
                cmd.Parameters.Add("@sifre", SqlDbType.NVarChar).Value = sifre;

                cn.Open();

                var reader = cmd.ExecuteReader();

                if (!reader.HasRows) return login;
                reader.Read();
                login.Id = reader.GetInt32(0);
            }
        }

        return login;
    }
}

Call the method above in your form

Login login = DataOperations.DoSomething("TODO", "TODO");

CodePudding user response：

Karen's code, using Dapper (right click the project, choose Manage Nuget Packages, click Browse, type Dapper):

public static Login DoSomething(string userName, string sifre)
{
    using var c = new SqlConnection("conn str here");
    var id = c.ExecuteScalar<int?>(
        "SELECT id FROM login WHERE username= @u AND sifre = @s", 
        new{ u = textBox1.Text, s = textBox2.Text }
    );

    return new Login() { Id = id ?? 0 };
}