Efficient way to export database tables to XML file in c#.net

Question

I have a sqlite database which has 7 tables. I need to export them to an XML file in c#.net. When doing so, i have used 7 loops nested to access the data of 7 Datatables to maintain the dependencies of columns. but while i run it, my machine becomes very slow. it generates XML file, but the generated XML changes after some time. it seems, in every iteration , it generates one XML and replace it by new one. my codes are following :

public XMLSerializer(String filepath,ROOT root)
            {               


                XmlDocument doc = new XmlDocument();
                XmlNode rootNode, aNode, pNode, eNode, mNode, plNode;

                // Create a procesing instruction.
                XmlProcessingInstruction newPI;


                String PItext = "type='text/xsl' href='root.xsl'";
                newPI = doc.CreateProcessingInstruction("xml-stylesheet", PItext);

                // Add the processing instruction node to the document.
                doc.AppendChild(newPI);

                rootNode = doc.CreateElement("root");
                doc.AppendChild(rootNode);    


                plNode = doc.CreateElement("pl");
                rootNode.AppendChild(plNode);

                eNode = doc.CreateElement("e");
                rootNode.AppendChild(eNode);

                mNode = doc.CreateElement("m");
                rootNode.AppendChild(mNode);

                aNode = doc.CreateElement("a");
                rootNode.AppendChild(aNode);                 


                DataTable plDT = root.Getpl();

                foreach (DataRow pRow in plDT.Rows)
                {
                    XmlNode plNode = doc.CreateElement("p");

                    XmlNode pIDNode = doc.CreateElement("id");
                    XmlNode pteNode = doc.CreateElement("te");                    
                    XmlNode fNode = doc.CreateElement("fs");

                    plNode.AppendChild(pIDNode);
                    plNode.AppendChild(pteNode);                    
                    plNode.AppendChild(fNode);

                    plNode.AppendChild(plNode);

                    pIDNode.InnerText = pRow["id"].ToString();
                    pteNode.InnerText = root.Getpte(pRow["id"].ToString());

                    DataTable fmdt = root.GetFml(pRow["id"].ToString());
                    foreach (DataRow fmRow in fmdt.Rows)
                    {
                        XmlNode fsNode = doc.CreateElement("fm");

                        XmlNode fmIDNode = doc.CreateElement("id");
                        XmlNode fmpNode = doc.CreateElement("p");

                        XmlNode eNode = doc.CreateElement("e");                    

                        fsNode.AppendChild(fmIDNode);
                        fsNode.AppendChild(fmpNode);

                        fsNode.AppendChild(eNode);                           

                        plNode.AppendChild(fsNode);


                        fmIDNode.InnerText = fmRow["id"].ToString();
                        fmpNode.InnerText = pRow["id"].ToString();                      


                        DataTable eDT = root.Gete();

                        foreach (DataRow eRow in eDT.Rows)
                        {
                            XmlNode eNode = doc.CreateElement("e");

                            XmlNode eIDNode = doc.CreateElement("id");
                            XmlNode eDanNode = doc.CreateElement("dan");                            

                            XmlNode mNode = doc.CreateElement("m");    

                            eNode.AppendChild(eIDNode);
                            eNode.AppendChild(edanNode);                           

                            //eNode.AppendChild(eNode);
                            fsNode.AppendChild(eNode);

                            eIDNode.InnerText = eRow["id"].ToString();
                            edanNode.InnerText = root.Getedan(eRow["id"].ToString());                           


                            DataTable mDT = root.Getm();

                            foreach (DataRow mRow in mDT.Rows)
                            {
                                XmlNode mNode = doc.CreateElement("m");

                                XmlNode mIDNode = doc.CreateElement("id");
                                XmlNode meNode = doc.CreateElement("e");


                                mNode.AppendChild(mIDNode);
                                mNode.AppendChild(meNode);                                

                                eNode.AppendChild(mNode);

                                mIDNode.InnerText = mRow["id"].ToString();
                                meNode.InnerText = root.Getme(mRow["id"].ToString());                                

                                DataTable aDT = root.Geta();

                                foreach (DataRow aRow in aDT.Rows)
                                {
                                    XmlNode aNode = doc.CreateElement("a");

                                    XmlNode AssIDNode = doc.CreateElement("id");
                                    XmlNode AssNaNode = doc.CreateElement("na");

                                    XmlNode pinNode = doc.CreateElement("pin");

                                    aNode.AppendChild(AssIDNode);
                                    aNode.AppendChild(AssNaNode);

                                    aNode.AppendChild(pinNode);

                                    AssIDNode.InnerText = aRow["id"].ToString();
                                    AssNaNode.InnerText = aRow["na"].ToString();

                                    mNode.AppendChild(aNode);

                                    DataTable piDT = root.Getp(aRow["id"].ToString());

                                    foreach (DataRow piRow in piDT.Rows)
                                    {
                                        XmlNode pInNode = doc.CreateElement("pIn");

                                        XmlNode pInIDNode = doc.CreateElement("id");

                                        XmlNode pInaNode = doc.CreateElement("a");
                                        XmlNode pInpNode = doc.CreateElement("p");
                                        XmlNode pInFminNode = doc.CreateElement("fmin");

                                        pInNode.AppendChild(pInIDNode);                                       
                                        pInNode.AppendChild(pInaNode);
                                        pInNode.AppendChild(pInpNode);
                                        pInNode.AppendChild(pInFminNode);

                                        aNode.AppendChild(pInNode);       


                                        pInIDNode.InnerText = piRow["id"].ToString();                                       
                                        pInaNode.InnerText = aRow["id"].ToString();
                                        pInpNode.InnerText = pRow["id"].ToString();

                                        DataTable fmiDT = root.GetFmin(piRow["id"].ToString());
                                        foreach (DataRow fmiRow in fmiDT.Rows)
                                        {
                                            XmlNode fmInNode = doc.CreateElement("fmIn");

                                            XmlNode fmInIDNode = doc.CreateElement("id");
                                            XmlNode fmInpInNode = doc.CreateElement("pIn");
                                            XmlNode fmInFmNode = doc.CreateElement("fm");
                                            XmlNode fmIneNode = doc.CreateElement("e");
                                            XmlNode fmInmNode = doc.CreateElement("m");                                            

                                            fmInNode.AppendChild(fmInIDNode);
                                            fmInNode.AppendChild(fmInpInNode);
                                            fmInNode.AppendChild(fmInFmNode);
                                            fmInNode.AppendChild(fmIneNode);
                                            fmInNode.AppendChild(fmInmNode);                                            

                                            pInNode.AppendChild(fmInNode);

                                            fmInIDNode.InnerText = fmiRow["id"].ToString();
                                            fmInpInNode.InnerText = piRow["id"].ToString();
                                            fmInFmNode.InnerText = fmRow["id"].ToString();
                                            fmIneNode.InnerText = eRow["id"].ToString();
                                            fmInmNode.InnerText = mRow["id"].ToString();   


                                        }        

                                    }        

                                }
                            }
                        }             
                    }                       

              }

            doc.Save(filepath); 

            }       


        }       

    }

