how to save and fetch Files from SQL Server Database in ASP.Net using c#

In this web programming tutorial we will learn that how we can save files in sql server database and how we can fetch or retrieve saved files from sql server database in asp.net using c#.

Below is the table structure in order to save files

here is the connection string that will be used to connect to database

now here we need to read the file using file Stream and then File Stream will be converted into byte array using BinaryReader in order to save into the database.

// Read the file and convert it to Byte Array
string filePath = Server.MapPath("APP_DATA/TestDoc.docx");
string filename = Path.GetFileName(filePath);
 
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
br.Close();
fs.Close();

after reading the file we need to save it into database by using following command

//insert the file into database
string strQuery = "insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-word";
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
InsertUpdateData(cmd);

Below function InsertUpdateData accepts the SqlCommand object, executes the query and inserts the data into the database and thats it our file will be saved into database.

private Boolean InsertUpdateData(SqlCommand cmd)
{
    String strConnString = System.Configuration.ConfigurationManager
    .ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        cmd.ExecuteNonQuery();
        return true;
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
        return false;
    }
    finally
    {
        con.Close();
        con.Dispose();
    }
}

Now we need to fetch the data and need to display. In order to do so web need to write a select command to fetch the data from sql server database table.

string strQuery = "select Name, ContentType, Data from tblFiles where id=@id";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;
DataTable dt = GetData(cmd);
if (dt != null)
{
    download(dt);
}

below function will simply execute select statement in sqlserver query window.

private DataTable GetData(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager
    .ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        return dt;
    }
    catch
    {
        return null;
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
    }
}

Here is the function which initiates the download of file. It basically reads the file contents into a Byte array and also gets the file name and the Content Type. Then it writes the bytes to the response using Response.BinaryWrite

private void download (DataTable dt)
{
    Byte[] bytes = (Byte[])dt.Rows[0]["Data"];
    Response.Buffer = true;
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = dt.Rows[0]["ContentType"].ToString();
    Response.AddHeader("content-disposition", "attachment;filename="
    + dt.Rows[0]["Name"].ToString());
    Response.BinaryWrite(bytes);
    Response.Flush();
    Response.End();
}