SQL服务器的FileStream如何填充FILESTREAM列 [英] SQL Server FileStream how to populate the filestream column

问题描述

我已经走过了跨越几个不同的方法将数据插入到SQL Server（有关的FileStream）。什么是插入的FileStream对象的最佳方法？下面是一是直接并插入另一个放于为FileStream对象的占位符的方法之间的主要区别。

一种方法是，他们通过插入插入直接通过C＃文件：

链接：的FileStream

  con.Open（）; 
字符串SQL =INSERT INTO MyFsTable VALUES（@fData，@fname，默认）; 
的SqlCommand CMD =新的SqlCommand（SQL，CON）; 
 cmd.Parameters.Add（@ FDATA，SqlDbType.Image，fileData.Length）.value的= FILEDATA; 
 cmd.Parameters.Add（@ FNAME，SqlDbType.NVarChar）.value的= fi.Name; 
 cmd.ExecuteNonQuery（）; 
 con.Close（）; 
  

他们插入一排，但留下的文件（FILESTREAM字段）为空的另一种方法。我不得不把在一个虚拟的值，因为当FILESTREAM字段为空我获取文件的路径调用返回NULL：

链接：的FileStream

  5：如果（FileUpload1.FileContent.Length大于0）
 6：{
 7：SqlConnection的objSqlCon =新的SqlConnection（ConfigurationManager中。的ConnectionStrings [的ConnectionString]的ConnectionString）。 
 8分配：objSqlCon.Open（）; 
 9的SqlTransaction objSqlTran = objSqlCon.BeginTransaction（）; 
 10：
 11的SqlCommand objSqlCmd =新的SqlCommand（FileAdd，objSqlCon，objSqlTran）; 
 12：objSqlCmd.CommandType = CommandType.StoredProcedure; 
 13：
 14：SqlParameter的objSqlParam1 =新的SqlParameter（@ SystemNumber，SqlDbType.Int）; 
 15分配：objSqlParam1.Value =1; 
 16：
 17：SqlParameter的objSqlParam2 =新的SqlParameter（@文件类型，SqlDbType.VarChar，4）; 
 18：objSqlParam2.Value = System.IO.Path.GetExtension（FileUpload1.FileName）; 
 19分配：
 20：的SqlParameter objSqlParamOutput =新的SqlParameter（@文件路径，SqlDbType.VarChar，-1）; 
 21：objSqlParamOutput.Direction = ParameterDirection.Output; 
 22：
 23：objSqlCmd.Parameters.Add（objSqlParam2）; 
 24：objSqlCmd.Parameters.Add（objSqlParam1）; 
 25：objSqlCmd.Parameters.Add（objSqlParamOutput）; 
 26：
 27：
 28：objSqlCmd.ExecuteNonQuery（）; 
 29：
 30：字符串路径= objSqlCmd.Parameters [@文件路径] Value.ToString（）; 
 31：
 32：objSqlCmd =新的SqlCommand（选择GET_FILESTREAM_TRANSACTION_CONTEXT（），objSqlCon，objSqlTran）; 
 33：
 34：字节[] = objContext（字节[]）objSqlCmd.ExecuteScalar（）; 
 35：
 36：
 37：SqlFileStream objSqlFileStream =新SqlFileStream（路径，objContext，FileAccess.Write）; 
 38：
 39：objSqlFileStream.Write（缓冲液，0，buffer.Length）; 
 40：objSqlFileStream.Close（）; 
 41：
 42：objSqlTran.Commit（）; 
  

解决方案

请参阅 FILESTREAM MVC：从SQL Server 的下载和上传图像为例说明如何上传并以高效，面向数据流的方式下载文件流。你追求，分配一个缓冲整个上传文件的大小（我假设你做同样的太上提供内容）的方式是非常低效的，你的ASP程序内存会被分解到受这么大的字节块[]操作。

I have come a across a few different methods for inserting data into SQL Server (For FileStream). What is the best method for inserting the FileStream objects? The main difference between the approaches below being one directly did the insert and the other put in a place holder for the FileStream object.

One approach is that they were directly inserting the document via C# through an insert:

Link: FileStream

  con.Open();
  string sql = "INSERT INTO MyFsTable VALUES (@fData, @fName, default)";
  SqlCommand cmd = new SqlCommand(sql, con);
  cmd.Parameters.Add("@fData", SqlDbType.Image, fileData.Length).Value = fileData;
  cmd.Parameters.Add("@fName", SqlDbType.NVarChar).Value = fi.Name;
  cmd.ExecuteNonQuery();
  con.Close();

Another approach they inserted a row, but left the document (FileStream Column) null. I had to put in a dummy value because when the FileStream column was null my Get File Path call returned Null:

Link: FileStream

  5: if (FileUpload1.FileContent.Length > 0)
   6: {
   7:     SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
   8:     objSqlCon.Open();
   9:     SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
  10:  
  11:     SqlCommand objSqlCmd = new SqlCommand("FileAdd",objSqlCon,objSqlTran);
  12:     objSqlCmd.CommandType = CommandType.StoredProcedure;
  13:  
  14:     SqlParameter objSqlParam1 = new SqlParameter("@SystemNumber", SqlDbType.Int);
  15:     objSqlParam1.Value = "1";
  16:  
  17:     SqlParameter objSqlParam2 = new SqlParameter("@FileType", SqlDbType.VarChar,4);
  18:     objSqlParam2.Value = System.IO.Path.GetExtension(FileUpload1.FileName);
  19:  
  20:     SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);
  21:     objSqlParamOutput.Direction = ParameterDirection.Output;
  22:  
  23:     objSqlCmd.Parameters.Add(objSqlParam2);
  24:     objSqlCmd.Parameters.Add(objSqlParam1);
  25:     objSqlCmd.Parameters.Add(objSqlParamOutput);
  26:  
  27:  
  28:     objSqlCmd.ExecuteNonQuery();
  29:  
  30:     string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();
  31:  
  32:     objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
  33:  
  34:     byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
  35:     
  36:  
  37:     SqlFileStream objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write);
  38:     
  39:     objSqlFileStream.Write(buffer, 0, buffer.Length);
  40:     objSqlFileStream.Close();
  41:  
  42:     objSqlTran.Commit();

解决方案

See FILESTREAM MVC: Download and Upload images from SQL Server for an example showing how to upload and download filestream in an efficient, stream oriented manner. The approach you're pursuing, allocating a buffer the size of the entire uploaded file (and I assume you do the same on serving the content too) is very inefficient, your ASP process memory will be shred to pieces by such large byte[] operations.

这篇关于SQL服务器的FileStream如何填充FILESTREAM列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！