join DBF files
-
i have a temporary folder which contains DBF files, it might be 1 dbf file up too 40 dbf files the structure of these DBF file are the same, i wrote a small app with C# to read these files one by one and add it to a main DBF file so i can run a report from one big DBF file. but this is a very slow solution. this is a small example just adding two fields TYPE,EMPLOYEE DirectoryInfo TMP_ReportDirectory = new DirectoryInfo(Application.StartupPath+"\\TMP"); FileInfo [] DbfFiles = TMP_ReportDirectory.GetFiles("*.dbf"); if (DbfFiles.Length != 0) { string ITMConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath+ "\\TMP;Extended Properties=dBase IV"; OleDbConnection ITMConnection = new OleDbConnection(ITMConnString); string ITMSelectQuery = "Select TYPE,EMPLOYEE from " + DbfFiles[iloop].ToString(); //MessageBox.Show(ITMSelectQuery); OleDbCommand ITMCommand = new OleDbCommand(ITMSelectQuery,ITMConnection); ITMConnection.Open(); OleDbDataReader ITMReader = ITMCommand.ExecuteReader(); string STOCKConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath+ ";Extended Properties=dBase IV"; OleDbConnection STOCKConnection = new OleDbConnection(STOCKConnString); while (ITMReader.Read()) { frmwait.statusBar1.Text = " Loading . . . . . . ."; string newID = ITMReader.GetValue(0).ToString(); string newLONGNAME = ITMReader.GetValue(1).ToString(); string STOCKInsertQuery = "INSERT INTO GNDITEM.DBF (TYPE, EMPLOYEE) VALUES (" + newID +",\"" + newLONGNAME + "\")"; //MessageBox.Show(STOCKInsertQuery); OleDbCommand STOCKInsertCommand = new OleDbCommand(STOCKInsertQuery,STOCKConnection); STOCKConnection.Open(); STOCKInsertCommand.ExecuteNonQuery(); STOCKConnection.Close(); } ITMConnection.Close(); ITMReader.Close(); STOCKConnection.Close(); }
-
i have a temporary folder which contains DBF files, it might be 1 dbf file up too 40 dbf files the structure of these DBF file are the same, i wrote a small app with C# to read these files one by one and add it to a main DBF file so i can run a report from one big DBF file. but this is a very slow solution. this is a small example just adding two fields TYPE,EMPLOYEE DirectoryInfo TMP_ReportDirectory = new DirectoryInfo(Application.StartupPath+"\\TMP"); FileInfo [] DbfFiles = TMP_ReportDirectory.GetFiles("*.dbf"); if (DbfFiles.Length != 0) { string ITMConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath+ "\\TMP;Extended Properties=dBase IV"; OleDbConnection ITMConnection = new OleDbConnection(ITMConnString); string ITMSelectQuery = "Select TYPE,EMPLOYEE from " + DbfFiles[iloop].ToString(); //MessageBox.Show(ITMSelectQuery); OleDbCommand ITMCommand = new OleDbCommand(ITMSelectQuery,ITMConnection); ITMConnection.Open(); OleDbDataReader ITMReader = ITMCommand.ExecuteReader(); string STOCKConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath+ ";Extended Properties=dBase IV"; OleDbConnection STOCKConnection = new OleDbConnection(STOCKConnString); while (ITMReader.Read()) { frmwait.statusBar1.Text = " Loading . . . . . . ."; string newID = ITMReader.GetValue(0).ToString(); string newLONGNAME = ITMReader.GetValue(1).ToString(); string STOCKInsertQuery = "INSERT INTO GNDITEM.DBF (TYPE, EMPLOYEE) VALUES (" + newID +",\"" + newLONGNAME + "\")"; //MessageBox.Show(STOCKInsertQuery); OleDbCommand STOCKInsertCommand = new OleDbCommand(STOCKInsertQuery,STOCKConnection); STOCKConnection.Open(); STOCKInsertCommand.ExecuteNonQuery(); STOCKConnection.Close(); } ITMConnection.Close(); ITMReader.Close(); STOCKConnection.Close(); }
This has to be slow. Dbf files dont have a very good performance and copying all this together for just some queries is probably a bit overhead. Some hints you might try out: 1. Try out the Visual Fox Pro Ole Db Provider. It can be found somewhere on MSDN. I dont know it but its probably faster (I used it once and never had performance issues). 2. What kind of report are you making? Do you really need one big DBF file? Consider getting all data at once into one
DataTableand then calculating whatever you need on thisDataTable. You can use theUNION ALLstatement to get all tables in one statement:Select TYPE,EMPLOYEE from MyDbfFile1 UNION ALL Select TYPE,EMPLOYEE from MyDbfFile2 UNION ALL Select TYPE,EMPLOYEE from MyDbfFile3 ...3. Use some real Database ;) -
This has to be slow. Dbf files dont have a very good performance and copying all this together for just some queries is probably a bit overhead. Some hints you might try out: 1. Try out the Visual Fox Pro Ole Db Provider. It can be found somewhere on MSDN. I dont know it but its probably faster (I used it once and never had performance issues). 2. What kind of report are you making? Do you really need one big DBF file? Consider getting all data at once into one
DataTableand then calculating whatever you need on thisDataTable. You can use theUNION ALLstatement to get all tables in one statement:Select TYPE,EMPLOYEE from MyDbfFile1 UNION ALL Select TYPE,EMPLOYEE from MyDbfFile2 UNION ALL Select TYPE,EMPLOYEE from MyDbfFile3 ...3. Use some real Database ;)thx man i used UNION ALL STATMENT a lot faster than reading DBF files one by one and adding Records. this code is with loop string ITMConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + ";Extended Properties=dBase IV"; OleDbConnection ITMConnection = new OleDbConnection(ITMConnString); string ITMSelectQuery = "Select * from "+ALOHA_PATH+"\\GNDITEM.DBF UNION ALL Select * from "+ALOHA_PATH+"\\"+datefolders.ToString()+"\\GNDITEM.DBF" ; MessageBox.Show(ITMSelectQuery); OleDbDataAdapter da= new OleDbDataAdapter(ITMSelectQuery ,ITMConnection); da.Fill (dsrefresh,"GNDITEM"); dv = new DataView(dsrefresh.Tables["GNDITEM"]); dataGrid1.DataSource = dv; ITMConnection.Close(); i have a small problem to generate a report out of dataset, before i was using the main DBF to Generate Report. how can i solve this problem.
-
thx man i used UNION ALL STATMENT a lot faster than reading DBF files one by one and adding Records. this code is with loop string ITMConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + ";Extended Properties=dBase IV"; OleDbConnection ITMConnection = new OleDbConnection(ITMConnString); string ITMSelectQuery = "Select * from "+ALOHA_PATH+"\\GNDITEM.DBF UNION ALL Select * from "+ALOHA_PATH+"\\"+datefolders.ToString()+"\\GNDITEM.DBF" ; MessageBox.Show(ITMSelectQuery); OleDbDataAdapter da= new OleDbDataAdapter(ITMSelectQuery ,ITMConnection); da.Fill (dsrefresh,"GNDITEM"); dv = new DataView(dsrefresh.Tables["GNDITEM"]); dataGrid1.DataSource = dv; ITMConnection.Close(); i have a small problem to generate a report out of dataset, before i was using the main DBF to Generate Report. how can i solve this problem.
What do you mean with 'report'? Crystal report? If yes Im sorry to say I dont have much knowledge in making crystal reports :(( I can only help you if you state how your report creation functions so far. That includes what kind of report you are creating, what data it should present and how you currently bind the datasource to it.
-
What do you mean with 'report'? Crystal report? If yes Im sorry to say I dont have much knowledge in making crystal reports :(( I can only help you if you state how your report creation functions so far. That includes what kind of report you are creating, what data it should present and how you currently bind the datasource to it.
-
Yup i want to Create a Crystal Report, can i create a DBF file from my DATASET , if yes, then i can easily create a Crystal Report. i tried to create a blank report, then set Report data source to DATASET but it didn't work. thx Robert
As I said I have no experience with Crystal Report. But a quick googling showed up this. It basically says you have to bind your report at design time to a XSD schema file and then bind the DataTable at runtime. Hope this helps.
-
As I said I have no experience with Crystal Report. But a quick googling showed up this. It basically says you have to bind your report at design time to a XSD schema file and then bind the DataTable at runtime. Hope this helps.