Import multiple .xml files into a DataSet

So, here's what I want to do:

I'd like to import all the xml files from a folder (lets say C:\Bla\AllMyLittleXmlFiles) into a DataSet, do stuff to it and from there export it to SQL Server. Is that possible? It seems that the DataSet cleans itself out after every successful file read, leaving only the first file's data.

Here's my code, including some unnecessary stuff:

        StringBuilder fileNames = new StringBuilder();
        ArrayList filePaths = new ArrayList();
        FolderBrowserDialog folder = new FolderBrowserDialog();
        folder.ShowDialog();
        int pathLength = folder.SelectedPath.Length;
        foreach (string file in Directory.EnumerateFiles(folder.SelectedPath))
        {
            string fielname = file.ToString().Substring(pathLength + 1);
            string filepath = file.ToString();
            fileNames.AppendLine(fielname);
            filePaths.Add(filepath);
        }
       // textBox1.Text = filePaths[0].ToString();

        DataSet aDS = new DataSet();
        StringBuilder uh = new StringBuilder();
        int filesImported = 0;
        foreach (object ob in filePaths)
        {
            string test = ob.ToString();
            uh.Append(test);
           aDS.ReadXml(ob.ToString());
           filesImported++;

        }
        int tablesimported = 0;
        foreach (DataTable table in aDS.Tables)
        {
            dataGridView1.DataSource = table.DefaultView;
            tablesimported++;

        }
        MessageBox.Show("Files Imported:" + filesImported.ToString() + "    Tables Imported : " + tablesimported.ToString());
        textBox1.Text = uh.ToString();

EDIT After trying some answers I'm left with this :

        int filesImported = 0;
        foreach (object ob in filePaths)
        {
            dsCollection[filesImported].ReadXml(ob.ToString());
            filesImported++;
        }
        int tablesImported = 0;
        foreach (DataSet ds in dsCollection)
        {
            foreach (DataTable table in ds.Tables)
            {
                mainDS.Merge(table);
                tablesImported++;
            }
        }

then I call the Merge method on the dsCollection. On开发者_StackOverflow社区ly thing is is that the datasets in dscollection are never instantiated so ... back to square 2.

---

Maybe you could create main dataset and after reading xml to temp dataset try to Merge these datasets like this:

mainDataSet.Merge(tempDataSet);

---

this will solve ur problem, though not sure if it's efficient...

DataSet[] aDS = new DataSet[filePaths.Count]; 
StringBuilder uh = new StringBuilder();
int filesImported = 0;
foreach (object ob in filePaths)
{
string test = ob.ToString();
uh.Append(test);
//every xml file gets its own dataset
//so that new read operation will not clear data
aDS[filesImported].ReadXml(ob.ToString()); 
filesImported++;

}
int tablesimported = 0;
foreach (DataSet ds in aDS)
{
foreach (DataTable table in ds.Tables)
{
dataGridView1.DataSource = table.DefaultView;
tablesimported++;

}
}

---

Try this:

        foreach (object ob in filePaths)
        {
            string test = ob.ToString();
            uh.Append(test);
            DataSet tmpDS = new DataSet();
            tmpDS.ReadXml(ob.ToString());
            aDS.Merge(tmpDS);
            filesImported++;
        }

This will leave aDS with a DataSet Table for each file, which seems to be what you're looking for.

EDIT: [With apologies to @Reniuz, who pointed you in exactly the same direction, but 20 minutes earlier!]

---

My bloated and inefficient solution.... I am disappoint.

Thanks to Niko and Reniuz for pointing me in the right direction.

    private ArrayList GetFilePaths()
    {
        ArrayList filePaths = new ArrayList();
        FolderBrowserDialog folder = new FolderBrowserDialog();
        folder.ShowDialog();

        foreach (string filePath in Directory.EnumerateFiles(folder.SelectedPath))
        {
            filePaths.Add(filePath.ToString());
        }
        return filePaths;
    }

    private void ImportXmls(ArrayList filePaths)
    {
        DataSet[] tempDSCollection = new DataSet[filePaths.Count];
        int impFiles = 0;
        foreach (object ob in filePaths)
        {
            DataSet impDS = new DataSet();
            impDS.ReadXml(ob.ToString());

            tempDSCollection[impFiles] = impDS;
            impFiles++;
        }

        foreach (DataSet aDS in tempDSCollection)
        {
            foreach (DataTable table in aDS.Tables)
            {
                mainDS.Merge(table);
            }
        } 
    }

I'll keep working on it and update but it'll have to do for now

---

try this:

DataSet ds = new Dataset();

for(int x=0;x<Filepath.Count;x++)
{
    ds.ReadXml(Filepath[x].ToString());
}