i think my way of coding is not efficient. could anyone please show me any better approach of accessing the datatables ?

http://stackoverflow.com/questions/5259759/how-can-i-convert-a-datatable-to-an-xml-file-in-c ? — har07, Feb 04 '14 at 08:40
if you notice the code, i have used 7 loops nested. is it normal to use 7 loops ? there is dependency among the columns of different table. to maintain that dependency, i used 7 loops. do you think there is any better way to access the data of datatable ? — rzmuc, Feb 04 '14 at 10:34
is it usual to use 7 nested foreach loops as i described above ? — rzmuc, Feb 05 '14 at 09:42
It is unusual I think, but that could happen if the requirement you have is as unusual (each row in a table f.e. `plDT` have one related table `fmdt`??) — har07, Feb 05 '14 at 10:17
actually, the "id" of plDT is equal to "p" of fmdt. if i don't put fmdt nested into plDT, i can't access "id" of plDT. similar situation in other tables. that's why, i have nested all the loops, so that i can access the dependant columns of other tables. is there any other better way to handle this situation ? — rzmuc, Feb 05 '14 at 10:36

score 0 · Answer 1 · answered Feb 04 '14 at 08:38

0

you can simply use DataTable.WriteXml() :

YourDataTable.WriteXml(strfilepath);

or

YourDataSet.WrteXml(strfilepath);

answered Feb 04 '14 at 08:38

Mohammad Arshad Alam

9,694
6
38
61

you mean, in the last line ? YourDataSet.WrteXml(strfilepath); instead of doc.Save(filepath); ? actually, if you notice the code, i have used 7 loops nested. is it efficient ?do you think there is any better way to access the data of datatable ? – rzmuc Feb 04 '14 at 10:31
How you are getting data from the database? If you are getting it into a `dataset` or a `datatable` then you can just serialize it with `WriteXml()` – Mohammad Arshad Alam Feb 04 '14 at 11:42
actually, i am a new c# programmer :) anyway,if you notice the codes, i am accessing the data from the database using methods like pteNode.InnerText = root.Getpte(pRow["id"].ToString()); there is another file from where i am getting the data and putting them in datatable. now i will try to use WriteXml() method in the inner most loop. but what about the 7 loops ? is it usual to use 7 nested loops ? – rzmuc Feb 04 '14 at 11:51
i used WriteXml() method instead of doc.Save(filepath); in the last line code. but, it is not saving the file. – rzmuc Feb 04 '14 at 13:14
Take the whole data in datatable, and write it in one shot. – Mohammad Arshad Alam Feb 04 '14 at 14:56
could you please explain what do you mean by one shot ? as i said i have used 7 nested loops. so i have 7 Datatable plDT, fmdt, eDT, mDT,aDT, piDT, fmiDT. i have put piDT.WriteXml(filepath); while all loops are finished, but it is not saving the file :( – rzmuc Feb 04 '14 at 15:11
is it usual to use 7 nested loops ? – rzmuc Feb 04 '14 at 16:32

Efficient way to export database tables to XML file in c#.net

1 Answers1