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);
}
}
}