Creating new database tables and indexes from existing tables

[Shelby]

Problem: My company generates its own data export from a propietary
database. These (free) tables can be read in C#.NET using a Visual FoxPro
driver (vfpoledb). I can read each of the six tables into its own datatable,
modify them, and add them to a dataset. It take approximately 15 minutes to
pass that dataset to Crystal Reports (45 minutes if the report uses three
subreport datasets). Then it takes over 7 hours for Crystal to generate the
report. Ugh!

My first attempt to speed things up was to generate an external index file
for each table. Even after extensive internet searches and reading, I could
not get past the syntax errors and "Exclusive use" errors; I've tried adding
"Mode=ReadWrite " and "Exclusive= On" to the connection string with no effect.
(I still think the indexing would be beneficial, and would appreciate any
help with this).

The current attempt involves combining similar columns from two tables into
other tables for a total of four tables in the dataset, plus a total re-write
of the report to use this new dataset. This is not a trivial report re-write.

To avoid this dataset cluge and the report re-write: I would like to load
the tables using SELECT commands to limit the data loaded, add indexing, and
write the tables back to disk with the same names in a new location (I cannot
alter the existing tables because that data is used for numerous reports).
My hope is that the report will only have to change the location of where it
gets its tables, and that it will not have to be re-written. Can anyone tell
me if this is a valid approach, or if there is a better way? I have included
the code below:

private void crvReport_Load( object sender, System.EventArg s e)
{
string sDataSourcePath = "C:\\CB\\CRData \\";

// ***** Get data from database (OleDb) *****
string sConnectionStri ng = "Provider=VFPOL EDB.1;DSN=CB Data32;"
+ "DataSource =" + sDataSourcePath + ";";
OleDbConnection conn = new OleDbConnection ( sConnectionStri ng );
conn.Open();

// Load the first table - A/R Transactions
string sSelect = "SELECT * FROM ZARTRAN.DBF " +
"WHERE trnstype IN ('CreditAdjustm ent', 'Charge') " +
"AND (Upper(tblnam) LIKE '%BILLING%')";

OleDbDataAdapte r da1 = new OleDbDataAdapte r();
da1.SelectComma nd = new OleDbCommand( sSelect, conn);
DataTable dtTransactions = new DataTable( "Transactio ns" );

// Add primary key (needed to create an index)
DataColumn [] dcaPrimaryKeyTr ansactions = new DataColumn[3];
dcaPrimaryKeyTr ansactions[0] = dtTransactions. Columns[ "arshort" ];
dcaPrimaryKeyTr ansactions[1] = dtTransactions. Columns[ "arunqid" ];
dcaPrimaryKeyTr ansactions[2] = dtTransactions. Columns[ "invptr" ];
dtTransactions. PrimaryKey = dcaPrimaryKeyTr ansactions;

// Fill the datatable
da1.Fill( dtTransactions );

// ??? attempt to add an index, but could not get it to work
// ??? using "CREATE INDEX" did not work either
OleDbCommand cmdAlterTable = new OleDbCommand();
cmdAlterTable.C ommandText = "INDEX ON idxTrnsType ON
dtTransactions( trnstype)";
// cmdAlterTable.E xecuteCommand() ;
cmdAlterTable.C ommandText = "INDEX ON idxTrnsDate ON
dtTransactions( trnsdate)";
// cmdAlterTable.E xecuteCommand() ;
cmdAlterTable.C ommandText = "INDEX ON idxTblNam ON
dtTransactions( tblnam)";
// cmdAlterTable.E xecuteCommand() ;

// Load second table - A/R Invoices
sSelect = "SELECT * FROM ZARINV.DBF " +
"WHERE (Upper(tbl2nam) LIKE 'NAT%')";
OleDbDataAdapte r da2 = new OleDbDataAdapte r();
da2.SelectComma nd = new OleDbCommand( sSelect, conn);
DataTable dtInvoices = new DataTable( "Invoices" );
DataColumn [] dcaPrimaryKeyIn voices = new DataColumn[3];
dcaPrimaryKeyIn voices[0] = dtInvoices.Colu mns[ "arshort" ];
dcaPrimaryKeyIn voices[1] = dtInvoices.Colu mns[ "arunqid" ];
dcaPrimaryKeyIn voices[2] = dtInvoices.Colu mns[ "invunqid" ];
dtInvoices.Prim aryKey = dcaPrimaryKeyIn voices;
// (add index goes here)
da2.Fill( dtInvoices );

// Repeat above steps above for remaining tables

// Create the dataset
DataSet dsDataSet = new DataSet();

// Add the tables to the dataset
dsDataSet.Table s.Add( dtTransactions );
dsDataSet.Table s.Add( dtInvoices );
// repeat to add other tables ... dsDataSet.Table s.Add( ... );

conn.Close();
// ***** end reading OleDb *****

// Write back to disk (currrently passing the dataset to Crystal)
// ???

// Launch Crystal Reports
}

I appreciate any help or suggestions. Thanks!

[Shelby]

This is just a follow up post in case anyone else has the same problem.

There does not seem to be a way to add external indexes to these old-style
FoxPro tables. But we were still able to cut the entire report process down
to 15 minutes by creating new tables that were just a subset of the original
tables, and re-writing the Crystal Report to remove the subreport sections!

Here's an example of one table:
string sSourceFile = sDataSourcePath + "ZACCOUNT.D BF";
string sDestFile = sDataSourcePath + "QACCOUNT.D BF";
if ( true == File.Exists( sDestFile )) File.Delete( sDestFile );
string sCreate = "CREATE DBF QACCOUNT FREE (arshort c(16), arname c(30),
arunqid c(10))";
OleDbCommand cmdCreate = new OleDbCommand( sCreate, conn);
cmdCreate.Execu teNonQuery();
string sInsert = "INSERT INTO QACCOUNT SELECT arshort, arname, arunqid FROM
ZACCOUNT";
OleDbCommand cmdInsert = new OleDbCommand( sInsert, conn);
cmdInsert.Execu teNonQuery();

The new table (QACCOUNT) is created with only the columns needed. The
INSERT command takes only those columns from the source table and puts them
into the new table.

An alternative way to do this is to use C#'s File.Copy command, then remove
the unwanted columns from the new table:
string sSourceFile = sDataSourcePath + "ZACCOUNT.D BF";
string sDestFile = sDataSourcePath + "QACCOUNT.D BF";
File.Copy( sSourceFile, sDestFile, true );
if ( ConnectionState .Closed == conn.State ) conn.Open();
string sDropColumn = "ALTER TABLE QACCOUNT DROP COLUMN arnum";
OleDbCommand cmdAlter = new OleDbCommand( sDropColumn, conn );
cmdAlter.Execut eNonQuery();