General Help with table required Please

[rdrunner400]

Hi all,

I have a SQL Server Table called Sample_Table which contains 70,000 records which are separated in 100 different areas by a field called District

What i have been trying to do is to extract into a separate table called Sample_Data_Table this i have to work as below one of each of the districts.


SqlDataAdapter da = new SqlDataAdapter("SELECT Distinct District FROM Sample_Table ORDER by District", cs);

// Create the Data Table
DataTable Sample_Data_Table = new DataTable();

// Fill Data Table with Records
da.Fill(Sample_Data_Table);


What i then want to do is to somehow loop around this Sample_Data_Table and then to extract out into 100 different tables / files all of the records that match the relevant field called district.

foreach (DataRow row in Sample_Data_Table.Rows)
{
SqlDataAdapter OutPut_Table = new SqlDataAdapter("SELECT * FROM Sample_Table WHERE Sample_Table.DISTRICT = Sample_Data_Table.Rows", cs);

// Create the Data Table
DataTable Export_Table = new DataTable();

// Fill Data Table with Records
OutPut_Table.Fill(Export_Table);

// when i get to this stage i need to be able to copy the table to a fox pro free standing table
// any help with this command string would also be welcome as i have not done this before
}


Currently this is not quite working and i am at a loss as to how to get it to work

thank you in advance

rdrunner40

 

[ralphtrent]

If I undertand you correctly, you want to create one DataTable, in memory, for all the records that match your distinct districts? Is that correct?

If so the sql in your loop is incorrect, it should look like this

foreach (DataRow row in Sample_Data_Table.Rows) 
{
     SqlDataAdapter OutPut_Table = new SqlDataAdapter("SELECT * FROM Sample_Table WHERE Sample_Table.DISTRICT = '" + Convert.ToString(rows["DISTRICT"])+"'", cs);
.
.
.
}

Thats assuming the DISTRICT datatype is a string. But I have to ask, why do you want to do this? Do you need to create a separate SQL table for each district type?

 

[rdrunner400]

Hi

Yes what i have to do is to create 100 Print Files as the job is to to be sent to a laser printer for printing hence why i need all the seperate files.

The District type is a string and consists of the letters BA1, BA2, BA3 and so on ....

Once i have all the data that applies to the BA1 district ( there is approxiametly around 85 fields .... i then need to be able to do some special sorting and updating of this table so i would like to do this as well..

As this job is being printed 2up on a A4 Sheet of paper i have to do a spilt sort on this OutPut Table as well...... so assuming there is 1000 records in district BA1 i have to have the table in the following print order .....

Record No 1
Record No 501
Record No 2
Record No 502

This is meaning that i have to have a unique record NUmber before i do the sort part to generate the output file in this particular format.

Then once i have done all of that then i want to send the file to a folder with a name like BA1_N as a Visual Fox Pro Stand alone table if possible.

Is this making sense ?

regards

Rdrunner40

 

[ralphtrent]

based on your original post, I was under the impression that you were unable to split the data by district, is that still the case?

 

[rdrunner400]

Hi

I have pasted in the answer that you forwarded to me in your first reply and i now are able to split the Main Table into the various districts. That is so easy when I have seen what you forwarded to me to use .....

The data adapter that i have created called Export_Table am i able to do any updating of it in this state ? ie adding a unique number to each record ?

I thank you for that answer so quickly ....

Are you able to assist with the next part of what i am trying to do with the sorting of the file and then copying out to a fox table ? I am totally new to c# yet i am a competent visual fox pro programmer and this is all new here.

Regards,

rdrunner40

 

[ralphtrent]

Glad to hear that helped.

Now to confirm you want to add another "column" to your eport table? If so then will need to do something like this.

DataTable Export_Table = new DataTable();
Export_Table.Columns.Add("YourNewColumnName", typeof(Int32));

/// fill your data table  

/// now you will need to loop through each
/// record to value your new column 
int uniqueInt =0;
foreach(DataRow dr in Export_Table.Rows())
{
     dr["YourNewColumnName"] = uniqueInt;
     uniqueInt++;
}

I am not familiar with fox pro so I cannot offer much help there but I'd you need more assistance with the above code let me know.

 

[rdrunner400]

Hi

That is exactlty what i am wanting to do is to add another field to the table.

At this point i would then like to add text into this field in each of the output tables "sample text"

Then i would like to either export this table out as a FoxPro or DBase table or if not like that then as pipe delimited export file called "District" what ever the value of the district is for the particular District that is being processed.

Boy this is so different to fox but at least i am learning at the same time

Regards

rdrunner40

 

[ralphtrent]

If what your really need is a file of the data for each district, I would do the following

This will create one file for each district. Each row will be separated by a |.

foreach(DataRow dr in Sample_Data_Table.Rows())
{
string district = Convert.ToString(dr["District"]);
using(StreamWriter swNewFile = new StreamWriter(@"c:\temp\"+district+".txt"))
{
/// dr.ItemArray will creat an array of each column in the current row 
foreach(object o in dr.ItemArray())
{
swNewFile.Write(o + "|")
}
/// now you can append your additional data
swNewFile.WriteLine("custom data");
swNewLine.Flush();
}
}

I am typing this from my phone so of something does not compile correctly don't let me know.

 

[rdrunner400]

Hi,

I will try tommorrow what you have sent to me today I was unwell.

Thank you for your help

Regards

rdrunner40

 

[ralphtrent]

sounds good, feel better. Let me know how it goes.

 

[ralphtrent]

Hey rdrunner400, Just checking in to see how this went.

 

[rdrunner400]

Hi,

I am going to be attacking this over the weekend ... so will let you know as i am working all weekend.

Currently i am working on another project that is taking all my time

Regards

rdrunner400

 

[phinoppix]

Hi, just to give a slightly different opinion. From what I gather, you basically want to:
1) Get records per district
2) Perform some updating
3) Sort table
4) Transform sorted table to DBF

For the printing part, I assume your report generator is bound to the DBF.

I'm not sure what sort of updating you need to do, but normally when preparing reports, using computed columns most of the time will suffice. Of course, it depends on the complexity of what you need to update.
But, is it not possible to use SQL computed columns? For example,

select 
    upper([Field1]) AS [A],
    case 
      when [Field2] 
        then [Field3] 
        else [Field4] 
    end AS [B],
    Field5
    from Sample_table
    where District = @district;

Then sort the table based on the column(s) you need in SQL. If you have SQL 2005, you can utilize the row_number() function to generate the row index and common table expression (CTE) to return a custom view.

with t(A, B, C) AS (
  select 
    upper([Field1]) AS [A],
    case 
      when [Field2] 
        then [Field3] 
        else [Field4] 
    end AS [B],
    Field5
    from Sample_table
    where District = @district
)
select row_number() over(group by A, C) as rowIndex, A, B, C
  from t;

Then using an SqlDataAdapter, get the table and transform it to whatever data format, as already suggested.

So far, from this point of view, I don't see the need to create that many dataTables.

In addition, if you want to sort the table based on this manner (1, 501, 2, 502, 3, 503...), you can do this:
1) Make sure the RowIndex column to decimal type. (from SQL, just cast the RowIndex to decimal)
2) Starting on the next index from the middle record, update the value by adding the iterator and 0.5

int midpoint = tbl.Rows.Count / 2;
for(int i = midpoint + 1; i < tbl.Rows.Count; i++)
{
  tbl.Rows[i]["RowIndex"] = (i + 1.5);  // need to add 1 because iterator is zero-based.
}

Hope this helps. If not, can be a useful C# reference