ASP.Net - Insert and Retrieve files in SQL Server using C#

Demo

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)

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

Insert the file in database using 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();
}

Retrieving file from database using 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) {}
}