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

Mohammed Imtiyaz Jul 04, 2015

Demo

Insert and Retrieve files in SQL Server using C#

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)

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:

Insert and Retrieve files in SQL Server using C#

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) {}
}