CODEWITHSUNDEEP
c#.netxmlfile
RatBoyStl
RatBoyStl

Reputation: 71

Creating files from record in database in .Net

I have a database table with the following structure:

Columns: DealID (int), CustomerID (int), DocumentArchiveID (int), DealXML (string of XML)

The issue is:

a value for the CustomerID field can appear multiple times in the table but for each time it appears the DealXML associated with it is unique. I need to connect to the table and create a file of the XML contained in the DealXML column - but only one file per CustomerID. So basically if a CustomerID appears 5 times I need to create one file with all the related DealXML data in that file... if a CustomerID appears only once then I create a single file with just that CustomerID's DealXML in it. So at the end of it all I need one file per each unique instance of CustomerID but each file should contain all the DealXML data associated with that particular CustomerID. I am at a complete loss as to how to accomplish this and I'm facing a deadline.

Summary: need to create a file for each unique instance of CustomerID (a CustomerID can appear many times in the table, each time with a distinct DealXML that needs to go into the file) - each file contains all the DealXML for that particular CustomerID.

I thought of creating a FileStream with FileMode.Append, creating a unique file for each unique CustomerID in the database but unfortunately I do not have control over what I name my files (they must adhere to a convention established outside my organization) and cannot use this method to create unique files (based on the CustomerID) which was my first idea.

I'm doing this in .Net platform using C# and a SQL Server 2008 database.

Any help? Anyone? Feedback deeply appreciated.

Upvotes: 0

Views: 583

Answers (2)

RQDQ
RQDQ

Reputation: 15579

First, you need a query that brings you back everything in the correct order:

SELECT CustomerID, DealID, DealXML FROM TABLE ORDER BY CustomerID, DealID

Next loop through the results. Every time you encounter a new CustomerID, just create a new file (using whatever conventions you need to) and write the xml (Note that you will have to wrap all of the DealXML values with a root element to create well formed xml):

<customerData>
     <DealXML />
     <DealXML />
     <DealXML />
</customerData>

EDIT: You can do something like this (this is some sketch code - but this gives you the basic idea)... The basic idea is you put the deal xmls into a collection until you move to another customer id.

int previousCustomerId = -1;

List<string> deals = new List<string>();

while(rs.ReadNext())
{
   int customerId = rs["CustomerID"];

   if (customerId != previousCustomerId)
   {
       //Don't do this on the first go
       if (customerId != -1)
       {

          //Generate a filename
          string filename = GenerateFileName(customerId);  

          //There are better ways to write multiple values to a file,
          //  but this should give you an idea of where to start.
          File.AppendText(filename, "<customerData>");

          //Dump all of the DealXML values to the file
          foreach(string deal in deals)
              File.AppendText(filename, deal);

          File.AppendText(filename, "</customerData>");
       }

       //Reinitialize the list
       deals = new List<string>();

       //Save the new customer id
       previousCustomerID = customerId;

   }

   deals.Add((string)rs["DealXML"]);
}

Upvotes: 2

Val
Val

Reputation: 2323

Well, a brute-force approach would be to query for distinct CustomerIDs. If DealXML is nullable, you should select distinct CustomerID from table X where DealXML is not null

Then, loop over the resulting dataset. In each loop, query for the DealXML for the current Customer ID. You'll get back a dataset with 1 or more rows; open a new file, loop thru the dataset, and append to the file.

You'll need to put a wrapper around each customer's XML to be well-formed...

Upvotes: 0

Related Questions