Asp.net - Insert and Retrieve files in SQL Server using C#

Jul 04, 2015 By Mohammed Imtiyaz

Download

Demo

Demo

Introduction

In this tutorial we will learn how to insert and retrieve different type of files with different file formats
(E.g. Microsoft Word Document, WinRAR archive, Text Document, Image, Audio/Video, etc.) in SQL Database table using C#

First let us understand how the data is saved into the database table. Before uploading any file, it is converted into binary format and then it is saved in the database table.

Let us begin with creating a SQL Database (db_InsertRetrieveFilesSql)

NOTE: To create a database in SQL Server, go to the Database Folder of your downloaded file and open Database Script file and execute it using SQL Query Analyzer, the database will be created with tables.

Once you run the database script, the database table will be created as follows:

Demo

Insert file in database table - C#

protected void btnUploadFile_Click(object sender, EventArgs e)

    {

        HttpPostedFile hpf = FileUpload1.PostedFile;

        if (hpf.ContentLength > 0)

        {

            try

            {

                int intFileSize;

                byte[] myFileData = new byte[hpf.ContentLength];

                hpf.InputStream.Read(myFileData, 0, (int)hpf.ContentLength);

 

                // Gets the Size of the file.

                intFileSize = hpf.ContentLength;

 

                // Get the type of file. (.doc, .docx, .pdf, etc...)

                string strFileType = hpf.ContentType;

 

                string strSQL = "";

                strSQL = "INSERT INTO tbl_file";

                strSQL += "(file_name, file_extension, file_data, file_size)";

                strSQL += "VALUES(@fileName, @fileExtension, @fileData, @fileSize)";

 

                SqlCommand objCmd = new SqlCommand(strSQL, objConn);

 

                objCmd.Parameters.Add("@fileName", SqlDbType.VarChar, 200).Value = System.IO.Path.GetFileName(hpf.FileName);

                objCmd.Parameters.Add("@fileExtension", SqlDbType.VarChar, 30).Value = strFileType;

                objCmd.Parameters.Add("@fileSize", SqlDbType.BigInt, 99999).Value = intFileSize;

                if (intFileSize != 0)

                    objCmd.Parameters.Add("@fileData", SqlDbType.VarBinary, myFileData.Length).Value = myFileData;

                else

                    objCmd.Parameters.Add("@fileData", SqlDbType.VarBinary, myFileData.Length).Value = Convert.DBNull;

 

                objConn.Open();

                objCmd.ExecuteNonQuery();

                objConn.Close();

            }

            catch (Exception ex) { }

        }

        else

        {

            lblMessage.Text = "No file is selected yet, please select any file first";

        }

        lblMessage.Text = "File uploaded successfully!";

        getFiles();

    }

Retrieve file from database - C#

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        try

        {

            if (e.CommandName == "Open")

            {

                int intFileId = int.Parse(e.CommandArgument.ToString());

                string strQuery = "SELECT file_name, file_data, file_extension, file_size FROM tbl_file WHERE file_id_pk=" + intFileId;

 

                objConn.Open();

 

                SqlCommand objCmd = new SqlCommand(strQuery, objConn);

                SqlDataReader dr = objCmd.ExecuteReader();

                if (dr.Read())

                {

                    if (dr["file_data"] != null)

                    {

                        byte[] fileData = (byte[])dr["file_data"];

                        Response.Clear();

                        Response.Buffer = true;

                        Response.AddHeader("Content-Disposition", "attachment; filename = " + dr["file_name"].ToString());

                        BinaryWriter bw = new BinaryWriter(Response.OutputStream);

                        bw.Write(fileData);

                        bw.Close();

                        Response.ContentType = dr["file_extension"].ToString();

                        Response.Flush();

                        Response.Close();

                    }

                }

                objConn.Close();

            }

        }

        catch (Exception ex) { }

    }



Post your comment