1

Let's say if I capture a screen shot. There is my code for it

int sWidth = 1600, sHeight = 1200;

Bitmap B_M_P = Bitmap(sWidth, sHeight);

Graphics gfx = Graphics.FromImage((Image)B_M_P);
gfx.CopyFromScreen(0, 0, 0, 0, new Size(screenWidth, screenHeight));            

B_M_P.Save("img.jpg", ImageFormat.Jpeg);

instead of saving this to an Image, i wanna be able to send this to my SQL or MySQL and store them in the database as BLOB.

i know the LINQ as well to query the DB. What i don't know is the intermediate portion.

  • What kind of Data Type will be used to INSERT in a BLOB column, my guess is it will be a Byte[]?

if it is a 'Byte', then the conversion is pretty easy.

ImageConverter imgc = new ImageConverter();
Byte[] temp = (byte[])imgc.ConvertTo(B_M_P,typeof(byte[]));

so that afterwards i can prepare my query 

"INSERT INTO EMPLOYEE (pic) VALUES ('"+temp+"');"
  • If not then WHAT IS THE TYPE & HOW TO CONVERT
Moon
  • 19,518
  • 56
  • 138
  • 200
  • That method of inserting data is very **VERY** wrong, You should be using [Prameters](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx). If you do it the way you want to it opens your self to exploitation. Even if your case this time would not likely allow it to happen it is a VERY bad habit to get. When you hear about "SQL Injection attacks" writing queries like you did is what causes it. – Scott Chamberlain Feb 24 '12 at 20:24

1 Answers1

6

Your are correct that you just need to turn it in to a byte array. The datatype of your table should be VarBinary(max).

Note that there is a Image datatype, but that datatype will be removed in a future version of Microsoft SQL Server, so Microsoft is recommending everyone switch to VarBinary for future compatibility.

BLOBs and VarBinary(max) are the same thing. From Understanding VARCHAR(MAX) in SQL Server 2005

To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.

Microsoft recommend using MAX data types instead of BLOBs in SQL Server 2005. In fact, BLOBs are being deprecated in future releases of SQL Server.

That Image data type I mentioned at the top is a example of the old-style BLOB datatype.

Here is a example of how the code should be

using(var cmd = SqlCommand("INSERT INTO EMPLOYEE (pic) VALUES (@pic);", connection)
{
    cmd.Parameters.Add("@pic", temp);
    cmd.ExecuteNonQuery();
}

However there are some Gotchas if you are working with large files, see this SO answer and its links for in depth look at storing large data objects in the database.

Community
  • 1
  • 1
Scott Chamberlain
  • 124,994
  • 33
  • 282
  • 431
  • there is problem, the data type of my table is BLOB, cuz i have some tutorial which will teach me how to retrieve images when they are saved in BLOB, so should i stick with BLOB or try to do it via VarBinary – Moon Feb 24 '12 at 19:57
  • i remember seeing a question here on stackoverflow, to blob or not – Moon Feb 24 '12 at 20:04
  • @JunaidSaeed Your tutorial will work fine with varbinary(max). – Scott Chamberlain Feb 24 '12 at 20:22
  • Looks like the database is MySQL and not MSSQL, see [PHP retrieve VARBINARY & display as an image](http://stackoverflow.com/questions/9440759/php-retrieve-varbinary-display-as-an-image) – hakre Feb 25 '12 at 03:28