How to create multiple DataTables with one sql query?

Question

I'm creating an application that loads data from SQL Database once a day and saves it into a text file. The main table is a "Transactions" table, which holds data about all transactions made on that day. One of the columns represents a middle-man call sign. My program saves the data in a DataTable first and then with a StringBuilder I give it the proper form and finally save it into a text file with StreamWriter.

My question is, how or on which stage of the process can I distinguish one table entry from another. I want to create two files: one with transactions made by middle-man A and B. This is my code so far:

// Query for Data

row = new SqlDataAdapter("SELECT [MSISDN], [Amount], [Transaction_ID], POS.[Name], MNO.[Call Sign] FROM"
                     + "[Transactions] join [POS] "
                     + "on Transactions.POS_ID = POS.idPOS "
                     + "join [MNO] on Transactions.MNO_ID = MNO.idMNO "
                     + "where [Status] = '1'", con);
row.Fill(Row);

// Save Data in StringBuilder

for (int i = 0; i < Row.Rows.Count; i++)
{
      sb.Append(Row.Rows[i].ItemArray[0].ToString()).Append(",");
      double amount = Convert.ToDouble(Row.Rows[i].ItemArray[1].ToString());
      sb.Append(Math.Round(amount, 2).ToString().Replace(",", ".")).Append(",");
      sb.Append(Row.Rows[i].ItemArray[2].ToString()).Append(",");
      sb.Append(Row.Rows[i].ItemArray[3].ToString()).Append(",");
    sb.Append(Row.Rows[i].ItemArray[4].ToString()).Append(",").Append(Environment.NewLine);
}


// Create a file from StringBuilder
mydocpath = @"C:\Transactions\" + fileDate.ToString(format) + ".txt";
FileStream fsOverwrite = new FileStream(mydocpath, FileMode.Create);
using (StreamWriter outfile = new StreamWriter(fsOverwrite))
{
       outfile.WriteAsync(sb.ToString());
}

Hope I was clear enough. English isn't my strong side. As well as coding for what it seems...

You need some sample data. Is the MSISDN the "middle man" unique identifier? — granadaCoder, Apr 16 '13 at 13:13

score 0 · Answer 1 · edited May 23 '17 at 12:18

One option.

Put all your data into a DataSet. And then do Xsl transformations against the ds.GetXml(). Here is kind of an example:

http://granadacoder.wordpress.com/2007/05/15/xml-to-xml-conversion/

But what I would do is eliminate the DataTable altogether. Use an IDataReader. Loop over the data. Maybe do the original query as "Order By Middle-Man-Identifer", and then when the middleManIdentifer "makes a jump", close the previous file and write a new one. Something like that.

You may be able to learn something from this demo:

http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

Here is a couple of IDataReader helpers:

http://kalit-codesnippetsofnettechnology.blogspot.com/2009/05/write-textfile-from-sqldatareader.html

and

How to efficiently write to file from SQL datareader in c#?

How to create multiple DataTables with one sql query?

1 Answers1