使用c#从SQL Server数据生成XML

我在SQL Server中有一个查询，它生成XML输出。

我想用c#生成相同的结果。这可能吗?

查询是

select T1_1.HomeID as [@HomeID],
(
  select T1_2.DayID as [@ID],
  ( select T2.RndString+' '+left(T1_3.TimeValue, 5) as '*'
    from TB1 as T1_3
    inner join TB2 as T2 on T1_3.DayID = T2.DayType 
    and T1_3.TimeCode = T2.StringCode
    where T1_2.HomeID = T1_3.HomeID 
    and T1_2.DayID = T1_3.DayID
    order by T2.StringCode
    for xml path('String'), type)
    from TB1 as T1_2
    where T1_2.HomeID = T1_1.HomeID
    group by T1_2.DayID,T1_2.HomeID
    order by T1_2.DayID
    for xml path('Day'), type )
    from TB1 as T1_1
    group by T1_1.HomeID
    order by T1_1.HomeID
    for xml path('Person'), root('Persons')

有关这方面的更多细节，请参阅我之前的帖子。在SQL Server中从多个表生成XML。

我c#学得很差。算是初学者吧。这里确实需要一些帮助。

我使用的代码是…

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Windows.Forms;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    namespace SQL__
    {
        static class Program
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                // Create a String to hold the database connection string.
                // NOTE: Put in a real database connection string here or runtime won't work
                string sdwConnectionString = @"Data Source=IE1ADTBD5ZL1S';Initial Catalog=RecommendEngine;Integrated Security=True";
                // Create a connection
                SqlConnection sdwDBConnection = new SqlConnection(sdwConnectionString);
                // Open the connection
                sdwDBConnection.Open();
                // To generate XML File using C# from SQL Server Data
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand.CommandText = @"select T1_1.HomeID as [@HomeID],
                                                     (
                                                       select T1_2.DayID as [@ID],
                                                              (
                                                               select T2.RndString+' '+left(T1_3.TimeValue, 5) as '*'
                                                               from TB1 as T1_3
                                                                 inner join TB2 as T2
                                                                   on T1_3.DayID = T2.DayType and
                                                                      T1_3.TimeCode = T2.StringCode
                                                               where T1_2.HomeID = T1_3.HomeID and
                                                                     T1_2.DayID = T1_3.DayID
                                                               order by T2.StringCode
                                                               for xml path('String'), type
                                                              )
                                                       from TB1 as T1_2
                                                       where T1_2.HomeID = T1_1.HomeID
                                                       group by T1_2.DayID,
                                                                T1_2.HomeID
                                                       order by T1_2.DayID
                                                       for xml path('Day'), type
                                                      )
                                                     from TB1 as T1_1
                                                     group by T1_1.HomeID
                                                     order by T1_1.HomeID
                                                     for xml path('Person'), root('Persons')";
                    da.SelectCommand.Connection = new SqlConnection("sdwDBConnection");
                    string xml = "";
                    using (DataSet ds = new DataSet())
                    {
                        da.SelectCommand.Connection.Open();
                        da.Fill(ds);
                        da.SelectCommand.Connection.Close();
                        if (ds != null && ds.Tables.Count > 0)
                            xml = ds.GetXml();
                    }
                }
                // Close the connection
                sdwDBConnection.Close();

            }
        }
    }

SqlCommand cmd = new SqlCommand("Your Command", new SqlConnection("Connection String"));
DataTable dt = new DataTable();
new SqlDataAdapter(cmd).Fill(dt);
dt.TableName = "Your Table Name";
dt.WriteXml("File Address");
dt.WriteXmlSchema("Schema File Address");

using (SqlDataAdapter da = new SqlDataAdapter())
{
    da.SelectCommand.CommandText = "you sql command";
    da.SelectCommand.Connection = new SqlConnection("your connection");
    string xml = "";
    using (DataSet ds = new DataSet())
    {
        da.SelectCommand.Connection.Open();
        da.Fill(ds);
        da.SelectCommand.Connection.Close();
        if(ds != null && ds.Tables.Count > 0)    
           xml = ds.GetXml();
    }
}