Linq到SQL截断字符串,由存储过程返回[英] Linq to sql truncating string returned by Stored Procedure

本文是小编为大家收集整理的关于Linq到SQL截断字符串,由存储过程返回的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我以前问过这个问题.但是我无法得到任何答案.可能我还不清楚.让我提供更多细节.

我有一个sp,它返回一个长字符串.这是DBML文件代码

[Function(Name="dbo.spX")]
public ISingleResult<spXResult> spX([Parameter(DbType="VarChar(8000)")] string str)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), str);
    return ((ISingleResult<spXResult>)(result.ReturnValue));
}

这是SPXResult类

public partial class spXResult
{
    private string _XML_F52E2B61_18A1_11d1_B105_00805F49916B;

    public spXResult()
    {  }

    [Column(Name="[XML_F52E2B61-18A1-11d1-B105-00805F49916B]", 
     Storage="_XML_F52E2B61_18A1_11d1_B105_00805F49916B", 
     DbType="NText", UpdateCheck=UpdateCheck.Never)]
    public string XML_F52E2B61_18A1_11d1_B105_00805F49916B
    {
        get
        {
            return this._XML_F52E2B61_18A1_11d1_B105_00805F49916B;
        }
        set
        {
            if ((this._XML_F52E2B61_18A1_11d1_B105_00805F49916B != value))
            {
                this._XML_F52E2B61_18A1_11d1_B105_00805F49916B = value;
            }
         }
     }
}

这是我的代码

ISingleResult<spXResult> result = ctx.spX("1234");

string returnStr = result.First().XML_F52E2B61_18A1_11d1_B105_00805F49916B;

一切都很好,当结果不是一个长字符串时,但是一旦SP返回一个很长的字符串,它就会截断结果.我不知道为什么..有人可以帮忙.

谢谢

推荐答案

我唯一能发现的鱼话中唯一可以发现的东西 - 在声明中,您:

public ISingleResult<spXResult> spX([Parameter(DbType="VarChar(8000)")] string str)

(dbtype = varchar(8000)) - 是ansi(non -unicode),但是在"列声明"中,您使用ntext-首先,这是unicode(每个字符2个字节),以及为什么ntext?上方您有Varchar?

[Column(Name="[XML_F52E2B61-18A1-11d1-B105-00805F49916B]", 
     Storage="_XML_F52E2B61_18A1_11d1_B105_00805F49916B", 
     DbType="NText", UpdateCheck=UpdateCheck.Never)]

这似乎有点奇怪....

您可以在两个地方尝试使其相同吗?例如. Varchar(8000)在两种情况下??

marc

其他推荐答案

linqtosql将XML结果设置为块,因此您需要运行这样的循环:

ISingleResult<spXResult> result = ctx.spX("1234");
string xml = "";
foreach (var x in result)
    xml += x.XML_F52E2B61_18A1_11d1_B105_00805F49916B;

或使用linq:

string xml = result.Aggregate("", (current, x) => current + x.XML_F52E2B61_18A1_11d1_B105_00805F49916B);

其他推荐答案

只需从

更改您的SP
SELECT ...
  FROM MyTable
  FOR XML AUTO

to

DECLARE @ResultXML xml

SET @ResultXML = 
 (SELECT ...
  FROM MyTable
  FOR XML AUTO)

SELECT @ResultXML as [MyXML]

和重新创建LINQ方法

本文地址:https://www.itbaoku.cn/post/1557092.html

问题描述

I have asked this question before. but i was not able to get any answer. may be i wasnt very clear. let me give some more details.

I have a SP which returns a long string. here is dbml file code

[Function(Name="dbo.spX")]
public ISingleResult<spXResult> spX([Parameter(DbType="VarChar(8000)")] string str)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), str);
    return ((ISingleResult<spXResult>)(result.ReturnValue));
}

and here is spXResult class

public partial class spXResult
{
    private string _XML_F52E2B61_18A1_11d1_B105_00805F49916B;

    public spXResult()
    {  }

    [Column(Name="[XML_F52E2B61-18A1-11d1-B105-00805F49916B]", 
     Storage="_XML_F52E2B61_18A1_11d1_B105_00805F49916B", 
     DbType="NText", UpdateCheck=UpdateCheck.Never)]
    public string XML_F52E2B61_18A1_11d1_B105_00805F49916B
    {
        get
        {
            return this._XML_F52E2B61_18A1_11d1_B105_00805F49916B;
        }
        set
        {
            if ((this._XML_F52E2B61_18A1_11d1_B105_00805F49916B != value))
            {
                this._XML_F52E2B61_18A1_11d1_B105_00805F49916B = value;
            }
         }
     }
}

and here is my code

ISingleResult<spXResult> result = ctx.spX("1234");

string returnStr = result.First().XML_F52E2B61_18A1_11d1_B105_00805F49916B;

everything is fine, when the result is not a long string, but as soon as the sp returns a very long string, it truncates the result. i have no clue why.. can someone please help.

thanks

推荐答案

The only thing fishy I can spot is this - here in the declaration, you hvae:

public ISingleResult<spXResult> spX([Parameter(DbType="VarChar(8000)")] string str)

(DbType=VARCHAR(8000)) - which is ANSI (non-Unicode), but then in the column declaration you use NTEXT - first of all, that's UNICODE (2-byte per character), and why NTEXT?? Above you have VARCHAR?

[Column(Name="[XML_F52E2B61-18A1-11d1-B105-00805F49916B]", 
     Storage="_XML_F52E2B61_18A1_11d1_B105_00805F49916B", 
     DbType="NText", UpdateCheck=UpdateCheck.Never)]

That seems a bit odd.......

Can you try to make it the same in both places? E.g. VARCHAR(8000) in both cases??

Marc

其他推荐答案

LinqToSql splits the XML result set into chunks, so you need to run a loop like this:

ISingleResult<spXResult> result = ctx.spX("1234");
string xml = "";
foreach (var x in result)
    xml += x.XML_F52E2B61_18A1_11d1_B105_00805F49916B;

Or using LINQ:

string xml = result.Aggregate("", (current, x) => current + x.XML_F52E2B61_18A1_11d1_B105_00805F49916B);

其他推荐答案

Just change your sp from

SELECT ...
  FROM MyTable
  FOR XML AUTO

to

DECLARE @ResultXML xml

SET @ResultXML = 
 (SELECT ...
  FROM MyTable
  FOR XML AUTO)

SELECT @ResultXML as [MyXML]

and recreate linq method