Data Access

Last Updated: 2001

Beta 1 manuals are pretty useless for understanding Beta 2 stuff as all the names have changed.

.NET basically supports ADO.NET and isn't interested in anything else (it may be possible to create OLE DB connections directly through OLE2, but I wouldn't like to try). There are currently only two ADO.NET providers (provider = driver?), one for SQL 7 & 2000 and one for OLE DB emulation. (?? So, to access VFP data, we'd have to go: C# -> ADO -> OLE DB -> ODBC -> Fox! ??)

There are two approaches to data under ADO.NET: DataSet objects (fat clients / connections) and Reader objects (thin clients / connections)

DataSet objects pull down a lot of data and metadata (MS describe DataSets as "in-memory, database-like containers"). Reader objects pull down data a record at a time.

DataSet objects I will have to look at later, as I can't get any of it to work in Beta 2 (I don't think its broken, just not documented properly. The Beta 1 books are no good here as the object name / model has changed to much)

VFP Code ADO.NET C# Equivalent
Local lnCH using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication_Data1 {
    class Class1 {
        static void Main() {
lnCH = SQLStringConnect("server=spike_nt;
                              usrid=sa;pwd=;database=northwind)		 SqlConnection myConnection = new SqlConnection("server=spike_nt;
                     user id=sa;password=;database=northwind);
myConnection.Open();
  SqlCommand myCommand =
                     new SqlCommand("select * from Employees");
SqlDataReader myReader = myCommand.ExecuteReader();
SQLExec(lnCH, "select * from Employees")
SCAN
         ?Employees.FirstName + ", " + Employees.LastName
ENDSCAN		 while (myReader.Read()) {
    Console.WriteLine(myReader["FirstName"] + ", " +
                    myReader["LastName"]);
}
SQLDisconnect(lnCH) myReader.Close();
myCommand.Dispose();
myConnection.Close();
      }
  }
}

Neat Things:

DataSet Objects

Example:

using System;
using System.Data;
using System.Data.SqlClient;
class DaveTest {

    public static void Main() {

DataSet ds = new DataSet("myDataSet");
DataTable dt;
DataRow dr;

// Connect to server
Console.WriteLine("Connecting to server...");
SqlDataAdapter sqlDA = new SqlDataAdapter("select * from Employees",
"server=myServer;user id=sa;password=;database=northwind");

// Fill dataset
Console.WriteLine("Filling dataset...");
sqlDA.Fill(ds, "Employees");

// Tell dataset it can create its own SQL Insert, Delete and Update statemtents
SqlCommandBuilder custCB = new SqlCommandBuilder(sqlDA);

// Extract some metadata
dt = ds.Tables["Employees"];
Console.WriteLine("Table has " + dt.Columns.Count.ToString() + " columns:");
int n, o;
for (n=0; n<dt.Columns.Count; n++)
    Console.WriteLine("\t" + dt.Columns[n].Caption);

// Extract rows
Console.WriteLine("\nReading data...");
for (o=0; o<dt.Rows.Count; o++) {
    Console.Write(o.ToString() + ": ");
    dr = dt.Rows[o];
    for (n=0; n<dr.ItemArray.Length; n++)
            Console.Write("\t" + dr.ItemArray[n].ToString());
    Console.WriteLine("\n");
}

// Add a row (base on another row to avoid nulls and date problems)
dr = dt.NewRow();
for (n=0; n<dr.ItemArray.Length; n++)
    dr[n] = dt.Rows[0][n];
dr["EmployeeID"] = 1001;
dr["LastName"] = "Brankin";
dr["FirstName"] = "FirstName";
dr["Title"] = "Mr";
dt.Rows.Add(dr);

// Save added row
sqlDA.Update(ds, "Employees");

Console.WriteLine("Done!");

    }
}