0

一段使用很久的程式今天忽然出錯，追查原因，是一段古早寫的FOR XML查詢出了問題。

程式用SqlCommand執行"SELECT ... FROM ... FOR XML RAW('Boo’)”，最近因資料異動，結果傳回超長的字串，透過SqlDataReader讀取時，只得到最前端的2,033個字元，XML內容被不正常截斷。

微軟有一篇KB310378 The XML data row is truncated at 2,033 characters when you use the SqlDataReader object 描述了這個問題，並建議改用ExecuteXmlReader()。不過，使用XmlReader()時，是採ReadOuterXml()一次讀入一個XmlElement.OuterXml的方式，需要一個while迴圈將結果串接起來才是完整結果。

覺得用XmlReader有點麻煩，所以找到另一個解法: 將FOR XML的結果CONVERT(NVARCHAR(MAX), ...)，就可以用一般SqlDataReader()將結果完整讀取出來。

以下是程式範例: (我用了”請SQL從0數到2047”的技巧來摸擬一個大型的FOR XML查詢結果)

using System;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
 
namespace ConsoleApplication1
{
    class Program
    {
        static string cnStr = "Data Source=dbsvr;User Id=user;Password=pass";
 
        static void Main(string[] args)
        {
            using (var cn = new SqlConnection(cnStr))
            {
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText =
@"select distinct number as No
from master.dbo.spt_values
where name is null
for xml raw('Item')";
                SqlDataReader dr = cmd.ExecuteReader();
                dr.Read();
                string s1 = dr[0].ToString();
                dr.Close();
                //只會傳回前2,033個字元
                  Console.WriteLine(s1.Length);
                //只到133，且被異常截斷於<Item No="133"/
                Console.WriteLine(Tail(s1, 100));
 
                XmlReader xdr = cmd.ExecuteXmlReader();
 
                xdr.Read();
                StringBuilder sb = new StringBuilder();
                while (xdr.ReadState != ReadState.EndOfFile)
                    sb.Append(xdr.ReadOuterXml());
                xdr.Close();
                string s2 = sb.ToString();
                //傳回35,754個字元
                Console.WriteLine(s2.Length);
                //傳回完整結果，到<Item No="2047" />
                Console.WriteLine(Tail(s2, 100));
                //解決方案2: 加上convert(nvarchar(max), ...)
                cmd.CommandText = @"
select convert(nvarchar(max), (
    select distinct number as No
    from master.dbo.spt_values
    where name is null
    for xml raw('Item')
))";
                dr = cmd.ExecuteReader();
                dr.Read();
                string s3 = dr[0].ToString();
                //傳回完整結果: 33,706個字元
                   // ..." /> vs ..."/>
                //結尾少了一個空白，所以35,754-2,048=33,706
                Console.WriteLine(s3.Length);
                //傳回完整結果，到<Item No="2047"/>
                Console.WriteLine(Tail(s3, 100));
 
                cn.Close();
            }
            Console.Read();
        }
        //取s字串最後c個字元
   private static string Tail(string s, int c)
        {
            c = Math.Min(s.Length, c);
            return s.Substring(s.Length - c, c);
        }
    }
}

執行結果:

2033
27"/><Item No="128"/><Item No="129"/><Item No="130"/><Item No="131"/><Item No="1
32"/><Item No="133"/
35754
="2042" /><Item No="2043" /><Item No="2044" /><Item No="2045" /><Item No="2046"
/><Item No="2047" />
33706
tem No="2042"/><Item No="2043"/><Item No="2044"/><Item No="2045"/><Item No="2046
"/><Item No="2047"/>


Comments

Be the first to post a comment

Post a comment