在.Net Core 2中使用SQL Server Filestream

问题描述

我需要访问存储在SQL Server中的pdf文件作为Filestream数据.我正在开发.Net Core 2(Razor页面)应用程序.

I need to access pdf files stored in a SQL Server as Filestream data. I am working on a .Net Core 2 (Razor pages) application.

我正在尝试访问此页面上概述的Filestream数据的方法: https://docs.microsoft.com/zh-CN/sql/relational-databases/blob/create-client-applications-for-filestream-data

I am trying the method to access Filestream data outlined on this page:https://docs.microsoft.com/en-us/sql/relational-databases/blob/create-client-applications-for-filestream-data

但是，SqlFileStream类型似乎在System.Data.SqlTypes的.Net Core 2版本中不可用.

However, it appears that the SqlFileStream type is not available in the .Net Core 2 version of System.Data.SqlTypes.

从SQL Server .Net Core 2访问Filestream数据的最佳方法是什么?

What is the best way to access Filestream data from SQL Server .Net Core 2?

还有一个相关的问题，是否有一种方法可以以更简化"的方式使用Linq和Entity Framework，而不必通过设置SqlCommand，SqlConnection来设置经典" SQL查询等等

And, related question, is there an approach that can make use of Linq and Entity Framework in a more "streamlined" manner, rather than having to set up a "classic" SQL query with setting SqlCommand, SqlConnection, etc.

推荐答案

.NET Core 3中的 Microsoft.Data.SqlClient .您可以尝试预览，但官方版本即将发布.

This will be available in .NET Core 3 as part of Microsoft.Data.SqlClient. You can try the preview, but the official release is right around the corner.

以前曾在这里进行过跟踪: https://github.com/dotnet/SqlClient/issues /15

It was previously tracked here: https://github.com/dotnet/SqlClient/issues/15

首先创建数据库.可以找到一个很好的例子在这里.

Start by creating your database. A good example can be found here.

安装SQLClient nuget.

Install SQLClient nuget.

Install-Package Microsoft.Data.SqlClient -Version 1.0.19249.1

请注意，不再使用System.Data.新的名称空间是Microsoft.Data.

Please note that System.Data is not used anymore. The new namespace is Microsoft.Data.

这是一个简单的应用程序(来自上述示例):

Here's an simple app (from above mentioned example):

class Program
{
    const string cs =@"Data Source=<your server>;Initial Catalog=MyFsDb;Integrated Security=TRUE";

    static void Main(string[] args)
    {
        Save();
        Open();
    }

    private  static void Save()
    {
        var path = @"C:\Files1\testfile.txt";
        FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
        BinaryReader rdr = new BinaryReader(fs);
        byte[] fileData = rdr.ReadBytes((int)fs.Length);
        rdr.Close();
        fs.Close();

        using (SqlConnection con = new SqlConnection(cs))
        {
            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 = "Some Name";
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    private static void Open()
    {
        using (SqlConnection con = new SqlConnection(cs))
        {
            con.Open();
            SqlTransaction txn = con.BeginTransaction();
            string sql = "SELECT fData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), fName FROM MyFsTable";
            SqlCommand cmd = new SqlCommand(sql, con, txn);
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                string filePath = rdr[0].ToString();
                byte[] objContext = (byte[])rdr[1];
                SqlFileStream sfs = new SqlFileStream(filePath, objContext, System.IO.FileAccess.Read);
                byte[] buffer = new byte[(int)sfs.Length];
                sfs.Read(buffer, 0, buffer.Length);
                sfs.Close();

                string fileContents = System.Text.Encoding.UTF8.GetString(buffer);
                Console.WriteLine(fileContents);
            }

            rdr.Close();
            txn.Commit();
            con.Close();

        }
    }
}

这篇关于在.Net Core 2中使用SQL Server Filestream的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！