.NET Programmer Thoughts

Friday, September 10, 2004

Using ADO.NET to UPDATE VFP tables

Using ADP.NET to update to VFP tables is not an easy task. Finding help on the subject is even harder. Maybe I'm doing it wrong but this is what I came up with.

First, I decided to use GOTDOTNET's Data Application Block. I did this because I'm eventually going to convert the tables to SQL Server. In theory, by using their data application block, I will only have to make changes to my configuration file.

I wanted to include all the code but the formatting gets all screwed up. So I will just put in the more important pieces. If you are really struggling, just email me and I will send you the code. Also, the point here is not to show you how to use the Application Block so I won't go into how working with it.

My constructor start out with:

helper = GetAdoHelper();
cs = GetConnectionString();

First, I wanted to try to retrieve all customers from the CUSTOMER table. I created a method named GETALLCUSTOMERS.

DataSet ds = new DataSet();
helper.FillDataset( cs, CommandType.Text,

"Select * from Customer", ds, new string[] { "Customers" } );
return ds;


This was very simple. Just create a variable to hold the dataset. And then use the helper to fill the dataset. "helper" is the code calls GOTDOTNET's ADOHELPER.

My next task was to try to find a customer. I created a method called FINDCUSTOMER.

DataSet ds = new DataSet();
helper.FillDataset( cs, CommandType.Text,
"Select * from Customer Where cust_id = " + customernumber, ds,
new string[] {"Customers"} );
return ds;


This turned out to be another very simple task.

The last thing on my list was to save changes to the customer table. I created a method named SAVECUSTOMERS.

First, get the changes that were made to the dataset.

DataSet tempDataSet = custds.GetChanges();

Then do some checking to see if the temporary dataset is null and if it doesn't have errors.

if(tempDataSet!=null)

if(!tempDataSet.HasErrors)

If it passed the conditions then you can update the table.

helper.UpdateDataset(insertCMD,deleteCMD,updateCMD,tempDataSet,"Customers");
custds.AcceptChanges();


Now you ask where did insertCMD,deleteCMD,updateCMD come from. This was the hard part.

Start by defining your SQL statements. I ended up put these in the constructor.

deleteSQL = "DELETE FROM Customer WHERE cust_id = ?";

updateSQL = "UPDATE customer SET last_name = ? WHERE cust_id = ?";

insertSQL = "INSERT INTO customer (cust_id,last_name,first_name,";
insertSQL += "address_1,address_2,city,state,zip,home_phone,work_phone) VALUES ";
insertSQL += "(?,?,?,?,?,?,?,?,?,?)";


Now create the command. I also put this in the constructor.

insertCMD = helper.CreateCommand(cs,insertSQL,CommandType.Text,null);
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.Decimal,10,"cust_id"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,30,"last_name"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,20,"first_name"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,30,"address_1"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,30,"address_2"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,30,"city"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar, 2,"state"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,10,"zip"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,14,"home_phone"));
insertCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,14,"work_phone"));

updateCMD = helper.CreateCommand(cs,updateSQL,CommandType.Text,null);
updateCMD.Parameters.Add(new OleDbParameter("?",OleDbType.VarChar,30,"last_name"));
updateCMD.Parameters.Add(new OleDbParameter("?",OleDbType.Decimal,10,"cust_id"));

deleteCMD = helper.CreateCommand(cs,deleteSQL,CommandType.Text,null);
deleteCMD.Parameters.Add(new OleDbParameter("?",OleDbType.Decimal,10,"cust_id"));


I did find out that "Numeric" in VFP is linked to Decimal in ADO.NET. Out of pure laziness, I didn't put all the fields in the UPDATE SQL statement. I started to update it but I know this code works and I didn't want to put code out there that doesn't work.

Here are the variables assigned in the class.

private string updateSQL;
private string insertSQL;
private string deleteSQL;
private IDbCommand updateCMD;
private IDbCommand insertCMD;
private IDbCommand deleteCMD;
private AdoHelper helper;
private string cs;


Oh and here is my using section:

using System;
using System.Data;
using System.Data.OleDb;
using GotDotNet.ApplicationBlocks.Data;

This took several days to put together but it's passed my NUnit testing and works from a DATAGRID. I can't believe it has to be this hard. I kept wishing I could use GATHER!!!!

Again, if you want to see ALL the code just email me and I will send it to you. If I can figure out a way to post it I will.