← Tutti gli articoli
Visual Studio 2010 ADO.Net Entity Framework - Sql Server 2008 - FILESTREAM - ASP.NET FILE UPLOAD DOWNLOAD
19 agosto 2010 · EDF · Article · 859 visite
Pratical example for upload and download file from/to Sql Server 2008 table using FileStream. Visual Studio 2010 and .Net Framework 4.0 , SQL Sever 2008 FILESTREAM, ADO.Net Entity Framework with FILESTREAM.
In Sql Server 2008 We create the table Files using the following script:
The Entity Data Model generate for us the code. In particular the fiel FileContent will be:
In the above code block I create a new instance of the object context (ctx) and I created a new object files. Then I moved the upload bytes stream to the field FileContent. Then, I use standard Entity Framework methods to add the file into the EntitySet and save the changes to the database.
For other information see the site of Guy Burnstein
CREATE TABLE [dbo].[Files](
[FileID] [bigint] IDENTITY(1,1) NOT NULL,
[FileGuid] [uniqueidentifier] NOT NULL,
[FileContent] [varbinary](max) NOT NULL,
[FileName] [nvarchar](256) NULL,
[Description] [nvarchar](256) NULL,
[Extension] [nvarchar](256) NULL,
[FileSize] [bigint] NULL,
[ContentType] [nvarchar](256) NULL,
[CreationDate] [datetime] NOT NULL,
[UserId] [uniqueidentifier] NULL,
CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[Files] ADD CONSTRAINT [DF_Files_FileGuid] DEFAULT (newid()) FOR [FileGuid]
GO
ALTER TABLE [dbo].[Files] ADD CONSTRAINT [DF_Files_FileContent] DEFAULT (0x) FOR [FileContent]
GO
ALTER TABLE [dbo].[Files] ADD CONSTRAINT [DF_Files_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
The Entity Data Model generate for us the code. In particular the fiel FileContent will be:
[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=false)]
[DataMemberAttribute()]
public global::System.Byte[] FileContent
{
get
{
return StructuralObject.GetValidValue(_FileContent);
}
set
{
OnFileContentChanging(value);
ReportPropertyChanging("FileContent");
_FileContent = StructuralObject.SetValidValue(value, false);
ReportPropertyChanged("FileContent");
OnFileContentChanged();
}
}
FILE UPLOAD IN ASP.NET
if (FileUpload1.HasFile)
{
string filename= Path.GetFileName(FileUpload1.PostedFile.FileName);
string contenttype= Path.GetFileName(FileUpload1.PostedFile.ContentType);
string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
MaragnaNetEntities ctx = new MaragnaNetEntities(ConfigurationManager.ConnectionStrings["MaragnaNetEntities"].ToString());
Files files = new Files();
Stream stream= FileUpload1.PostedFile.InputStream;
byte[] buffer= new byte[stream.Length];
stream.Read(buffer,0, buffer.Length);
files.FileContent= buffer;
files.CreationDate = DateTime.Now;
files.FileName=filename;
//files.Extension= da valorizzare
files.Description = txtFileDescription.Text;
files.FileGuid= Guid.NewGuid();
files.ContentType = contenttype;
ctx.AddToFiles(files);
ctx.SaveChanges();
txtFileDescription.Text = ""
In the above code block I create a new instance of the object context (ctx) and I created a new object files. Then I moved the upload bytes stream to the field FileContent. Then, I use standard Entity Framework methods to add the file into the EntitySet and save the changes to the database.
FILE DOWNLOAD - ASP.NET
private bool DownloadFile(long lFileID)
{
//File Path and File Name
//string filePath = Server.MapPath("~/ApplicationData/DownloadableProducts");
// string _DownloadableProductFileName = "DownloadableProduct_FileName.pdf"; MaragnaNetEntities ctx = new MaragnaNetEntities(ConfigurationManager.ConnectionStrings["MaragnaNetEntities"].ToString());
var files = ctx.Files.Where(c => c.FileID == lFileID).FirstOrDefault();
if (Roles.IsUserInRole("Adfin") || Roles.IsUserInRole("Administrators"))
{
//Check whether file exists in specified location
if (files != null)
{
try
{
long startBytes = 0;
string lastUpdateTiemStamp = files.CreationDate.ToLongDateString(); // File.GetLastWriteTimeUtc(filePath).ToString("r");
//string _EncodedData = HttpUtility.UrlEncode(_DownloadableProductFileName, Encoding.UTF8) + lastUpdateTiemStamp; Response.Clear();
Response.Buffer = false;
Response.AddHeader("Accept-Ranges", "bytes");
//Response.AppendHeader("ETag", "\"" + _EncodedData + "\"");
Response.AppendHeader("Last-Modified", lastUpdateTiemStamp);
Response.ContentType = files.ContentType; // "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment;filename=" + files.FileName);
Response.AddHeader("Content-Length", files.FileContent.Length.ToString());
Response.AddHeader("Connection", "Keep-Alive");
Response.ContentEncoding = Encoding.UTF8;
Response.BinaryWrite(files.FileContent);
Response.Flush();
return true;
}
catch
{
return false;
}
finally
{
//Response.End();
//_BinaryReader.Close();
//myFile.Close();
}
}
else System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(),
"FileNotFoundWarning", "alert('File is not available now!')", true);
}
else
{
System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(),
"NotEligibleWarning", "alert('Sorry! File is not available for you')", true);
}
Conclusion
ADO.Net Entity Framework adds the support of using SQL Server 2008 FILESTREAM in Visual Studio 2010 and .Net Framework 4.0. The FILESTREAM columns is represented as a byte[] field that we can access it and get the file contents.
For other information see the site of Guy Burnstein