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.EventArgs e)
{
string sDataSourcePath = "C:\\CB\\CRData\\";

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

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

OleDbDataAdapter da1 = new OleDbDataAdapter();
da1.SelectCommand = new OleDbCommand( sSelect, conn);
DataTable dtTransactions = new DataTable( "Transactions" );

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

// 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.CommandText = "INDEX ON idxTrnsType ON
dtTransactions(trnstype)";
// cmdAlterTable.ExecuteCommand();
cmdAlterTable.CommandText = "INDEX ON idxTrnsDate ON
dtTransactions(trnsdate)";
// cmdAlterTable.ExecuteCommand();
cmdAlterTable.CommandText = "INDEX ON idxTblNam ON
dtTransactions(tblnam)";
// cmdAlterTable.ExecuteCommand();

// Load second table - A/R Invoices
sSelect = "SELECT * FROM ZARINV.DBF " +
"WHERE (Upper(tbl2nam) LIKE 'NAT%')";
OleDbDataAdapter da2 = new OleDbDataAdapter();
da2.SelectCommand = new OleDbCommand( sSelect, conn);
DataTable dtInvoices = new DataTable( "Invoices" );
DataColumn [] dcaPrimaryKeyInvoices = new DataColumn[3];
dcaPrimaryKeyInvoices[0] = dtInvoices.Columns[ "arshort" ];
dcaPrimaryKeyInvoices[1] = dtInvoices.Columns[ "arunqid" ];
dcaPrimaryKeyInvoices[2] = dtInvoices.Columns[ "invunqid" ];
dtInvoices.PrimaryKey = dcaPrimaryKeyInvoices;
// (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.Tables.Add( dtTransactions );
dsDataSet.Tables.Add( dtInvoices );
// repeat to add other tables ... dsDataSet.Tables.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.DBF";
string sDestFile = sDataSourcePath + "QACCOUNT.DBF";
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.ExecuteNonQuery();
string sInsert = "INSERT INTO QACCOUNT SELECT arshort, arname, arunqid FROM
ZACCOUNT";
OleDbCommand cmdInsert = new OleDbCommand( sInsert, conn);
cmdInsert.ExecuteNonQuery();

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.DBF";
string sDestFile = sDataSourcePath + "QACCOUNT.DBF";
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.ExecuteNonQuery();