c# Display data from database in datagridview

Question

I'm trying to insert image into sql server and then I want to render back in datagridview in c#.I got this codes from internet. I've following database schemes

create table product
 (
   product_id int not null primary key identity,
   product_serialno varchar(10) not null unique,
   product_name varchar(60) not null,
   brand_id int,
   category_id int,
   img varbinary,
   remarks text
  )

I've used following code for insertion

MemoryStream ms = new MemoryStream();
Picture.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] pic_arr = new byte[ms.Length];

SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["conSetting"]);
 String sql = "Insert into product(product_serialno,product_name,brand_id,category_id,img,remarks) values(@product_serialno,@product_name,@brand_id,@category_id,@img,@remarks)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@product_serialno",txtProductSerial.Text);
cmd.Parameters.AddWithValue("@product_name",txtProductName.Text);
cmd.Parameters.AddWithValue("@brand_id",txtbrand.Text);
cmd.Parameters.AddWithValue("@category_id",txtcategory.Text);
cmd.Parameters.AddWithValue("@img",pic_arr);
cmd.Parameters.AddWithValue("@remarks",txtRemarks.Text);

try
{
    con.Open();
    int i = cmd.ExecuteNonQuery();
}catch (Exception ex)
{
    MessageBox.Show("Enter all required field", "Error !!");
}finally
{
    con.Close();
    reset();
}

Now I've tried to display it in datagridview using following code

SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["conSetting"]);
String sql = "Select * From product";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader dataReader;

try
{
    con.Open();
    dataReader = cmd.ExecuteReader();
    try
    {
       while (dataReader.Read())
       {
           byte[] img= (byte[])dataReader.GetValue(5);
           MemoryStream ms = new MemoryStream(img);

           //dataGridView1.
           dataGridView1.Rows.Add(dataReader[0].ToString(), dataReader[1].ToString(), dataReader[2].ToString(), dataReader[3].ToString(), dataReader[4].ToString(), Image.FromStream(ms), dataReader[6].ToString());                    
         }
     }catch(Exception ex)
     {
         MessageBox.Show(ex.ToString(),"Exception");
     }
}catch (Exception ex){  
}finally{
         con.Close();
}

I'm getting error in rendering image. It will be nice if anyone help me to solve this problem.

Steve · Accepted Answer · 2017-08-26 10:39:47Z

1

You are missing some steps when you write your image to the database

MemoryStream ms = new MemoryStream();
Picture.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

// Reposition the Memory stream to the beginning of the data
ms.Seek (0, SeekOrigin.Begin);

// Allocate the array
byte[] pic_arr = new byte[ms.Length];

// Read the bytes into the array
ms.Read (pic_arr, 0, ms.Length);

Now your array contains the effective bytes to be written to the database.

I also suggest you to not use AddWithValue but use the more precise Add with the specific datatype for the parameters. While convenient AddWithValue is well known to be source of bugs and underperformances for your database engine

answered Aug 26, 2017 at 10:39

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8251888 Over a year ago

I'm not getting it. Can you format it with above code

Steve Over a year ago

The code above goes in the Insertion phase of your code. Before opening and running the ADO.NET code. If you don't fill the pic_arr with the bytes loaded in the MemoryStream you are not writing it in the database table. (And of course you cannot read it back)

Collectives™ on Stack Overflow

c# Display data from database in datagridview

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related