Updating FILESTREAM enabled Table for easy access in C#:
1. Open FIleSTREAM enabled table in design mode in SQL Server Management Studio
PathName with formula value FileContents.pathName()
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)
