CODEWITHSUNDEEP
c#asp.netsqlsql-serverwinforms
Jeena
Jeena

Reputation: 101

Adding an image to SQL database using Visual C#

I am working on a visual C# program for image processing.I am trying to add image to sql database using Visual C# (Windows forms) and ADO.NET.

I have converted the image to binary form with filestream method but the image bytes are not getting saved in database. At the database image column, it says < Binary data > and no data is getting saved!

I have tried many methods for inserting( with and without stored procedures..etc) but always getting the same thing at database.

private void button6_Click(object sender, EventArgs e)
{
   try
   {
      byte[] image = null;
      pictureBox2.ImageLocation = textBox1.Text;
      string filepath = textBox1.Text;
      FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read);
      BinaryReader br = new BinaryReader(fs);
      image = br.ReadBytes((int)fs.Length);
      string sql = " INSERT INTO ImageTable(Image) VALUES(@Imgg)";
      if (con.State != ConnectionState.Open)
         con.Open();
      SqlCommand cmd = new SqlCommand(sql, con);
      cmd.Parameters.Add(new SqlParameter("@Imgg", image));
      int x= cmd.ExecuteNonQuery();
      con.Close();
      MessageBox.Show(x.ToString() + "Image saved");
   }
}

I am not getting error in code. the image got converted and entry is done in database but says < Binary Data > at the sql database.

Upvotes: 5

Views: 35197

Answers (4)

user2136053
user2136053

Reputation:

Execute this stored procedure:

create procedure prcInsert
(
   @txtEmpNo varchar(6),
   @photo image
)
as
begin
   insert into Emp values(@txtEmpNo, @photo)
end

Table Emp:

create table Emp
(
   [txtEmpNo] [varchar](6) NOT NULL,
   imPhoto image
)

Upvotes: 0

Jeena
Jeena

Reputation: 101

The selected file stream should be converted to byte array.

        FileStream FS = new FileStream(filepath, FileMode.Open, FileAccess.Read); //create a file stream object associate to user selected file 
        byte[] img = new byte[FS.Length]; //create a byte array with size of user select file stream length
        FS.Read(img, 0, Convert.ToInt32(FS.Length));//read user selected file stream in to byte array

Now this works fine. Database still shows < Binary data > but it could be converted back to image using memorystream method.

Thanks to all...

Upvotes: 5

GarethD
GarethD

Reputation: 69809

Assuming that you are using VARBINARY to store your image (which you should be), you may need to make your SqlParameter more strongly typed:

So instead of 

cmd.Parameters.Add(new SqlParameter("@Imgg", image));

You would use:

cmd.Parameters.Add("@Imgg", SqlDbType.VarBinary).Value = (SqlBinary)image;

You could also use System.IO.File.ReadAllBytes to shorten your code turning a file path into a byte array.

Upvotes: 1

majimenezp
majimenezp

Reputation: 312

Try with something like this: 

byte[] fileBytes=System.IO.File.ReadAllBytes("path to file");
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("insert  into table(blob,filename) values (@blob,@name)");
command.Parameters.AddWithValue("blob", fileBytes);
command.Parameters.AddWithValue("name", "filename");
command.ExecuteNonQuery();

Upvotes: 2

Related Questions