How to Retrieve and Re size VarBinary Image in Window form C Sharp using sqlClient in DataGrid

Question

In Want to retrieve the Record with MyImage in it in Window form Application . in My Student table i have record of FirstName LastName Gender GPA And MyImage . but i don't know how to retrieve the Record having Image also in varBinary Max my code is 


using System.IO;
using System.Drawing.Imaging;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace SchoolProject
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void PopulateGridView()
        {
            string sql = "SELECT * FROM Student";
            using (SqlCommand Command = new SqlCommand(sql, DataAccessLayer.ConnectionManager.GetConnection()))
            {
                SqlDataAdapter ds = new SqlDataAdapter();
                ds.SelectCommand = Command;
                DataSet Ds = new DataSet();     
                ds.Fill(Ds, "Student");
                 dataGridView1.DataSource = Ds;
                
                dataGridView1.DataMember = "Student";
            }
            label1.Text = "Data Retrieved";
        }
        private void Form1_Load(object sender, EventArgs e)
        {    
            PopulateGridView(); 
        }      
    }
}
And my DataAccess layer code is 


  public sealed class DAC
    {
        public static SqlDataReader GetCompanyInformation()
        {
            SqlDataReader reader;
            string sql = "SELECT * FROM Student ";
            using (SqlCommand command = new SqlCommand(sql, ConnectionManager.GetConnection()))
            {
                reader = command.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.CloseConnection);
            }
            return reader;
        }}

Answer 1

Hi：）

1）The codes in the PopulateGridView() should be all right，and it will automatically show images in the generated Image column，if all of the columns in the DataGridView are auto-generated。

2）For codes in GetCompanyInformation()，I think you can use something like this：

SqlDataReader sr = GetCompanyInformation();
sr.Read();
byte[]imgbytes = (byte[])sr["MyImage"];
using(MemoryStream ms = new MemoryStream(imgbytes))
{
    Image img = Image.FromStream(ms);
    PicutureBox.Image = img;    sr.Close();
}

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 2

Hi Muhammad,
How is it going with Wei’s suggestion? Is the issue solved?
Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 3

Thanks to replay but tell me how can i resize the MyImage the big problem is actually to resize the Image and for the second code i never use it for retieving the code but thanks to know me about this code also So please tell me who to retrive the code and also resize it and yes for converting binary to image code also please Mr  Wei_Dong

Answer 4

Hi again，

Set PictureBox.SizeMode = AutoSize Or StretchImage (If you don't want to change your picturebox's width and height)

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 5

Thanks to replay but i honorabl Wei_Dong i want to retrieve Image in Datagridview and i want to resize the Image in Datagridview not in PictureBox . Note Size of the Image is big but i wan to resize it so that i can see it as small size pic as in below datagrid I think now you can understand my question please replay me i am waiting for your replay Thanks again to helping me Wei_Dong 

Answer 6

Hi again，

It seems that you want to adjust the size of your image……Well，plz use BitMap Class and do this：

namespace WinFormCSharp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Image", typeof(byte[]));
            byte[]bytes = System.IO.File.ReadAllBytes("d:\\Penguins.jpg");
            dt.Rows.Add("Penguins",bytes) ;
 
            //Resize
            using (MemoryStream ms = new MemoryStream(bytes))
            {
                Image img = Image.FromStream(ms);
                Bitmap bt = new Bitmap(img, new Size(150,150));
               MemoryStream ms2 = new MemoryStream();
                bt.Save(ms2, ImageFormat.Jpeg);
                dt.Rows[0]["Image"] = ms2.ToArray();
                ms2.Dispose();
            }
            dataGridView1.AutoSizeColumnsMode =DataGridViewAutoSizeColumnsMode.AllCells;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
 
            dataGridView1.DataSource = dt;
        }
    }
}

---

   QQ我：
下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 7

Thanks to help me my problem have been solve but i once again have the problem for that is not displaying the record in DataGrid Please help me for this error also thanks to you in advance my DAC Code is 

