.NET Development
Connecting to a VFP table using VB .NET (Visual Studio 2017)
Gravatar is a globally recognized avatar based on your email address. Connecting to a VFP table using VB .NET (Visual Studio 2017)
  Russell
  All
  Sep 19, 2018 @ 08:07pm

Hi all,

I am new to VB .NET and was wondering whether anyone could let me know in code how to connect to a local (ie. on my C: drive) VFP table using VB .NET.

I have been Googling all day and have not been able to get any of the examples I've found to work.

My test table is "C:\VBNET\VFP_TEST_TABLE.DBF".

My initial observation is that it seems a lot harder in VB .NET than using VFP! Quite literally "USE VFP_TEST_TABLE" or "SELECT * FROM VFP_TEST_TABLE".

Regards,

Russell.

Gravatar is a globally recognized avatar based on your email address. re: Connecting to a VFP table using VB .NET (Visual Studio 2017)
  Rick Strahl
  Russell
  Sep 20, 2018 @ 05:28pm

It's not more difficult just different. xBase is different in that it has a DB language built in that has native data commands. Just about any other language will use a driver using SQL commands to access data.

Here's how you do it (using Westwind.Utilities) using C:

Dynamic data access with a Data Table

var connString = @"Provider=vfpoledb.1;Data Source=c:\apps\data\;EXCLUSIVE=false;Deleted=true;NULL=false;";

var sql = new SqlDataAccess(connString);

var table = sql.ExecuteTable("select * from Customers")

// dynamic data
foreach(var row in table.Rows)
{
     Console.WriteLine(row["Company"] + ", " + row["LastName"] + ", " + row[FirstName] +
                       " " + ((DateTime) row["Entered"]).ToString("MMM dd, yyyy"));
}

Strongly Typed Data Access with a Customer Entity

var connString = @"Provider=vfpoledb.1;Data Source=c:\apps\data\;EXCLUSIVE=false;Deleted=true;NULL=false;";

var sql = new SqlDataAccess(connString);

// typed data
var custList = sql.Query<Customer>("select * from Customers");

foreach(var cust in custList)
{
    Console.WriteLine(cust.Company + " " + cust.Lastname + ", " + cust.Firstname + 
                      cust.Entered.ToString("MMM dd, yyyy")
}

Then separately the class definition that will hold the data:

// type declaration for data to load into
public class Customer 
{
    public string Company {get; set;}
    public string Lastname {get; set; }
    public string Firstname {get; set; }
    public DateTime Entered {get; set; }
}

VB.NET syntax will be slightly different but the code flow will be exactly the same.

You can use a code converter like the one at:

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Connecting to a VFP table using VB .NET (Visual Studio 2017)
  Russell
  Rick Strahl
  Sep 20, 2018 @ 10:14pm

Thanks a lot for that Rick.

From looking around the internet I'm starting to think that maybe I should be using LINQ TO SQL?

Regards,

Russell.

Gravatar is a globally recognized avatar based on your email address. re: Connecting to a VFP table using VB .NET (Visual Studio 2017)
  Rick Strahl
  Russell
  Sep 21, 2018 @ 12:24am

You most definitely shouldn't use Linq to SQL. It's deprecated technology for well over 10 years now. If anything 'modern' it would be Entity Framework. However there's no direct support for FoxPro with Entity Framework (or LINQ to SQL for that matter) - only through a third party provider. I haven't used it so no idea how well it works and what feature compatibility is.

Bottom line is if you're starting something new you might want to consider using something other than FoxPro tables. Moving tables to some sort of SQL backend (SQL Server, Postgres, MySql) to get better support from any language other than FoxPro, as well as getting away from the frailty that is the FoxPro DBF/FPT/CDX format.

I also recommend that you pick up a book on building business applications with .NET (or whatever platform you choose) to get a better understanding how things work. Trying to do things the FoxPro way is not going to serve you well in just about any other environment simply because xBase is so very different than most other languages especially when it comes to data access. Just my two cents.

As somebody who's made those mistakes I can tell you that clinging to FoxPro way of thinking was one of the things that made the initial learning curve a lot longer than it could have been and now looking back some of those things (and some of the code I wrote and still maintain today) seems ridiculous 😃.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Connecting to a VFP table using VB .NET (Visual Studio 2017)
  Russell
  Rick Strahl
  Sep 21, 2018 @ 01:46am

Ok Rick, point taken re LINQ TO SQL.

Thanks for your very helpful feedback on all of this.

Regards,

Russell.

© 1996-2024