C# Output XMLWriter to XML file. Select SQL and save to XML

Mam taki kod:

private void button5_Click(object sender, EventArgs e)
        {

            string sConnectionString;
            sConnectionString = "Data Source=SERWER;Initial Catalog=baza;Persist Security Info=True;User ID=user; Password=pass;";
            SqlConnection objConn = new SqlConnection(sConnectionString);
            objConn.Open();


            SqlDataAdapter faktura = new SqlDataAdapter("SELECT  [t0].[Faktura] as 'InvoiceNumber', [t0].[FATrN_Data2] as 'Date', [t0].[FATrN_Data3] as 'InvoiceIssueDate',  DATEADD(DD, 90, [t0].[FATrN_Data3]) as 'InvoiceDueDate', '90' as 'PaymentTerms', 'I' as 'PaymentTermsReferenceDate' FROM [CDN].[INEcodFANaglo] AS [t0] WHERE ([t0].[TrN_GIDNumer] = '1015871') AND ([t0].[TrN_GIDTyp] = '2033')", objConn);

            SqlDataAdapter paymentmethod1 = new SqlDataAdapter("SELECT  'P' as 'CODE', 'Przelew' as 'Description' FROM [CDN].[INEcodFANaglo] AS [t0] WHERE ([t0].[TrN_GIDNumer] = '1015871') AND ([t0].[TrN_GIDTyp] = '2033')", objConn);


            DataSet ds = new DataSet("Invoice");
            faktura.Fill(ds, "InvoiceHeader");

            MemoryStream stream = new MemoryStream();

            ds.WriteXml(@"c:\test1.xml");
            stream.Position = 0;
            XDocument doc = XDocument.Load(@"c:\test1.xml");

            XElement invoice = doc.Element("Invoice");

            DataTable dt = new DataTable("PaymentMethod");
            paymentmethod1.Fill(dt);

            stream = new MemoryStream();
            dt.WriteXml(@"c:\test1.xml");
            stream.Position = 0;
            XElement paymentMethod = XElement.Load(@"c:\test1.xml");

            invoice.Add(paymentMethod);
        }
    }
}

Który generuje mi plik XML:

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <PaymentMethod>
    <CODE>P</CODE>
    <Description>Przelew</Description>
  </PaymentMethod>
</DocumentElement>

Niestety nie jest w pliku XML uwzględniany zapis

  DataSet ds = new DataSet("Invoice");
`        **    faktura.Fill(ds, "InvoiceHeader");**``
`
            MemoryStream stream = new MemoryStream();

            ds.WriteXml(@"c:\test1.xml");
            stream.Position = 0;
            XDocument doc = XDocument.Load(@"c:\test1.xml");

Co robię nie tak?

Dlaczego InvoiceHeader to DataSet a PaymentMethod to DataTable?

Ja nie znam odpowiedzi, ale nie łatwiej wygenerować tego xmla po stronie bazy? Nie bardzo widzę sens tego 2 zapytania skoro wszystko pobierasz na sztywno...

Panczo napisał(a):

Ja nie znam odpowiedzi, ale nie łatwiej wygenerować tego xmla po stronie bazy? Nie bardzo widzę sens tego 2 zapytania skoro wszystko pobierasz na sztywno...

A jak później to przekazać do programu?

Nie wiem jak ma wygladac ten XML ale jeżeli dobrze czytam to chcesz uzyskać coś takiego:

<Invoice>
	<InvoiceHeader>
		<InvoiceNumber>FV/18/02/001</InvoiceNumber>
		<Date>2018-02-14T01:00:00</Date>
		<InvoiceIssueDate>2018-02-28T01:00:00</InvoiceIssueDate>
		<InvoiceDueDate>2018-05-29T01:00:00</InvoiceDueDate>
		<PaymentTerms>90</PaymentTerms>
		<PaymentTermsReferenceDate>I</PaymentTermsReferenceDate>
	</InvoiceHeader>
	<PaymentMethod>
		<CODE>P</CODE>
		<Description>Przelew</Description>
	</PaymentMethod>
</Invoice

Do tego użyj zapytania:

