How do I save the BLOB of an image into a sql database without saving it to the filesystem first?

Question

what I've been trying to find out is how I can store the BLOB of an image into my database without saving it to the filesystem first, so directly from the server's memory.

I use an sql server and among other form information I have 2 images that need to be stored in the database. I would also like to know how I can read them out and convert them back to images.

In the db I have "Thumbnail" which is of type "image". That should be correct if I'm not wrong.

For the image upload I use the following asp control:

<asp:FileUpload ID="_imageUpload" runat="server" />

I have never done anything like this as I am quite new to working with databases especially together with websites.

Oh, and sorry if this question has been asked and answered already.

Thanks in advance!

[edit]

My entire code:

protected void _uploadImageBtn_Click(object sender, EventArgs e)
{
    string extension;

    // checks if file exists
    if (!_imageUpload.HasFile)
    {
        _resultLbl.Text = "Please, Select a File!";
        return;
    }

    // checks file extension
    extension = System.IO.Path.GetExtension(_imageUpload.FileName).ToLower();

    if (!extension.Equals(".jpg") && !extension.Equals(".jpeg") && !extension.Equals(".png"))
    {
        _resultLbl.Text = "Only image files (.JPGs and .PNGs) are allowed.";
        return;
    }

    // checks if image dimensions are valid
    if (!ValidateFileDimensions(140, 152))
    {
        _resultLbl.Text = "Maximum allowed dimensions are: width 1520px and height <= 140px.";
        return;
    }

    int fileLen;
    string displayString = "";

    // Get the length of the file.
    fileLen = _imageUpload.PostedFile.ContentLength;

    // Create a byte array to hold the contents of the file.
    byte[] input = new byte[fileLen - 1];
    input = _imageUpload.FileBytes;

    // Copy the byte array to a string.
    for (int loop1 = 0; loop1 < fileLen; loop1++)
    {
        displayString = displayString + input[loop1].ToString();
    }

    try
    {

        SqlConnection sqlCn = new SqlConnection("Data Source=localhost;Initial Catalog=database;User ID=user;Password=pw");

        string qry = "INSERT INTO Project (thumbnail) VALUES (@thumbnail)";

        SqlCommand sqlCom = new SqlCommand(qry, sqlCn);

        sqlCom.Parameters.Add("@thumbnail", SqlDbType.Image, input.Length).Value = input;

        sqlCn.Open();
        sqlCom.ExecuteNonQuery();
        sqlCn.Close();

    }

    catch (Exception)
    {
        (...)
    }
}

public bool ValidateFileDimensions(int aHeight, int aWidth)
{
    using (System.Drawing.Image image = System.Drawing.Image.FromStream(_imageUpload.PostedFile.InputStream))
    {
        return (image.Height == aHeight && image.Width == aWidth);
    }
}

KV Prajapati · Accepted Answer · 2012-08-15 14:51:53Z

4

You can save the returned byte array from FileUpload.FileBytes.

if(_imageUpload.HasFile)
{
 byte[] imageData = _imageUpload.FileBytes;

 using(SqlConnection sqlCn = new SqlConnection("Server=localhost;database=databaseName;uid=userName;pwd=password"))
  {
    string qry = "INSERT INTO Project (thumbnail) VALUES (@thumbnail)";
    using(SqlCommand sqlCom = new SqlCommand(qry, sqlCn))
     {
       sqlCom.Parameters.Add("@thumbnail",
                              SqlDbType.Image,
                              imageData.Length).Value=imageData;
       sqlCn.Open();
       sqlCom.ExecuteNonQuery();
       sqlCn.Close();
     }
   }
}

EDIT:

protected void _uploadImageBtn_Click(object sender, EventArgs e)
{
    string extension;

    // checks if file exists
    if (!_imageUpload.HasFile)
    {
        _resultLbl.Text = "Please, Select a File!";
        return;
    }

    // checks file extension
    extension = System.IO.Path.GetExtension(_imageUpload.FileName).ToLower();

    if (!extension.Equals(".jpg") && !extension.Equals(".jpeg") && !extension.Equals(".png"))
    {
        _resultLbl.Text = "Only image files (.JPGs and .PNGs) are allowed.";
        return;
    }

    // checks if image dimensions are valid
    if (!ValidateFileDimensions(140, 152))
    {
        _resultLbl.Text = "Maximum allowed dimensions are: width 1520px and height <= 140px.";
        return;
    }


    byte []input = _imageUpload.FileBytes;
    SqlConnection sqlCn = new SqlConnection("Data Source=localhost;Initial 
                                 Catalog=database;User ID=user;Password=pw");

    string qry = "INSERT INTO Project (thumbnail) VALUES (@thumbnail)";
    SqlCommand sqlCom = new SqlCommand(qry, sqlCn);
    sqlCom.Parameters.Add("@thumbnail", SqlDbType.Image, input.Length).Value = input;
    sqlCn.Open();
    sqlCom.ExecuteNonQuery();
    sqlCn.Close();
}

edited Aug 15, 2012 at 14:51

answered Aug 14, 2012 at 12:06

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Some Developer Over a year ago

hey, I tried it several times and I just debugged it because the INSERT always failed and that happened, because (in your example here) the "bytes" array contains no value so it tries to fill in NULL which of course does not work as it is not allowed. Are you sure that this should work?

Some Developer Over a year ago

how do I do that? I updated my code above so you can see what I do excactly

Some Developer Over a year ago

I actually already did that like this: if (!_imageUpload.HasFile) { _resultLbl.Text = "Please, Select a File!"; return; }

Some Developer Over a year ago

How does the loop need to like like and what excactly do I have to loop?

KV Prajapati Over a year ago

You must have to include all columns (which are not null) in your INSERT SQL (query). Something like : INSERT INTO PROJECT (col1,col2,col3) values (@para1,para2,para3)

Collectives™ on Stack Overflow

How do I save the BLOB of an image into a sql database without saving it to the filesystem first?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related