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.StoredProcedur e;
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.AddWithValu e("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.AppSe ttings["DB FFileLocat ion"];
//string connectionString = @"Provider=VFPOLEDB;Data Source=" + dbfFile;
string connectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\DBF_files_Folder\201809 21;
Extended Properties = dBASE IV";
using (OleDbConnection connection = new OleDbConnection(connection String))
{
OleDbCommand cmd = connection.CreateCommand() ;
cmd.CommandType = CommandType.StoredProcedur e;
cmd.CommandText = "ExecScript";
OleDbParameter parm = cmd.CreateParameter();
parm.OleDbType = OleDbType.Char;
//cmd.Parameters.Add(parm) ;
//parm.Value = myCode;
cmd.Parameters.AddWithValu e("code", myCode);
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// ...
}
}
}
catch (Exception ex)
{
}
{
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
OleDbConnection con = new OleDbConnection(strCon);
OleDbCommand cmd = con.CreateCommand();
con.Open();
cmd.CommandType = CommandType.StoredProcedur
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.AddWithValu
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.AppSe
//string connectionString = @"Provider=VFPOLEDB;Data Source=" + dbfFile;
string connectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\DBF_files_Folder\201809
Extended Properties = dBASE IV";
using (OleDbConnection connection = new OleDbConnection(connection
{
OleDbCommand cmd = connection.CreateCommand()
cmd.CommandType = CommandType.StoredProcedur
cmd.CommandText = "ExecScript";
OleDbParameter parm = cmd.CreateParameter();
parm.OleDbType = OleDbType.Char;
//cmd.Parameters.Add(parm)
//parm.Value = myCode;
cmd.Parameters.AddWithValu
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// ...
}
}
}
catch (Exception ex)
{
}
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.
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.
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.
ASKER
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.
ASKER
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\201809 21\ 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.
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.
ASKER
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,
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.
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.
ASKER
"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
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.
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 TRIALMembers 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.
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.