SELECT  
	[t0].[Faktura] as 'InvoiceHeader/InvoiceNumber'
	, [t0].[FATrN_Data2] as 'InvoiceHeader/Date'
	, [t0].[FATrN_Data3] as 'InvoiceHeader/InvoiceIssueDate'
	,  DATEADD(DD, 90, [t0].[FATrN_Data3]) as 'InvoiceHeader/InvoiceDueDate'
	, '90' as 'InvoiceHeader/PaymentTerms'
	, 'I' as 'InvoiceHeader/PaymentTermsReferenceDate' 
	, 'P' as 'PaymentMethod/CODE'
	, 'Przelew' as 'PaymentMethod/Description' 
FROM 
	[INEcodFANaglo] AS [t0] 
FOR 
	XML PATH('Invoice')

http://sqlfiddle.com/#!18/a416c/5

Do odebrania z bazy ExecuteXmlReader https://msdn.microsoft.com/pl-pl/library/system.data.sqlclient.sqlcommand.executexmlreader(v=vs.110).aspx?f=255&MSPPError=-2147217396&cs-save-lang=1&cs-lang=csharp#code-snippet-3

Panczo napisał(a):

Nie wiem jak ma wygladac ten XML ale jeżeli dobrze czytam to chcesz uzyskać coś takiego:

<Invoice>
	<InvoiceHeader>
		<InvoiceNumber>FV/18/02/001</InvoiceNumber>
		<Date>2018-02-14T01:00:00</Date>
		<InvoiceIssueDate>2018-02-28T01:00:00</InvoiceIssueDate>
		<InvoiceDueDate>2018-05-29T01:00:00</InvoiceDueDate>
		<PaymentTerms>90</PaymentTerms>
		<PaymentTermsReferenceDate>I</PaymentTermsReferenceDate>
	</InvoiceHeader>
	<PaymentMethod>
		<CODE>P</CODE>
		<Description>Przelew</Description>
	</PaymentMethod>
</Invoice

Do tego użyj zapytania:

SELECT  
	[t0].[Faktura] as 'InvoiceHeader/InvoiceNumber'
	, [t0].[FATrN_Data2] as 'InvoiceHeader/Date'
	, [t0].[FATrN_Data3] as 'InvoiceHeader/InvoiceIssueDate'
	,  DATEADD(DD, 90, [t0].[FATrN_Data3]) as 'InvoiceHeader/InvoiceDueDate'
	, '90' as 'InvoiceHeader/PaymentTerms'
	, 'I' as 'InvoiceHeader/PaymentTermsReferenceDate' 
	, 'P' as 'PaymentMethod/CODE'
	, 'Przelew' as 'PaymentMethod/Description' 
FROM 
	[INEcodFANaglo] AS [t0] 
FOR 
	XML PATH('Invoice')

http://sqlfiddle.com/#!18/a416c/5

Do odebrania z bazy ExecuteXmlReader https://msdn.microsoft.com/pl-pl/library/system.data.sqlclient.sqlcommand.executexmlreader(v=vs.110).aspx?f=255&MSPPError=-2147217396&cs-save-lang=1&cs-lang=csharp#code-snippet-3

Zrobiłem tak. Plik się tworzy, ale niestety jest pusty. Po stronie serwera zapytanie SQL się wykonuje poprawnie.

 private void button1_Click(object sender, EventArgs e)
        {
           



            SqlConnection conn = new SqlConnection("Data Source=SERWER;Initial Catalog=baza;Persist Security Info=True;User ID=user; Password=haslo;");
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM [tabela].[CDN].[kody] where twk_id = '88' FOR XML PATH('Invoice')", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            string path = @"c:\testowy.xml";
            var writer = new StreamWriter(path);
            while (reader.Read())
            {
                Console.SetOut(writer);
                Console.WriteLine(reader.GetString(0));

            }

            reader.Close();
            conn.Close();
        }




    }
    }

Nie używaj executereader on ucina zwracanego XML-a, zrób jak pisałem, użyj ExecuteXMLReader

Panczo napisał(a):

Nie używaj executereader on ucina zwracanego XML-a, zrób jak pisałem, użyj ExecuteXMLReader