public sealed class DAC
    {
        // Create Class variabl 
        private static SqlDataAdapter StudentDataAdapter = CreateSutdentDataAdapter();
       public static SqlDataReader GetCompanyInformation()
       {
            SqlDataReader reader;
            string sql = "SELECT * FROM Student ";
            using (SqlCommand command = new SqlCommand(sql, ConnectionManager.GetConnection()))
            {
                reader = command.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.CloseConnection);
            }
            return reader;
        }
        // Creat the Data Addapter 
       private static SqlDataAdapter CreateSutdentDataAdapter()
       {
        // Create the Select Statement and create Insert Update and Delete query 
           string gettSQL = "SELECT * FROM Student";
           string insertSQL = "INSERT INTO Sutdent(StudentID, FirstName,LastName,Gender,GPA,MyImage)" +
               "VALUES (@StudentID,@FirstName,@LastName,@Gender.@GPA,@MyImage)";
           string updateSQL = "UPDATE Student SET StudentName=@StudentName, LastName=@LastName,Gender=@Gender,GPA=@GPA, MyImage=@MyImage";
           string deleteSQL = "DELETE FROM Student WHERE StudentID=@StudentID";
           // For Lager Programing (DATABASE) using StorProceduer not the string type 
           // Now create the SQLDATAAdapeter
           SqlDataAdapter dataAdapter = new SqlDataAdapter();
           // now Create the DataAdapter to Poputlate our GridView or DisconnectedDataset using SqlCommand
           // In SelectCommand passing SQlCommand and in SQlCommand passing to varaible namely string sql query and the Connection of the Database 
           dataAdapter.SelectCommand = new SqlCommand(gettSQL, ConnectionManager.GetConnection());
           //now in same way create the InsertCommand also 
           dataAdapter.InsertCommand = new SqlCommand(insertSQL, ConnectionManager.GetConnection());
           // Create or Add the Parameter of th dataAdapter for InsertCommand 
           dataAdapter.InsertCommand.Parameters.Add("@StudentID", SqlDbType.Int).SourceColumn = "StudentID";
           dataAdapter.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar,25 ).SourceColumn = "FirstName";
           dataAdapter.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 25 ).SourceColumn = "LastName";
           dataAdapter.InsertCommand.Parameters.Add("@Gender", SqlDbType.VarChar ,1).SourceColumn = "Gender";
           dataAdapter.InsertCommand.Parameters.Add("@GPA", SqlDbType.Float ).SourceColumn = "GPA";
           dataAdapter.InsertCommand.Parameters.Add("@MyImage", SqlDbType.VarBinary).SourceColumn = "MyImage";
           //Create the UpdateCommand and the Parameters
           dataAdapter.UpdateCommand = new SqlCommand(updateSQL, ConnectionManager.GetConnection());
           dataAdapter.UpdateCommand.Parameters.Add("@StudentID", SqlDbType.Int).SourceColumn = "StudentID";
           dataAdapter.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.VarChar,25 ).SourceColumn = "FirstName";
           dataAdapter.UpdateCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 25 ).SourceColumn = "LastName";
           dataAdapter.UpdateCommand.Parameters.Add("@Gender", SqlDbType.VarChar ,1).SourceColumn = "Gender";
           dataAdapter.UpdateCommand.Parameters.Add("@GPA", SqlDbType.Float ).SourceColumn = "GPA";
           dataAdapter.UpdateCommand.Parameters.Add("@MyImage", SqlDbType.VarBinary).SourceColumn = "MyImage";
           dataAdapter.DeleteCommand = new SqlCommand(deleteSQL, ConnectionManager.GetConnection());
           dataAdapter.DeleteCommand.Parameters.Add("@StudentID", SqlDbType.Int).SourceColumn = "StudentID";
           // last return dataAdapter
           return dataAdapter;
       }
        // Creating the Table Schima 
       private static void DefinestudentTableSchema(DataTable table)
       {
           DataColumn StudentIDColumn = table.Columns.Add("StudentID", typeof(string));
           StudentIDColumn.AllowDBNull = false;
           table.PrimaryKey = new DataColumn[] { StudentIDColumn };
           DataColumn StudentFirstName = table.Columns.Add("FirstName", typeof(string));
           StudentFirstName.MaxLength = 150;
           DataColumn StudentLastName = table.Columns.Add("LastName", typeof(string));
           StudentLastName.MaxLength = 150;
           DataColumn StudentGender = table.Columns.Add("Gender", typeof(char));
           //StudentGender.MaxLength = 10;
          DataColumn StudentGPA = table.Columns.Add("GPA", typeof(float));
          DataColumn StudentImage = table.Columns.Add("MyImage", typeof(Byte[]));
           //Create the DataSet
       }
       private static DataSet CreateStudentTrackerDataSet()
       {
           DataSet StudentTrackerDataSet = new DataSet();
           DataTable StudentTable = StudentTrackerDataSet.Tables.Add("Student");
           DefinestudentTableSchema(StudentTable);
           return StudentTrackerDataSet;
       }
        //GetDataFunction that Populat the Gridview 
       public static DataSet GetData()
       {
           DataSet StudentTrakerDataSet = CreateStudentTrackerDataSet();
           StudentTrakerDataSet.EnforceConstraints = false;
           StudentDataAdapter.Fill(StudentTrakerDataSet.Tables["Student"]);
           StudentTrakerDataSet.EnforceConstraints = true;
           return StudentTrakerDataSet;
       }

