Link to home
Start Free TrialLog in
Avatar of Veera Reddy
Veera Reddy

asked on

VPFOLEDB driver should be installed on all client machines for DBF Index file generation in C#

try
            {
                var cdxExpressions = new List<CDXExpression> {
                new CDXExpression { expression = "ENTRY_DATE", name = "cdx_p1"},
                new CDXExpression { expression = "LOSS_PD", name = "cdx_p2"}
            };

                string strCon =
                    @"Provider =vfpoledb;Data Source=C:\DBF_files_Folder\20180921\" + tableName + ".DBF"; // TestDataBase1.DBF";
                OleDbConnection con = new OleDbConnection(strCon);
                OleDbCommand cmd = con.CreateCommand();
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ExecScript";

                var myCode = "use " + tableName + " exclusive\n" +
                      string.Join("\n", cdxExpressions.Select(e => $"index on {e.expression} tag {e.name}")) +
                      "\nuse";

                cmd.Parameters.AddWithValue("code", myCode);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {

            }
C# -

Code above used VFPOLEDB driver. This driver should be installed on machine explicitly.
When I put my WPF application on common drive and ask users to access it, they also got to install VPFOLEDB. Is there a other than installing this MSI on all client machines.
Tried using JET OLEDB. It gives an error saying 'Cannot execute a Select Query'.

try
            {
                var cdxExpressions = new List<CDXExpression> {
                new CDXExpression { expression = "ENTRY_DATE", name = "cdx_p1"},
                new CDXExpression { expression = "LOSS_PD", name = "cdx_p2"}
                 };            


                var myCode = "use " + tableName + " exclusive\n" +
                      string.Join("\n", cdxExpressions.Select(e => $"index on {e.expression} tag {e.name}")) +
                      "\nuse";

                string dbfFile = ConfigurationManager.AppSettings["DBFFileLocation"];
                //string connectionString = @"Provider=VFPOLEDB;Data Source=" + dbfFile;
                string connectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\DBF_files_Folder\20180921;
            Extended Properties = dBASE IV";

                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    OleDbCommand cmd = connection.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "ExecScript";
                    OleDbParameter parm = cmd.CreateParameter();
                    parm.OleDbType = OleDbType.Char;
                    //cmd.Parameters.Add(parm);
                    //parm.Value = myCode;

                    cmd.Parameters.AddWithValue("code", myCode);

                    try
                    {
                        connection.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                       
                        // ...
                    }
                }
            }
            catch (Exception ex)
            {
            }
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Well, you use VFP specific code: EXECSCRIPT, INDEX ON expression TAG name. That's needing the VFP OLEDB Provider. Even just the core format of DBF files can't be handled with JET engine, the JET engine is about MS Access, and at maximum this would support legacy dbf dBase tables, generally speaking, you would only use JET for accdb databases in current driver versions. not DBFs.

You may ask and post your question about what's possible with JET and dbf files in the Access section. The only usage of JET or ACE OleDB providers the VFP community has is to support access to excel files. We obviously have no problems accessing DBFs even natively without any driver.

For a very long time, DBF support of Office, Excel or Access only is about legacy versions, aka dBase tables, not at all about VFP DBFs.

But what is the big problem of installing a new provider on all clients of a company? There are ways to do that without an administrator needing to visit every computer in person, look into the topic of SCM. The IT department of a bigger corporation should be able to provide a way to assign software packages, either just the OLEDB provider or your whole package if you manage to create an MSI setup.

Just keep in mind the VFP OleDB provider is 32 bit so your C# must be compiled for a 32bit target CPU, too. All this runs on 64bit systems, as there still always is the 32bit subsystem, so that's not the problem, but as far as I tried compiling C# clients for Any CPU target means the executable will not find 32bit ODBC drivers or OleDB providers, as running in a 64bit system it will start as 64 bit process and it hasn't any means to "realize" it needs to access 32bit drivers/providers.

Bye, Olaf.
Avatar of Veera Reddy
Veera Reddy

ASKER

Thanks Olaf. You information is of great help. Installing VPFOLEDB in each client machine looks to be difficult, so we have decided to implement Ssis dtsx package for dbf and cdx generation. I'll update soon on this approach once completed.
The main question here is: Do you really need to store data processed in C# in DBF format?  C# offers many more reliable ways to store/process data. You may use SQL Server Express, SQLite etc.  Even some C# structures could do this job better than DBF.

OTOH, if you are just reading DBF data prepared in other applications then you should not generate indexes on such data. If you really need such index then you can use Visual FoxPro application to create it and process the data.
It's impossible to say for sure what's necessary without knowing the outset of which application imports or exports what data here, whether the source code of that is available to change the outset and what's the final goal at all.

