Friday, November 26, 2010

FILESTREAM storage in SQL 2008 with ASP.net MVC Part 2/2

Updating FILESTREAM enabled Table for easy access in C#:

1. Open FIleSTREAM enabled table in design mode in SQL Server Management Studio

2. Add following computed properties:

PathName with formula value FileContents.pathName()



3. Add FileStreamTransactionContext with formula value get_filestream_transaction_context()



4. Save changes and accept any validation warning appeared

Creating LINQ Domain Model:

Table to class mapping:

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlTypes;

namespace FileUploadSample.Models
{
    [Table(Name = "Files")]
    public class tbl_Files
    {
       
        [Column(IsPrimaryKey = true, IsDbGenerated = false)]
        public Guid FileID { get; set; }

        [Column]
        public String FileName { get; set; }

        [Column]
        public Int64 Length { get; set; }
        [Column(Name = "pathName", IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        private String _PathName { get; set; }

        [Column(Name = "FileStreamTransactionContext", IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        private byte[] _FileStreamTransactionContext { get; set; }

        public String PathName
        {
            get
            {
                return this._PathName;
            }
        }

        public byte[] FileStreamTransactionContext
        {
            get
            {
                return this._FileStreamTransactionContext;
            }
        }       
       
    }
}


SQL Repository:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using FileUploadSample.Controllers;
using System.Data.Linq;
using System.Transactions;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Data;


namespace FileUploadSample.Models
{

    public class tbl_FilesRepository
    {
        private String connectionString;
        private Table<tbl_Files> FileTable;
        private DataContext FilesContext;
        public tbl_FilesRepository(string connectionString)
        {
            FilesContext = new DataContext(connectionString);
            FileTable = FilesContext.GetTable<tbl_Files>();
            //FileTable = (new DataContext(connectionString)).GetTable();
            this.connectionString = connectionString;

        }

        public Table<tbl_Files> getTable()
        {
            return FileTable;
        }
        public void Addfile(tbl_Files ObjFile)
        {
            FileTable.InsertOnSubmit(ObjFile);
            FileTable.Context.CommandTimeout = 3600;
            FileTable.Context.SubmitChanges();

        }
        public void downloadFileStream(Guid id, HttpResponseBase Response)
        {
           
            using (TransactionScope ts = new TransactionScope())
            {
                var selectedFile = FileTable.Where(Tfile => Tfile.FileID == id).FirstOrDefault();
                SqlFileStream sqlFile = new SqlFileStream(((SqlString)selectedFile.PathName).Value, ((SqlBinary)selectedFile.FileStreamTransactionContext).Value, System.IO.FileAccess.Read);



                if (selectedFile != null)
                {
                  

                    // Buffer to read 10K bytes in chunk:
                    byte[] buffer = new Byte[1024 * 512]; //512 KB

                    // Length of the file:
                    int length;

                    // Total bytes to read:
                    long dataToRead;

                    try
                    {
                    
                        // Total bytes to read:
                        dataToRead = sqlFile.Length;

                        Response.ContentType = "application/octet-stream";
                        Response.AddHeader("Content-Disposition", "attachment; filename=" + selectedFile.FileName);

                        // Read the bytes.
                        while (dataToRead > 0)
                        {
                            // Verify that the client is connected.
                            if (Response.IsClientConnected)
                            {
                                // Read the data in buffer.
                                length = sqlFile.Read(buffer, 0, 1024 * 512);

                                // Write the data to the current output stream.
                                Response.OutputStream.Write(buffer, 0, length);

                                // Flush the data to the HTML output.
                                Response.Flush();

                                buffer = new Byte[1024 * 512];
                                dataToRead = dataToRead - length;
                            }
                            else
                            {
                                //prevent infinite loop if user disconnects
                                dataToRead = -1;
                            }
                        }
                        ts.Complete();
                    }
                    catch (Exception ex)
                    {
                        // Trap the error, if any.
                        Response.Write("Error : " + ex.Message);
                        ts.Dispose();
                    }
                    finally
                    {
                        if (sqlFile != null)
                        {
                            //Close the file.
                            sqlFile.Close();
                            Response.Close();
                        }
                       
                    }
                }
            }
        }

        public bool uploadStream(HttpRequestBase Request)
        {
            foreach (string inputTagName in Request.Files)
            {
                HttpPostedFileBase file = Request.Files[inputTagName];
                if (file.ContentLength > 0)
                {
                    SqlFileStream sqlFile = null;
                    using (TransactionScope ts = new TransactionScope())
                    {
                        try
                        {
                            FileUploadSample.Models.tbl_Files objFile = new FileUploadSample.Models.tbl_Files();
                            objFile.FileName = file.FileName.Split('\\')[file.FileName.Split('\\').Length - 1]; ;
                            objFile.Length = Convert.ToInt64(file.ContentLength / 1024);
                            objFile.FileID = Guid.NewGuid();
                            //objFile.FileContents = new byte[0];
                            Addfile(objFile);

                            //Save file on FILESTREAM                        
                            sqlFile = new SqlFileStream(((SqlString)objFile.PathName).Value, ((SqlBinary)objFile.FileStreamTransactionContext).Value, System.IO.FileAccess.Write);
                            byte[] buffer = new byte[1024 * 1024]; // 512Kb
                            int bytesRead = file.InputStream.Read(buffer, 0, buffer.Length);
                            while (bytesRead > 0)
                            {
                                sqlFile.Write(buffer, 0, bytesRead);
                                bytesRead = file.InputStream.Read(buffer, 0, buffer.Length);
                            }
                            
                            ts.Complete();
                            return true;
                           
                        }
                        catch (Exception ex)
                        {
                           
                            ts.Dispose();
                            return false;
                        }
                        finally
                        {
                            if (sqlFile != null)
                                sqlFile.Close();

                        }
                      
                    }
                   
                }
           }// For Each Loop
            return false; // If this LOC is reached that means there is no valid file to upload.
        }//Method uploadStream()

    }
}


Creating View:


<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage>" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Index
asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <script type="text/javascript">
        $(function () {
            $("#dialog").dialog({
                bgiframe: true,
                height: 140,
                modal: true,
                autoOpen: false,
                resizable: false
            })
        });
       
    script>
   
   
    <h2>
        Indexh2>
    <div id="dialog" title="Upload files">
        <% using (Html.BeginForm("UploadFileStream", "File", FormMethod.Post, new { enctype = "multipart/form-data" }))
           {%>
        <p>
            <input type="file" id="fileUpload" name="fileUpload" size="23" />
        p>
        <p>
            <input type="submit" value="Upload file" />p>
        <% } %>
    div>
    <a href="#" onclick="jQuery('#dialog').dialog('open'); return false">Upload Filea>
    <br />
    <br />
    <br />
    <table width="100%">
        <tr>
            <th>
                Name
            th>
            <th>
                File Url
            th>
            <th>
                Uploaded Date
            th>
            <th>
                Size
            th>
           
        tr>
        <% foreach (var item in Model)
           {
               string fileType = System.IO.Path.GetExtension(item.FileName);
        %>
        <tr>
            <td>
                <%= Html.Encode(item.FileName)%>
            td>
            <td style="vertical-align: middle">
                <a href='<%= Html.Encode("/file/downloadFileStream/"+item.FileID)%>'>Download a>
            td>
            <td style="vertical-align: middle">
             
            td>
            <td style="vertical-align: middle">
                <%= Html.Encode(item.Length) + " KB" %>
            td>
          
        tr>
        <% } %>
    table>
asp:Content>


Creating Controller:

Note:We must have to use Integrated Security=True in connection string

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using FileUploadSample.Models;
using System.IO;
using System.Data.Linq;

namespace FileUploadSample.Controllers
{
       public class FileController : Controller
    {
       
        static String connectionString = "Data Source=ASAD-PC-DESKTOP\\SQLSERVER_R2;Initial Catalog=CSAFMS;Integrated Security=True";
    
        FileRepository fileRepository = new FileRepository(connectionString);
        tbl_FilesRepository tbl_FilesRepository = new tbl_FilesRepository(connectionString);

        public ActionResult Index()
        {
            return View(tbl_FilesRepository.getTable().ToList());
        }
       
        public ActionResult downloadFileStream(Guid id)
        {           
            tbl_FilesRepository.downloadFileStream(id, Response);
            return View();
        }
               
        public void UploadFileStream()
        {
            tbl_FilesRepository.uploadStream(Request);
            Response.Redirect("~/File");
        }
    }
}


Source download password (asadyousufi.blogspot.com)

Followers

Search This Blog