How to Add & Retrieve Images from a SQL database using LINQ in c#

Today I received an email from a friend, asking how to save a binary file in to the database using LINQ to SQL. This was the second time a person had asked me the same question, So I thought of having a blog entry about it.

Assume that we need to upload a file and save its name, and the file in the database table. so lets see how we do it.

This is my Table

CREATE TABLE [dbo].[Files2](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [FileName] [varchar](50) NOT NULL,
   [FileSource] [image] NOT NULL,
)
GO

And The Following Stored Procedure is used to Insert the Value:

CREATE PROCEDURE [dbo].[ADDFILE]
(
   @FILENAME VARCHAR(50),
   @FILESOURCE image
)
AS
SET NOCOUNT ON

INSERT INTO Files([FileName],[FileSource])
VALUES(@FILENAME,@FILESOURCE)

1.Storing Images

The following code is used to upload and save the file using web form with a text box for file name, and a file upload control to upload the file. on the click event of a button this code is called.

protected void Button1_Click(object sender, EventArgs e)
{

    if (FileUploader.HasFile && FileUploader.PostedFile.ContentLength > 0)
     {
         string filename = txtFileName.Text;
         //Read the file in to a byte Array.
         byte[] filebyte = FileUploader.FileBytes;

   System.Data.Linq.Binary fileBinary = new System.Data.Linq.Binary(filebyte);
         MyDataDataContext MyData = new MyDataDataContext();

         MyData.ADDFILE(filename, fileBinary);
         lblStatus.Text = "File Uploaded Successfully!";

       }
}

The Main thing to remember here is that we need to pass the FileUploader.FileBytes to the constructor of System.Data.Linq.Binary.

2.Rerieving Images

context.Response.ContentType = "image/jpeg";

MyDataDataContext ctx = new MyDataDataContext();

//This part os for to getting the image file from data base
Img img = ctx.Imgs.SingleOrDefault(c => c.id == 2);
byte[] ret = img.image.ToArray();

context.Response.BinaryWrite(ret);