It seems you have to deal with a daily folder of some DBFs, you could indeed easily import this data into SQL Server and then process it starting from there. It'll need the extra time of reading in all data, but likely your indexing alone also reads much of the data anyway, at least the columns addressed by the index expressions are fully read from the DBF and are also written back (in form of an index tree) into the CDX file. So importing this daily data could solve this better, as that process only needs to be done on one client or even at the database server once you copied the DBFs there. And then any client-side application able to connect to MSSQL could be used from that point onwards.

Bye, Olaf.
We are in process of migrating old applications to .net. Old foxpro applications use dbf and in order to keep the applications still running, other than the once migrated, we need to generate dbf at the points where migrated applications end and foxpro applications do the rest. Once we completely migrate all foxpro applications we don't need to generate dbf files. For the migrated applications we are using SQL and ssis.
c# script in ssis even if it uses VPFOLEDB doesn't require installations on client machine as it runs in server, we only need to install VPFOLEDB on server if needed.
That's true, but you only can reach to DBF files as far as the OleDB provider can see and visibility of files and shares also is limited to the system account of MSSQL then. You know better, but if the folder C:\DBF_files_Folder\20180921\ is a local folder to clients, a Server side OLEDB provider won't be able to reach out there, especially as SQL Server typically doesn't run under a domain account.

If the files are local on the servers C drive, then the best option is not just to use the OLEDB provider to execute the VFP code to create an index, but read in all data into SQL Server database/tables and continue from there.

Bye, Olaf.
I fully agree to Olaf's posts.
The C drive location is just for unit testing of code. The file is actually stopped on shared drive. The exe of the application is also stored in such shared drive and user launches applications using a launcher application which shows all the exe's available there. Thus, application runs locally but it's not installed on local machine. While dealing with SQL and remote location and access, we have impersonated the user. We don't have any server as such where I can install vpfoledb and applications runs on local environment.

Thanks,
Veera
Thanks for the clarifications. When you have no central server I still assume you use one client as a server in a workgroup LAN, where you have your MSSQL server running. There are still many unknowns about the nature of the problem, but I can't help finding a recommendation and best solution if you don't want to tell more.

Pavel and I share the suspicion you're doctoring on a legacy FoxPro application with this and similar scripts acting on DBFs in ways you may rather break than mend its data and you should do something else. An application should know best to handle its data including its indexing and to not disturb its functionality you should do all your own concerns about the data and the tuning of the performance of additional software by a parallel branch of a synched database you own and not by external extension or modification.

The thought "an additional index won't hurt" will be true most of the time, but I fear you'll stretch it to a point you break it before you know how to revert it.

Bye, Olaf.
"you'll stretch it to a point you break it before you know how to revert it". This is a decent statement to be considered before any enhancement. Thanks for that.

We already have ssis dtsx packages as part of this application. We are not extending to New technology instead of overcoming a small problem. We had this option of creating dbf and cdx using Ssis package as plan B.

SQL has a dedicated server and the procedure here invokes the Ssis package which is deployed in separate/other dedicated machine. Vpfoledb is installed on this machine and cdx generation from this package works fine. I'm using almost same code of c# as script in Ssis package.

We now have a workaround for the problem which can also server as a permanent solution.

We currently have many dtsx packages for operations on data. Frontend wpf application is just a triggering point and as we have given UI for some master tables data maintenance, we had to generate dbfs once records are added or updated.

Thanks a lot for your inputs Olaf and Pavel.

Thanks, Veera
I just notice you have no mechanism to deal with failing to get exclusive access. So this script only works based on access to the DBF files is low. Maybe because they are just created, used by only one client so far. It naturally would belong to table creation to create index tags at that stage, when you naturally have exclusive access in the creation process.

Since that's not the case you fiddle with something you don't "own" - not in the business sense but in the sense of responsibilities and guarantees a developer can or should make about his software. If I were the vendor of a software it would be one thing you could copy out and then fiddle with this data copy as you like, I'd not protect my database, if it's merely the structure for your data just like a word document is only the structure of your document content and there's no reason to protect this. But fiddling with the structure I wouldn't take responsibility for my software still working with your modifications.

So even if you know what you're doing and tested it well, you never know about long-term effects. You might say since this is daily data the longest term you need to look at is one day and since it works out it's ok, but you never know when this daily data is used for weekly, monthly, yearly statistics or anything later troubled by your modifications.

If I were a developer for a company I'd be more cautious than you, I can only recommend, it's up to you what you do in the end. But taking this as export and importing it to my own realm of a database I'd decouple this from any usage of the original DBFs and won't need to worry. If the original software doing the DBFs would need that index, it would create it itself.

Bye, Olaf.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.