And my .CS Code is 

 public partial class AditStudent : Form
    {
        // Creat the Class variabl Dataset to track the Student
        private DataSet StudentTrackerDataset;
        public AditStudent()
        {
            InitializeComponent();
            // In the constructor of the AditStudent form we are puting the DataEvenhandler
            dataGridView1.DataError += DataGridView1_DataError;
            StudentTrackerDataset = ProjectOfSchool.DataAccessLayer.DAC.GetData();

// Problem is at here which tell us that Child list for field Student cannot be created.

dataGridView1.DataMember = "Student";
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    if (dataGridView1.Columns[i] is DataGridViewImageColumn)
                    {
                        ((DataGridViewImageColumn)dataGridView1.Columns[i]).ImageLayout = DataGridViewImageCellLayout.Stretch;
                        break;}

            DataTable StudentTable = StudentTrackerDataset.Tables["School"];
            dataGridView1.DataSource = StudentTable;
        }
        private void DataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            // creating DataErrorHandler and methods of Datagridview1
            // string meassage by string farmaating to show how the error will look like for the Error in the Datagrid in from columan 0 and in row 1:2 multidimintional array type 
            string message = string.Format("Error in {0} columan in row {1}:{2}", e.ColumnIndex, e.RowIndex, e.Exception.Message);
            // now show the Error using messagebox 
            MessageBox.Show(message, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }

Error is that When i run the form then Child list for field Student cannot be created. is shown . And Yes in same way when i display the record using this code like 

then no error and record is shown as i required Please tell me about how to fix this problem my friend 

            string sql = "SELECT * FROM Student";
            using (SqlCommand Command = new SqlCommand(sql, ProjectOfSchool.DataAccessLayer.ConnectionManager.GetConnection()))
            {
                SqlDataAdapter ds = new SqlDataAdapter();
                ds.SelectCommand = Command;
                DataSet Ds = new DataSet();
                ds.Fill(Ds, "Student");
  dataGridView1.DataSource = Ds;
              dataGridView1.DataMember = "Student";
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    if (dataGridView1.Columns[i] is DataGridViewImageColumn)
                    {
                        ((DataGridViewImageColumn)dataGridView1.Columns[i]).ImageLayout = DataGridViewImageCellLayout.Stretch;
                        break;
                    }

Please i am waiting for your replay and sorry of delay of replay because i have some home problems 

Answer 8

Hi Muhammad,
Please create a new thread to ask a new question.
Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 9

Thanks to reply and i will as you saying to me thanks again