Hello everyone I am trying to create and save XML with values from SQL database. My code basically creats an XML string filled with the variables that I get from several SQL querys (I need to fetch data from many tables to complete the XML) I am using SqlReader to fetch values for my variables. Since my XML will be kind of long and complicated, I decided to put the final XML strings from a few sub-strings according to the queries, basically like:
finalxml string = startxml + headerxml + buyerxml + sellerxml + linesxml + summaryxml
I was all OK till I reached a point, where an SQL query gave more than one rows as a result.
Here is my codebehind:
string orderdetails = "select ProductId, Quantity from [Order] inner join OrderItem on [Order].Id=OrderItem.OrderId where OrderId='" + orderID + "'";
SqlCommand com3 = new SqlCommand(orderdetails, dbConn);
dbConn.Open();
using (SqlDataReader orderitem = com3.ExecuteReader())
while (orderitem.Read())
{
string orderxml = "<Order>" + "<OrderItem>" + orderitem.GetInt32(0).ToString() + "</OrderItem>" + "<OrderItem>" + orderitem.GetInt32(1).ToString() + "</OrderItem>" + "</Order>";
Label20.Text = orderxml;
string termid = orderitem.GetInt32(0).ToString();
string termmenny = orderitem.GetInt32(1).ToString();
}
dbConn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
string headerxml = "<Order-Header>" +
"<DocumentType> számla </DocumentType>" +
"<OrderNumber>" + rendSz + "</OrderNumber>" +
"<OrderDate>"+ datum +"</OrderDate>" +
"<ExpectedDeliverydate> " + szallido + "</ExpectedDeliverydate>"+
"<PaymentMethod>"+ fizmod +"</PaymentMethod>"+
"<Remarks><![CDATA[" + txtFirstName.Text + "]]></Remarks>"+
"<Note><![CDATA[" + txtLastName + "]]></Note>" +
"<PreviousOrderNumber><![CDATA[]]></PreviousOrderNumber>" +
"</Order-Header>";
string vevoxml = "<Buyer>" +
"<ILN>435</ILN>" +
"<Name>"+ vezeteknev +" "+ keresztnev +"</Name>" +
"<City>" + vevovaros + "</City>" +
"<ZIP>" + irszam + "</ZIP>" +
"<Address>" + vevocim1 + vevocim2 + "</Address>" +
"<E-mail>" + vevoemail + "</E-mail>" +
"<Telefon>" + vevotel + "</Telefon>" +
"<Contact>" + "nincs" + "</Contact>" +
"<DeliveryName>" + szallkeresztnev +" "+ szallvezeteknev + "</DeliveryName>" +
"<DeliveryCity>" + szallcimvaros + "</DeliveryCity>" +
"<DeliveryZIP>" + szallcimirszam + "</DeliveryZIP>" +
"<DeliveryAddress>" + szallvevocim1 + szallvevocim2 + "</DeliveryAddress>" +
"</Buyer>";
string eladoxml = "<Seller>" +
"<ILN />" +
"<Name>E-Szoftver Kft.</Name>" +
"<City>Budapest</City>" +
"<ZIP>1195</ZIP>" +
"<Address>Ady Endre út 97-99. F/04.</Address>" +
"<E-mail>laszlo@marsalsoft.hu</E-mail>" +
"</Seller>";
string linexml = "<Line>" +
"<Line-Item>" +
"<LineNumber>1</LineNumber>" +
"<EAN />" +
"<SupplierItemCode />" +
"<CustomsCode>5829</CustomsCode>" +
"<ItemDescription><![CDATA[E-Számla szoftver frissítési és jogkövetési díj 1 évre]]></ItemDescription>" +
"<ItemNote><![CDATA[]]></ItemNote>" +
"<VATType>27</VATType>" +
"<PackageType>CU</PackageType>" +
"<OrderedQuantity>1</OrderedQuantity>" +
"<UnitOfMeasure>év</UnitOfMeasure>" +
"<OrderedUnitNetPrice>13200</OrderedUnitNetPrice>" +
"</Line-Item>" +
"</Line>";
string strMyXml = "<?xml version=\"1.0\" encoding=\"windows-1250\"?> " +
"<Document-Order>"+
headerxml +
"<Order-Parties>" +
vevoxml +
eladoxml +
"</Order-Parties>"+
"<Order-Lines>" +
linexml +
"</Order-Lines>" +
"<Order-Summary>" +
"<TotalLines>1</TotalLines>" +
"<TotalOrderedAmount>1</TotalOrderedAmount>" +
"<TotalNetPrice>13200</TotalNetPrice>" +
"<TotalVat>3564</TotalVat>" +
"<TotalGross>16764</TotalGross>" +
"</Order-Summary>" +
"</Document-Order>";
XmlDocument xDoc = new XmlDocument();
xDoc.LoadXml(strMyXml);
xDoc.Save(Server.MapPath("//orders//szamla.xml"));
Now the lineXML part is my problem, it describes the products, that is included in the order. every xml describes 1 order, but 1 order can contain 3 products, and in that case I need to create a line for every product with a few elements: number, quantity, net price, gross price.
Is it possible with sqlreader and variable added to the string, or do I need to use some other way, like dataset generated from the query results? I found a lot of info about generatng xml from dataset, and generating xml with string, but none of them contained info about this particular situation, generating xml from SQL where you need to use multiple queries. Luckily my code is modular, so if it can be done with dataset only, i don't need to touch the other parts of the xml hopefully. Any help would be appreciated.
Aucun commentaire:
Enregistrer un commentaire