How to accept ID numbers from SQL Server table using Visual C #

Question

I am teaching the concepts of database programming and C # as newbies. I just created a table on SQL Server and inserted some image files into it.

I also created a form project to save images in this DB table and get them to show them in a frame.

I am trying to get the ID numbers and load them into the combo box, so I can select the ID number of the linked image. I was able to save image files in DB, but I could not get identification numbers in combobox.

I ask for an easy explanation and some suggestions from you. You can check my code below.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(DBHandler.GetConnectionString());

        try
        {
            OpenFileDialog fop = new OpenFileDialog();
            fop.InitialDirectory = @"C:\";
            fop.Filter = "[JPG,JPEG]|*.jpg";

            if (fop.ShowDialog() == DialogResult.OK)
            {
                FileStream FS = new FileStream(@fop.FileName, FileMode.Open, FileAccess.Read);

                byte[] img = new byte[FS.Length];
                FS.Read(img, 0, Convert.ToInt32(FS.Length));

                if (con.State == ConnectionState.Closed)
                    con.Open();

                SqlCommand cmd = new SqlCommand("SaveImage", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@img", SqlDbType.Image).Value = img;

                cmd.ExecuteNonQuery();

                //loadImageIDs();
                MessageBox.Show("Image Save Successfully!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Please Select a Image to save!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
    }

    private void button3_Click(object sender, EventArgs e)
    {
        SqlConnection con2 = new SqlConnection(DBHandler.GetConnectionString());

        SqlCommand cmd2 = new SqlCommand("ReadImage",con2);
        cmd2.CommandType = CommandType.StoredProcedure;

        cmd2.Parameters.Add("@imgId", SqlDbType.Int).Value =
                  Convert.ToInt32(comboBox1.SelectedValue.ToString()); // I am not sure if this line is correct to get images from the table using "ReadImage" procedure.

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd2;

        DataTable dt = new DataTable();
        da.Fill(dt);

        dataGridView1.DataSource = dt;
    }

    private void button2_Click(object sender, EventArgs e) // I have a problem within this code block, I think :)
    {
        SqlConnection con3 = new SqlConnection(DBHandler.GetConnectionString());

        SqlCommand cmd3 = new SqlCommand("ReadAllImageIDs", con3);
        cmd3.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter dc = new SqlDataAdapter();
        dc.SelectCommand = cmd3;

        DataTable dtt = new DataTable();
        dc.Fill(dtt);

        comboBox1.DataSource = dtt;

        //dataGridView1.DataSource = dtt;
   }

Answer 1

, DisplayMember ValueMember ComboBox.

 comboBox1.DisplayMember = "ID";

, IDisposable (SqlConnection, SqlCommand, SqlDataAdapter) using . , :

private void button2_Click(object sender, EventArgs e)
{
    var dtt = new DataTable();
    using (var con3 = new SqlConnection(DBHandler.GetConnectionString()))
    using (var cmd3 = new SqlCommand("ReadAllImageIDs", con3))
    {
        cmd3.CommandType = CommandType.StoredProcedure;

        using(var dc = new SqlDataAdapter())
        {
            dc.SelectCommand = cmd3;
            dc.Fill(dtt);
            comboBox1.DataSource = dtt;
            comboBox1.DisplayMember = "ID";
            comboBox1.ValueMember = "ID";
        }
    }
}

SqlDataAdapter constructor, select, - , :

private void button2_Click(object sender, EventArgs e)
{
    var dtt = new DataTable();

    using (var dc = new SqlDataAdapter("ReadAllImageIDs", DBHandler.GetConnectionString()))
    {
        dc.SelectCommand.CommandType = CommandType.StoredProcedure;
        dc.Fill(dtt);
    }
    comboBox1.DataSource = dtt;
    comboBox1.DisplayMember = "ID";
    comboBox1.ValueMember = "ID";
}

, , (DAL) . , , , :

public class ImageService
{
    public static void SaveImage(string fileName)
    {
        byte[] img;
        using(var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
        {
            img = new byte[fileStream.Length];
            fileStream.Read(img, 0, Convert.ToInt32(fileStream.Length));
        }
        using (var con = new SqlConnection(DBHandler.GetConnectionString()))
        using (var cmd = new SqlCommand("SaveImage", con))
        {
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@img", SqlDbType.Image).Value = img;
            cmd.ExecuteNonQuery();
        }
    }
    public static DataTable GetAllImageIDs
    {
        var dtt = new DataTable();

        using (var dc = new SqlDataAdapter("ReadAllImageIDs", DBHandler.GetConnectionString()))
        {
            dc.SelectCommand.CommandType = CommandType.StoredProcedure;
            dc.Fill(dtt);
        }
        return dtt;
    }
}

:

private void button1_Click(object sender, EventArgs e)
{
     try
    {
        OpenFileDialog fop = new OpenFileDialog();
        fop.InitialDirectory = "C:\\";
        fop.Filter = "[JPG,JPEG]|*.jpg";

        if (fop.ShowDialog() == DialogResult.OK)
        {
            ImageService.SaveImage(fop.FileName);
        }

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}
private void button2_Click(object sender, EventArgs e)
{

    comboBox1.DataSource = ImageService.GetAllImageIDs();
    comboBox1.DisplayMember = "ID";
    comboBox1.ValueMember = "ID";
}

Answer 2

, combobox SQL.

:

        List<string> ids = new List<string>();
        using (SqlConnection conn = new SqlConnection("MyConnectionString"))
        {
            using (SqlCommand cmd = new SqlCommand("MyStoredProcedure", conn))
            {
                conn.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        ids.Add(rdr["NameOfIsField"].ToString());
                    }
                }
            }
        }

        MyListBox.DataSource = ids;

: