I am trying to store an image in my SQL Server database, what datatype should I use?
In the below code aspx.cs
, I am trying to read all the bytes from the request inputstream and store it in the database, but the byte[]
array is not updated properly in table. Am I missing something?
protected void Page_Load(object sender, EventArgs e)
{
Request.InputStream.Position = 0;
byte[] Contents = new byte[Request.InputStream.Length];
Request.InputStream.Read(Contents, 0, (int)Request.InputStream.Length);
con.Open();
try
{
string query = "update tblImageUpload set " + IMAGE_ID + " = @imageBytes where Image_ID='" + CID + "'";
int i = 0;
using (cmd = new SqlCommand(query, con))
{
cmd.Parameters.Add("@imageBytes", SqlDbType.VarBinary, Contents.Length).Value = Contents;
i = cmd.ExecuteNonQuery();
}
Response.Write("Upload Query = " + query);
Response.Write("Upload Code = " + i);
} catch (Exception ex) {
Response.Write("Upload Code=" + ex);
}
VARBINARY(MAX) - Binary strings with a variable length can store up to 2^31-1 bytes. IMAGE - Binary strings with a variable length up to 2^31-1 (2,147,483,647) bytes.
Insert one image into SQL Server This table will have an integer (int) id and the image column named img. The data type that we are going to use to store images is the varbinary(max). The INSERT statement inserts the value 1 as the id and then inserts the image named 1. png from the folder img in the c drive.
varbinary [ ( n | max ) ]Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.
You can use VARBINARY
yes. You're probably best off going with VARBINARY(MAX)
to store them.
You can use it like this:
cmd.Parameters.Add("@imageBytes", SqlDbType.VarBinaryMax);
cmd.Parameters["@imageBytes"].Value = Contents;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With