Posts
19
Comments
28
Trackbacks
1
Storing files to database and downloading it using ASP.NET

1) First create a new column and set it as varbinary(MAX) for the datatype.

2) Use this code to store the file to database:

            //Read File to Bytes
                    FileStream st = new FileStream(<Location of the file>, FileMode.Open);
                    byte[] fileData= new byte[st.Length];
                    st.Read(fileData, 0, (int)st.Length);
                    st.Close();

                    SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ID",<Reference ID>),
                                                    new SqlParameter("@Data",fileData)  };

            int i = SqlAccessor.ExecuteNonQuery(<ConnectionString>, SqlAccessor.SqlCommandBuilder(new SqlCommand (<Stored Procedure Name>), param), CommandType.StoredProcedure, out retVal);

 I've used Application Blocks to simplify data operation.

To Retrieve the data and to be downloaded from an ASP.NET Page:

3)  I created a function that returns a byte array with ID as parameter

 public static byte[] GetFileFromDB(string id)
    {
        object[] retVal = null;
        byte[] file = null;
        SqlParameter[] param = new SqlParameter[]
        {
            new SqlParameter ("@id", id)
        };
        string _connString = ConfigurationManager.ConnectionStrings[<ConnectionStringName>].ConnectionString.ToString();
        SqlDataReader reader = SqlAccessor.ExecuteReader(_connString, SqlAccessor.SqlCommandBuilder(new SqlCommand("SourceFileLogSelFileDataProc"), param), CommandType.StoredProcedure, out retVal);

        if (reader.Read())
        {
            file = (byte[])reader["<varbinary column that we define in Step 1>"];
        }

        return file;
    }

4) And on the ASP.NET Page itself, on the Page_Load event:

           byte[] fileData = Utils.GetFileFromDB(<ID>);
            Response.ClearContent();
            Response.AddHeader("Content-Disposition", "attachment; filename=" + <filename>);
            BinaryWriter bw = new BinaryWriter(Response.OutputStream);
            bw.Write(fileData);
            bw.Close();
            Response.ContentType = ReturnExtension(<file extension>);
            Response.End();

 5) I have another method to determine the ContentType:

  private string ReturnExtension(string fileExtension)
    {
        switch (fileExtension)
        {
            case ".htm":
            case ".html":
            case ".log":
                return "text/HTML";
            case ".txt":
                return "text/plain";
            case ".doc":
                return "application/ms-word";
            case ".tiff":
            case ".tif":
                return "image/tiff";
            case ".asf":
                return "video/x-ms-asf";
            case ".avi":
                return "video/avi";
            case ".zip":
                return "application/zip";
            case ".xls":
            case ".csv":
                return "application/vnd.ms-excel";
            case ".gif":
                return "image/gif";
            case ".jpg":
            case "jpeg":
                return "image/jpeg";
            case ".bmp":
                return "image/bmp";
            case ".wav":
                return "audio/wav";
            case ".mp3":
                return "audio/mpeg3";
            case ".mpg":
            case "mpeg":
                return "video/mpeg";
            case ".rtf":
                return "application/rtf";
            case ".asp":
                return "text/asp";
            case ".pdf":
                return "application/pdf";
            case ".fdf":
                return "application/vnd.fdf";
            case ".ppt":
                return "application/mspowerpoint";
            case ".dwg":
                return "image/vnd.dwg";
            case ".msg":
                return "application/msoutlook";
            case ".xml":
            case ".sdxl":
                return "application/xml";
            case ".xdp":
                return "application/vnd.adobe.xdp+xml";
            default:
                return "application/octet-stream";
        }
    }

 

Hope this helps.

posted on Thursday, November 20, 2008 4:59 PM Print
Comments
Gravatar
# re: Storing files to database and downloading it using ASP.NET
Ron Kunce
11/27/2008 3:57 AM
All I can say is WOW!!!!

I Love it an all-in-one file storage to keep with the database record it is associated! This is one problem that has been getting out of hand to control and manage and this simplifies that control so beutifully! Thank You!
Comments have been closed on this topic.