C# : how to get value from specific column from SQL Server and store into variable

Question

I want to ask how to store the full name (from database @3) of the user into the variable fname.

SqlDataReader rdr = cm.ExecuteReader();
string sql = "Select * from Login where Username like'" + txtUser.Text + "'and password like'" + txtPass.Text + "'";

cm = new SqlCommand(sql, cn);
dr = cm.ExecuteReader();

dr.Read();

if (dr.HasRows)
{
    cn.Close();
    AutoClosingMessageBox.Show("Access Granted. Welcome " + txtUser.Text + "!", "Successfully Login.", 400);

    cn.Open();
    UserID = cm.ExecuteScalar().ToString();

    //fname = ;
    Form1 frmMain = new Form1();
    frmMain.pass(UserID);
    frmMain.Show();
    this.Hide();
    //...
}

My database : @0 = ID, @1 = Username, @2 = Password, @3 = full name, @4 = binary image. I can't post photo :( >

If anyone also knows how to convert the binary into a picture, that would help me a lot thanks.

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Sep 06 '15 at 07:10

score 1 · Accepted Answer · answered Sep 06 '15 at 05:01

1

   while (dr.Read())
    {
           string userid=dr["sql_column_name"].ToString();
           //rest of the code...
    }

answered Sep 06 '15 at 05:01

Nir-Z

819
1
13
31

score 0 · Answer 2 · answered Sep 06 '15 at 05:04

0

Select ID from Login where Username like

use ID instead of *.

you can read binary as array of byte :byte[], if you saves image file in your database use system.io.streamwriter but if you saved the image you can use bitmap class

answered Sep 06 '15 at 05:04

Ali pishkari

532
4
18

score 0 · Answer 3 · edited May 23 '17 at 11:58

At first, it's better to use = operator for checking username and password in queries, instead of like.

To have the fullname field value, you can simply use the dr property as follows:

var fullname = dr["fullname"];

or any other field value:

var userID = dr["ID"];

do these things before cn.close(). I've no idea why you close the connection and open it again after a MessageBox! another issue with your code is you have executed the command already, there is no need to run in again with ExecuteScalar().

Your code could be so:

string sql = string.format("SELECT * FROM Login WHERE Username='{0}' AND Password='{1}'", txtUser.Text, txtPass.Text);
cm = new SqlCommand(sql, cn);
var dr = cm.ExecuteReader();
if (dr.Read()) // Read() returns TRUE if there are records to read, or FALSE if there is nothing
{
    fname = dr["fullname"];
    UserID = dr["ID"].ToString();

    AutoClosingMessageBox.Show("Access Granted. Welcome " + txtUser.Text + "!", "Successfully Login.", 400);

    Form1 frmMain = new Form1();
    frmMain.pass(UserID);
    frmMain.Show();
    this.Hide();
    //...
}

for your second question, you can convert the binary field to image and vice versa using these methods as @JensB says:

public byte[] imageToByteArray(System.Drawing.Image imageIn)
{
    MemoryStream ms = new MemoryStream();
    imageIn.Save(ms,System.Drawing.Imaging.ImageFormat.Gif);
    return  ms.ToArray();
}

public Image byteArrayToImage(byte[] byteArrayIn)
{
     MemoryStream ms = new MemoryStream(byteArrayIn);
     Image returnImage = Image.FromStream(ms);
     return returnImage;
}

Usage:

var theImage = byteArrayToImage((byte[])reader.Items["imageBinary"]);

C# : how to get value from specific column from SQL Server and store into variable

3 Answers3