Zrobiłem to w inny sposób i działa. Ale co jeśli chciałbym skorzystać z kilku innych tabel które nie są ze sobą połączone?

Np. chcę jeszcze skorzystać z select xxxx form a1 where zzz=5 i select yyy from a55 where cos=20 and xx=100

teraz pomiędzy tymi tabelami nie ma pół łączących a mimo to chciałbym je umieścić w strukturze XML.

Tak wygląda działający kod:

   ```csharp
 string query = "SELECT [t0].[Faktura] AS 'InvoiceHeader/InvoiceNumber' , [t0].[FATrN_Data2] AS 'InvoiceHeader/Date' , [t0].[FATrN_Data3] AS 'InvoiceHeader/InvoiceIssueDate' , DATEADD(DD, 90, [t0].[FATrN_Data3]) AS 'InvoiceHeader/InvoiceDueDate' , '90' AS 'InvoiceHeader/PaymentTerms' , 'I' AS 'InvoiceHeader/PaymentTermsReferenceDate' , 'P' AS 'PaymentMethod/CODE' , 'Przelew' AS 'PaymentMethod/Description' FROM [xx].[tabela] AS [t0] where ([t0].[TrN_GIDNumer] = '1017177') AND ([t0].[TrN_GIDTyp] = '2033') FOR XML PATH('Invoice')";

        using (SqlConnection _con = new SqlConnection("Data Source=SERWER;Initial Catalog=baza;Persist Security Info=True;User ID=user; Password=haslo;"))
        using (SqlCommand _cmd = new SqlCommand(query, _con))
        {
            _con.Open();
            string result = _cmd.ExecuteScalar().ToString();
            _con.Close();

            File.WriteAllText(@"C:\test.xml", result);
        }

Piszesz niezbyt precyzyjnie, jak to ma być zagnieżdżone w strukturze XML

Jeżeli to ma być pojedynczy węzeł to np. tak:

SELECT 
	[t0].[Faktura] AS 'InvoiceHeader/InvoiceNumber' 
	, [t0].[FATrN_Data2] AS 'InvoiceHeader/Date' 
	, [t0].[FATrN_Data3] AS 'InvoiceHeader/InvoiceIssueDate' 
	, DATEADD(DD, 90, [t0].[FATrN_Data3]) AS 'InvoiceHeader/InvoiceDueDate' 
	, '90' AS 'InvoiceHeader/PaymentTerms' 
	, 'I' AS 'InvoiceHeader/PaymentTermsReferenceDate' 
	, 'P' AS 'PaymentMethod/CODE' 
	, 'Przelew' AS 'PaymentMethod/Description' 
        , (select xxxx form a1 where zzz=5) as 'PojedynczyWezelZTabelia1'
FROM 
	[xx].[tabela] AS [t0] 
where 
	[t0].[TrN_GIDNumer] = '1017177'
	AND ([t0].[TrN_GIDTyp] = '2033'
FOR XML PATH('Invoice')

Natomiast jeżeli to ma być np. węzeł a1 z dziecmi xxxx, to tak:

SELECT 
	[t0].[Faktura] AS 'InvoiceHeader/InvoiceNumber' 
	, [t0].[FATrN_Data2] AS 'InvoiceHeader/Date' 
	, [t0].[FATrN_Data3] AS 'InvoiceHeader/InvoiceIssueDate' 
	, DATEADD(DD, 90, [t0].[FATrN_Data3]) AS 'InvoiceHeader/InvoiceDueDate' 
	, '90' AS 'InvoiceHeader/PaymentTerms' 
	, 'I' AS 'InvoiceHeader/PaymentTermsReferenceDate' 
	, 'P' AS 'PaymentMethod/CODE' 
	, 'Przelew' AS 'PaymentMethod/Description' 
        , (select xxxx AS 'xxxx'  form a1 where zzz=5  FOR XML PATH(''), TYPE) as 'a1'
FROM 
	[xx].[tabela] AS [t0] 
where 
	[t0].[TrN_GIDNumer] = '1017177'
	AND ([t0].[TrN_GIDTyp] = '2033'
FOR XML PATH('Invoice')