处理SQL Server之间非常大的字符串和.NET code + LINQ

问题描述

我有一个应用程序需要处理SQL Server数据库和.NET code之间非常大的字符串。我有一个LINQ查询它们保存时生成的字符串到的数据库，但试图创建一个从字符串在的数据库，应用程序崩溃，因为一个OutOfMemoryException弦尺寸

I have an app that needs to handle very large strings between a SQL Server database and .NET code. I have a LINQ query that generates the strings when saving them to the database, but when trying to create the strings from the database, the app crashes with an OutOfMemoryException because of the size of the strings.

我必须做一些事情，使LINQ产生code避免这种情况？使用某种COM pression可能是一种选择，但希望避免性能的原因。

Do I have to do something to make the LINQ generated code avoid that? Using some kind of compression might be an option, but would like to avoid that for performance reasons.

推荐答案

你叫什么非常大？什么是字符串？ CLOB？ BLOB？ XML？

What do you call "very large"? And what is the string? CLOB? BLOB? xml?

我怀疑你应该使用像的ExecuteReader（），这（通过的IDataReader ）公开的方法进行阅读这样的列块：

I suspect you should be using things like ExecuteReader(), which (via IDataReader) exposes methods for reading such columns in chunks:

        using (var reader = cmd.ExecuteReader(
            CommandBehavior.SequentialAccess)) {
            char[] buffer = new char[8040]; // or some multiple (sql server page size)
            while (reader.Read()) {
                long dataOffset = 0, read;
                while((read = reader.GetChars(colIndex, dataOffset, buffer, 0, buffer.Length)) > 0) {
                    // process "read"-many chars from "buffer"
                    dataOffset += read;
                }
            }
        }

显然与XML，你可能需要一个的XmlReader 通过 cmd.ExecuteXmlReader（）。

更新重新LINQ评论（现已删除）：

Updated re LINQ comment (now deleted):

要使用的IDataReader 直接从LINQ到SQL，我希望最接近你可以得到的是 ctx.GetCommand（），通过它查询。这样，你会使用的ExecuteReader 或的ExecuteXmlReader 如上。我不知道很多关于EF ...

To use IDataReader directly from LINQ-to-SQL, I expect the closest you can get is ctx.GetCommand(), passing it a query. You would then use ExecuteReader or ExecuteXmlReader as above. I don't know much about EF...

如果你给的查询失败类型的一个例子，可能有一些技巧可能 - 例如，如果你要过滤或选择XML的子集，有些东西你可以在SQL / XML做的 - 也许在通过LINQ到SQL称为UDF。

If you give an example of the type of query that is failing, there might be some tricks possible - for example, if you are filtering or selecting subsets of the xml, there are things you can do in SQL/XML - perhaps in a UDF called via LINQ-to-SQL.

这篇关于处理SQL Server之间非常大的字符串和.NET code + LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！