WinForms 应用程序设计——将文档从 SQL Server 移动到文件存储

2021-09-27 00:00:00 sql-server winforms

我有一个连接到 SQL Server 的标准 WinForms 应用程序.该应用程序允许用户使用图像列将当前存储在数据库中的文档上传到表格中.

I have a standard WinForms application that connects to a SQL Server. The application allows users to upload documents which are currently stored in the database, in a table using an image column.

我需要更改此方法,以便将文档存储为文件,并将指向该文件的链接存储在数据库表中.

I need to change this approach so the documents are stored as files and a link to the file is stored in the database table.

使用当前的方法 - 当用户上传文档时,他们不受存储方式的影响,因为他们连接到数据库,他们不需要知道关于文件存储位置的任何信息,没有特殊的目录权限等是必要的.如果我为文档设置网络共享,我希望避免任何 IT 问题,例如用户必须有权访问此目录才能上传或访问现有文档.

Using the current approach - when the user uploads a document they are shielded from how this is stored, as they have a connection to the database they do not need to know anything about where the files are stored, no special directory permissions etc are required. If I set up a network share for the documents I want to avoid any IT issues such as the users having to have access to this directory to upload to or access existing documents.

有哪些选项可以做到这一点?我想有一个临时数据库,以与当前方法相同的方式将文档上传到其中,然后在服务器上运行一个进程以将这些文件保存到文件存储中.然后可以删除并重新创建该数据库以回收任何空间.有没有更好的方法?

What are the options available to do this? I thought of having a temporary database where the documents are uploaded to in the same way as the current approach and then a process running on the server to save these to the file store. This database could then be deleted and recreated to reclaim any space. Are there any better approaches?

附加信息:我的应用程序没有网络服务器元素,所以我认为 WCF 服务是不可能的

ADDITIONAL INFO: There is no web server element to my application so I do not think a WCF service is possible

推荐答案

您是否有理由首先将文件从数据库中取出?

Is there a reason why you want to get the files out of the database in the first place?

如何仍然将它们保存在 SQL Server 中,但使用 FILESTREAM 列 而不是 IMAGE?

How about still saving them in SQL Server, but using a FILESTREAM column instead of IMAGE?

引自链接:

FILESTREAM 使基于 SQL Server 的应用程序能够存储非结构化的文件系统上的数据,例如文档和图像.应用可以利用丰富的流 API 和文件的性能同时保持系统之间的事务一致性非结构化数据和相应的结构化数据.

FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.

FILESTREAM 将 SQL Server 数据库引擎与 NTFS 文件集成系统通过将 varbinary(max) 二进制大对象 (BLOB) 数据存储为文件系统上的文件.Transact-SQL 语句可以插入、更新、查询、搜索和备份 FILESTREAM 数据.Win32 文件系统接口提供对数据的流式访问.

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM 使用 NT 系统缓存来缓存文件数据.这有助于减少 FILESTREAM 数据可能对数据库引擎产生的任何影响表现.不使用 SQL Server 缓冲池;因此,这内存可用于查询处理.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

这样你就可以两全其美:
文件将作为文件存储在硬盘上(可能比将它们存储在数据库中更快),但您不必关心文件共享、权限等.

So you would get the best out of both worlds:
The files would be stored as files on the hard disk (probabl faster compared to storing them in the database), but you don't have to care about file shares, permissions etc.

请注意,您至少需要 SQL Server 2008 才能使用 FILESTREAM.

Note that you need at least SQL Server 2008 to use FILESTREAM.

相关文章