ADO.NET如果返回XML格式的数据比较好?

Question

如果用
SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=1;database=Northwind;");
SqlCommand cmd = new SqlCommand("SELECT EmployeeID,LastName FROM Employees for xml auto", conn);
conn.Open();
string s = cmd.ExecuteScalar().ToString();
conn.Close();
我句的效率可能会比较差,因为返回的字符串里包含了好多重复的架构,如果SQL服务器和WEB服务器分离,这个是很不利的.
请教大家如何构建XML格式数据

Answer 1

先将数据读入DataSet，再将DataSet写入XML文件或流就不会有那么多重复的架构Schema=========================
www.webdiyer.com

Answer 2

如果我想转化成字符串,然后写入浏览器,下面的代码效率如何? private DataTable getdata1()
{
return SqlHelper.ExecuteDataset("server=.;uid=sa;pwd=1;database=Northwind;", CommandType.Text, "select * from Employees").Tables[0];
} /// <summary>
/// 功能说明：将DataTable手工转换成XML字符串
/// </summary>
/// <param name="dataTable">传入的DataTable对象</param>
/// <returns>XML字符串</returns>
public static string DataTableToXmlString(DataTable dataTable)
{
StringBuilder stringBuilder = new StringBuilder();
foreach(DataRow dataRow in dataTable.Rows)
{
stringBuilder.AppendFormat ("<{0}>\n", HttpUtility.HtmlEncode(dataTable.TableName));
stringBuilder.Append(DataRowToXmlString(dataRow));
stringBuilder.AppendFormat ("</{0}>\n", HttpUtility.HtmlEncode(dataTable.TableName));
}
return stringBuilder.ToString();
}
/// <summary>
/// 功能说明：将一个DataRow转换成Xml字符串
/// </summary>
/// <param name="dataTable">传入的DataRow对象</param>
/// <returns>XML字符串</returns>
private static string DataRowToXmlString(DataRow dataRow)
{
StringBuilder stringBuilder = new StringBuilder();
for(int i = 0; i<= dataRow.Table.Columns.Count - 1;i++)
{
if (dataRow[i].ToString() == string.Empty)
{
stringBuilder.AppendFormat("<{0}/>\n",System.Web.HttpUtility.HtmlEncode(dataRow.Table.Columns[i].ColumnName));
}
else
{
stringBuilder.AppendFormat("<{0}>{1}</{0}>\n",HttpUtility.HtmlEncode(dataRow.Table.Columns[i].ColumnName),HttpUtility.HtmlEncode(dataRow[i].ToString()));
}
}
return stringBuilder.ToString();
}

Answer 3

private void Page_Load(object sender, System.EventArgs e)
{
//ServerTran();
Client();
} private void ServerTran()
{
string s = DataTableToXmlString(getdata1());
Response.Write(TransformXmlToHtml(makexml(s), HttpContext.Current.Server.MapPath("default.xslt")));
} /// <summary>
/// 将XML字符串和XSL文件转化为html字符串
/// </summary>
/// <param name="xmlStr">XML字符串</param>
/// <param name="xslFile">XSL文件</param>
/// <returns>HTML String</returns>
/// <res> jChen 2001-10-10 *****           </res>
public static string TransformXmlToHtml(string XmlString, string XslFile)
{
StringReader xml = new StringReader(XmlString);
XPathDocument xmlDoc = new XPathDocument(xml);
XslTransform xsl = new XslTransform();
xsl.Load(XslFile);
StringWriter html = new StringWriter();
xsl.Transform(xmlDoc,null,html);
return html.ToString();
} private string makexml(string s)
{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n");
sb.Append("<?xml-stylesheet type=\"text/xsl\" href=\"default.xslt\"?>\r\n");
sb.Append("<root>\r\n");
sb.Append(s);
sb.Append("</root>\r\n");
return sb.ToString();
} private void Client()
{
Response.ContentType = "text/xml";
string s = DataTableToXmlString(getdata1());
Response.Write(makexml(s));
} private DataTable getdata1()
{
return SqlHelper.ExecuteDataset("server=.;uid=sa;pwd=123123;database=Northwind;", CommandType.Text, "select * from Employees").Tables[0];
} /// <summary>
/// 功能说明：将DataTable手工转换成XML字符串
/// </summary>
/// <param name="dataTable">传入的DataTable对象</param>
/// <returns>XML字符串</returns>
public static string DataTableToXmlString(DataTable dataTable)
{
StringBuilder stringBuilder = new StringBuilder();
foreach(DataRow dataRow in dataTable.Rows)
{
stringBuilder.AppendFormat ("<{0}>\n", HttpUtility.HtmlEncode(dataTable.TableName));
stringBuilder.Append(DataRowToXmlString(dataRow));
stringBuilder.AppendFormat ("</{0}>\n", HttpUtility.HtmlEncode(dataTable.TableName));
}
return stringBuilder.ToString();
}
/// <summary>
/// 功能说明：将一个DataRow转换成Xml字符串
/// </summary>
/// <param name="dataTable">传入的DataRow对象</param>
/// <returns>XML字符串</returns>
private static string DataRowToXmlString(DataRow dataRow)
{
StringBuilder stringBuilder = new StringBuilder();
for(int i = 0; i<= dataRow.Table.Columns.Count - 1;i++)
{
if (dataRow[i].ToString() == string.Empty)
{
stringBuilder.AppendFormat("<{0}/>\n",System.Web.HttpUtility.HtmlEncode(dataRow.Table.Columns[i].ColumnName));
}
else
{
stringBuilder.AppendFormat("<{0}>{1}</{0}>\n",HttpUtility.HtmlEncode(dataRow.Table.Columns[i].ColumnName),HttpUtility.HtmlEncode(dataRow[i].ToString()));
}
}
return stringBuilder.ToString();
}