Posts
19
Comments
28
Trackbacks
1
November 2008 Blog Posts
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 @ Thursday, November 20, 2008 4:59 PM | Feedback (1)