CODEWITHSUNDEEP
c#sqldatabase
User555
User555

Reputation: 83

INSERT an image to SQL server with some other data in C#

I've seen some posts here about how to INSERT just an image into an SQL database, but I just can't find any information about how to INSERT an image plus some other data. For example, if you want to send INSERT INTO SQL statement to server with values from some textboxes plus an image from an imagebox. Here is my code:

string sql;
        System.IO.MemoryStream picStream = new System.IO.MemoryStream();

        emp_picture.Image.Save(picStream, System.Drawing.Imaging.ImageFormat.Jpeg);

        byte[] bytePic = picStream.ToArray();


        sql = "INSERT INTO employees (employee_number, first_name, last_name, address, city, " +
      "zip_code, contact_info, truck_number, trailer_number, picture) " +
      "VALUES ('" + Driver_Number.Text + "', '" + First_Name.Text + "', '" + Last_Name.Text +
          "', '" + Address.Text + "', '" + City.Text + "', '" + Zip_Code.Text + "', '" + Contact_info.Text + "', '" +
          Truck_Number.Text + "', '" + Trailer_Number.Text +"', '"+bytePic+"')";

When I try to retrieve the image it says, "Parameter is not valid." I know used a plain string without any parameters that is why it throws that exception. I just don't know if I can use parameters to send a message to server or not. I'd appreciate any ideas about how to solve this problem. Thanks.

Upvotes: 1

Views: 5414

Answers (2)

Mariachi
Mariachi

Reputation: 139

The response is in this article: http://www.codeproject.com/Articles/25956/Sending-Receiving-PictureBox-Image-in-C-To-From-Mi

For example, table of database must have the next fields: Name varchar(10) Image binary(8000)

        string StrConection = "Data Source=XXXX;Initial Catalog=ExampleDB;Integrated Security=True";
        using (SqlConnection Conection = new SqlConnection(StrConection))
        {
            Conection.Open();
            using(SqlCommand Command = new SqlCommand("INSERT INTO Images VALUES(@Name,@Image);",Conection))
            {
                //Get bytes from PictureBox
                Image img = picBox.Image;
                MemoryStream ms = new MemoryStream();
                img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                byte[] bytes = ms.ToArray();

                Command.CommandType = CommandType.Text;
                Command.Parameters.Add("@Name",SqlDbType.VarChar,10).Value = txtName.Text;
                Command.Parameters.Add("@Image", SqlDbType.Binary, 8000).Value = bytes;
                Command.ExecuteNonQuery();
            }
            Conection.Close();
        }

EDIT: [please check the changes]:

    string sql;
    System.IO.MemoryStream picStream = new System.IO.MemoryStream();

    emp_picture.Image.Save(picStream, System.Drawing.Imaging.ImageFormat.Jpeg);

    byte[] bytePic = picStream.ToArray();
    string ParamImageStr = "0x" + BitConverter.ToString(bytePic, 0).Replace("-", string.Empty); //New Line

    sql = "INSERT INTO employees (employee_number, first_name, last_name, address, city, " +
  "zip_code, contact_info, truck_number, trailer_number, picture) " +
  "VALUES ('" + Driver_Number.Text + "', '" + First_Name.Text + "', '" + Last_Name.Text +
      "', '" + Address.Text + "', '" + City.Text + "', '" + Zip_Code.Text + "', '" + Contact_info.Text + "', '" +
      Truck_Number.Text + "', '" + Trailer_Number.Text +"', '"+ParamImageStr +"')"; //Added ParamImageStr

Hope this help.

Upvotes: 0

Wajih
Wajih

Reputation: 791

Assuming that your images are of very small size, There are a few ways of doing this

  1. Create a package, lets say a class that holds the image data and the "other" data.
  2. Serialize it and send it over the network, receive it on the other end and de-serialize it.

Note that there is a limit to sending data over the network. This goes deep down to the packet level of TCP. Therefore, generally in C# code examples- you find sizes of payload equal to 1024. That being maximum limit for a single send/receive request.

If you have a large image and need to send it over the network, you have to break it up and send it in pieces. E.g.

Sending

  1. Break the image, make a incremental/ascending sequence number. Keep this initial number somewhere
  2. Send first image part over the network with a custom string tag like "image start"
  3. Send last image part over the network with a custom string tag like "image end"
  4. Use the initial number and attach it to the "other" data and send it over the network and possibly another string "other_data" attached to indicate "other data"

Receiving

  1. Now when you receive the image in parts, save the parts in a dictionary like image<int,byte[]>. you know when you get the first image part by checking the string "image start"
  2. Save every part in ascending sequence number (this allows you to reconstruct the image correctly)
  3. You know when you receive the last image part by checking the "image end" string.
  4. You know when you have received the "other" data
  5. Sort the image data in the list in ascending order, extract the bytes and you have your image.

Hope this gives you an idea or a start. :)

Upvotes: 1

Related Questions