How to intert image in SQLServer uing sql query without using parameters.

Question

I am trying to insert image in my databse .However I dont want to use the parameters as my set up of coding pattern does not allow this.Is there a way out?

I know that following code inserts the image

byte[] imageData = ReadFile(txtImagePath.Text);
SqlConnection CN = new SqlConnection(txtConnectionString.Text);
string qry = "insert into ImagesStore (ImageData) values( @ImageData)";
SqlCommand SqlCom = new SqlCommand(qry, CN);
SqlCom.Parameters.Add(new SqlParameter("@ImageData", (object)imageData));
//Open connection and execute insert query.
CN.Open();
SqlCom.ExecuteNonQuery();
CN.Close();

But However, I would like to use some thing like this without the SQL Parameters

byte[] imageData = ReadFile(txtImagePath.Text);
SqlConnection CN = new SqlConnection(txtConnectionString.Text);
string qry = "insert into ImagesStore (ImageData) values(IMAGE DATA IN SOME FORM MAY BE 0101000101011001100 I dont know!)";
SqlCommand SqlCom = new SqlCommand(qry, CN);
//Open connection and execute insert query.
CN.Open();
SqlCom.ExecuteNonQuery();
CN.Close();

Answer 1

I haven't used sqlserver, but can you use a blob where you can just insert any binary object regardless of format? I found this article which may be of some help: http://www.developer.com/net/asp/article.php/3761486/Working-with-Binary-Large-Objects-BLOBs-Using-SQL-Server-and-ADONET.htm

Answer 2

You can use a hex notation:

INSERT INTO imagestore
(imagedata)
VALUES
(0xFF01);

would insert a blob with two bytes (255 and 1) into the table

Answer 3

Here is what I did:First converted the image into bytes then bytes into sting and inserted the sting using insert SQL, to retrieve the image I took back the steps!

    //convert to stirng
    Bitmap bmp = new Bitmap(@"D:/bmp.bmp");
    MemoryStream mem = new MemoryStream();
    bmp.Save(mem, System.Drawing.Imaging.ImageFormat.Jpeg );
    byte[] b = mem.ToArray();
    mem.Close();
    mem = null;
    System.Text.UnicodeEncoding a = new UnicodeEncoding();
    string s = System.Text.Encoding.Unicode .GetString(b);
    //test
    File.WriteAllText(@"D:/txt.txt", s);

    //convert back to image
    Image newImage;
    byte[] bytes = System.Text.Encoding.Unicode.GetBytes(s);
    using (MemoryStream ms = new MemoryStream(bytes.Length))
    {
        ms.Write(bytes, 0, bytes.Length);
        newImage = Image.FromStream(ms);
        ms.Close();
    }
    //test 
    pictureBox1.Image = newImage;
    //It works!
    //So just fire the SQL
   con.Executenonquery("insert into ImageDb ('img') values (" + s +")") ;

Answer 4

Dont'!

Sorry for dodging your question, but why do you want to insert the image as a blob? In my experience it is almost never a good idea. Instead store the path to the image file on disk.

It could be that rour requirements leave you no other option than to store the image as a blob in the db, but I would seriously reconsider the requirements as storing binary (image) data is almost always a bad idea.

Answer 5

I re-framed my question and I think I have got a solution plz Have a look at convert image to stream of characters .