CODEWITHSUNDEEP
c#datagridview
user8251888
user8251888

Reputation:

c# Display data from database in datagridview

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.

Upvotes: 0

Views: 791

Answers (1)

Steve
Steve

Reputation: 216243

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

Upvotes: 1

Related Questions