Générer un document XML à l'aide de C# à partir de Données SQL Server

J'ai une requête dans SQL Server qui génère une Sortie au format XML.

Je veux produire le même résultat à l'aide de C#. Est-il possible??

La requête est

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')

Pour de plus amples détails à ce sujet se référer à mon précédent post. La production de XML à partir de Plusieurs Tables dans SQL Server.

Je suis extrêmement pauvre en C#. Un débutant de toutes sortes. Avez besoin d'un peu d'aide ici.

Le code que j'ai utilisé est...

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

OriginalL'auteur SarangArd | 2013-05-27