With the functionality provided by the .NET runtime, building data access components with C# (pronounced “c-sharp”) is not difficult.

In this article, we discuss how to access data stored in a SQL Server database. We will then review the steps necessary to build a Dynamic Link Library and an Executable file in C# to query a table and display the results.

With the introduction of the new programming language C#, Microsoft has created a powerful object-oriented tool to make solutions for the .NET platform quick and easy. Utilizing ADO.NET with C# simplifies data access even more, as you will see in the following code examples. Our purpose for this article is to make you feel more at ease with data access in C#, and to introduce some of the features of both C# and ADO.NET.

ADO.NET

ADO.NET is the next step in the evolution of ADO, Microsoft's strategic, high-level interface to all kinds of data. There are two basic parts to ADO.NET, the DataSet and the managed providers.

A DataSet represents an in-memory cache of data, capable of including multiple related tables and constraints. Data received from a data store resides locally in RAM or persists locally on disk. Tables are represented by DataTable objects, which hold the actual data. This is akin to an offline view in VFP or a disconnected recordset in ADO. The TablesCollection object contains all the DataTable objects in a DataSet. A relationship, represented by the DataRelation object, associates rows in one data table with rows in another data table. The RelationsCollection object contains all the DataRelation objects in a DataSet. A DataSet is ideal for disconnected n-tier situations, such as web applications. However, it may not be ideal when a query result is very large or unknown in size, because local resources must be allocated to store the entire result set for the life of the DataTable.

A managed provider establishes connections between a DataSet and a data store, such as a SQL Server� database. There are three components of a managed provider implementation:

Connection, commands, and parameters provide the interface between the data source and a DataSet object. The DataSetCommand interface provides the bridge between the DataSet object and its source data store for retrieving and saving data. It defines column and table mappings.
The data stream provides high-performance, forward-only access to data via the DataReader object, which reads a stream of data records from a data source. Only one record at a time is ever in memory. This is ideal for iterating over the data stream when result sets are very large or unknown in size, since it avoids the overhead of downloading and constructing the entire data set from a query.
Low-level objects connect to a database and execute database system commands.

Microsoft provides two managed providers with ADO.NET. The SQL Managed Provider mediates between a DataSet table object and a table or view in a SQL Server� (version 7.0 and later) database via the SQLDataSetCommand object. The ADO Managed Provider does the same for any data source with an OLE-DB provider, using the ADODataSetCommand object. Both providers offer identical functionality, but with the various objects prefixed by either ADO or SQL. Any examples using the ADO provider will work with the SQL provider simply by substituting ADO for SQL in the object name prefixes.

System Architecture

In this example, our system architecture is a typical 3-tier model with a Data Tier, Middle Tier, and Presentation Tier. The Data Tier consists of a SQL Server 2000 database, with our system querying data from the Employee table of the PUBS database. The Middle Tier is housed in a C# Dynamic Link Library (.DLL) named MYSQL.DLL. MYSQL.DLL contains the program code to create a SQL Server connection and query the database. We will implement MYSQL.DLL using two different approaches. The first (MySQLDATASET.cs) utilizes the DataSet, while the second (MySQLDATAREADER.cs) uses the DataReader. The Presentation Tier is a C# executable file (.EXE) named MYMAIN.EXE. MYMAIN.EXE contains program code to instantiate an object from the Employee class defined in MYSQL.DLL. The Employee object exposes the GetEmployee method, which queries the employee table and writes the results. We chose to use ADO.NET's SQL managed provider, since we are working with only a SQL Server data store.

Source Code for MySQLDATASET.cs

The DataSet approach is best suited for situations where the data store is disconnected and query results are not extremely large. We will examine the source code for MYSQLDATASET.cs in greater detail below.

//---------------------------------------------
//	MySQLDATASET.cs
//---------------------------------------------
using System; // Implements the console class
using System.Data; // Implements the DataSet class 
using System.Data.SQL; // Implements the SQLDataSetCommand class
using System.Runtime; // Implements the object class

// Declare the MySQL namespace
namespace MySQL 
{
    // Declare class Employee
    public class Employee 
    {
        //Declare method GetEmployee
        public void GetEmployee() 
        {
            // Instantiate a Dataset object
            DataSet oDS = new DataSet();
            try 
            {
                // Attempt to execute the code below
                // Open the connection and run the Command
                SQLDataSetCommand oCMD = new SQLDataSetCommand("Select * from employee", "server=localhost;uid=sa;pwd=;database=pubs");
                
                // Populate the DataSet object
                oCMD.FillDataSet(oDS, "EmployeesList");
                
                // Loop through rows of the DataSet oDS
                foreach (DataRow oRow in oDS.Tables[0].Rows) 
                {
                    // Process each field for the current row oRow
                    foreach (object oField in oRow.ItemArray)
                    {
                        // Write the current field to the console
                        Console.Write(oField.ToString());
                    }
                    
                    // write newline
                    Console.WriteLine();
                }
            }
            catch (Exception e)
            {
                // Execute this logic if an error occurs.
                // An error occurred, pass the exception up
                throw e;
            }
        } 
    } 
}

The first thing we do is make references to System namespaces provided by the .NET framework. This is done with the using directive and is analogous to setting “references” in Visual Basic (VB) or using the “set classlib to” directive in Visual FoxPro (VFP). The use of namespaces is advantageous because it leads to better organization and less coding. Namespaces can also be referred to as class libraries (see sidebar for more information about namespaces and the “using” directive).

using System; // Implements the console class
using System.Data; // Implements the DataSet class 
using System.Data.SQL; // Implements the SQLDataSetCommand class
using System.Runtime; // Implements the object class

Next, the namespace MySQL is declared. The MySQL namespace implements the Employee class.

// Declare the MySQL namespace
namespace MySQL {

The Employee class is declared with the public modifier. This indicates that the Employee class will be available to all procedures in all modules in all applications.

// Declare class Employee
public class Employee {

The Employee class contains the method GetEmployee(). Remember, the GetEmployee method performs the data retrieval and display functions. The GetEmployee method is declared public in scope and has a void return type, meaning that the method does not return a value.

//Declare method GetEmployee
public void GetEmployee() {

The System.Data namespace implements the DataSet class, which can be considered the centerpiece of ADO.NET. The DataSet class provides a rich object model to work with when passing data between various components of an enterprise solution.

After we declare the GetEmployee method, we instantiate the DataSet object oDS. It will hold the query results from the database.

// Instantiate a Dataset object
DataSet oDS = new DataSet();

We create a connection to the SQL Server database “pubs”, using the SQLDataSetCommand class of the System.Data.SQL namespace. The first parameter of the SQLDataSetCommand class is the SQL Select statement used to generate the result set. The second parameter is a string containing information necessary to establish a connection with SQL Server.

// Open the connection and execute the Command
SQLDataSetCommand oCMD = new SQLDataSetCommand("Select * from employee", "server=localhost;uid=sa;pwd=;database=pubs");

Once the database connection is established, the FillDataSet() method is invoked. As a result, a DataTable object named EmployeesList is created in the DataSet object oDS and is populated with records from the employee table.

// Populate the DataSet object
oCMD.FillDataSet(oDS, "EmployeesList");

The foreach loop processes records held by the EmployeeList DataTable object (a foreach statement iterates through a collection or array to get the desired information). The variable oRow of type DataRow represents a row of data in the DataTable oDS.Tables[0].

// Loop through all rows of the DataSet oDS
foreach (DataRow oRow in oDS.Tables[0].Rows) 
{
    // Process each field for the current row oRow
    foreach (object oField in oRow.ItemArray)
    {
        // Write the current field oField to the console
        Console.Write(oField.ToString());
    }
    // write newline
    Console.WriteLine();
}

The second foreach loop iterates through each field of a data row. The variable oField is similar to a variant type in VB or VFP. A variable declared with object type is flexible enough to contain a reference to any object. Use of the object type in this instance makes sense, because each field is of a different data type. The oField.ToString() method returns a string representation of the object invoking it.

The System namespace implements a class called Console which provides simple input/output services. The Write() and WriteLine() methods are used to output field values to the console.

Source Code for MySQLDATAREADER.cs

The DataReader approach is best suited for situations where the data store is not disconnected and query results are represented by a single resultset returned from a SQL command. We will examine the source code for MYSQLDATAREADER.cs in greater detail below.

//---------------------------------------------
//	MySQLDATAREADER.cs
//---------------------------------------------
using System;  // Implements the console class
using System.Data; // Implements the DataSet class 
using System.Data.SQL; // Implements the SQLDataSetCommand class
using System.Runtime; // Implements the object class

// Declare the MySQL namespace
namespace MySQL 
{
    // Declare class Employee
    public class Employee 
    {
        //Declare method GetEmployee
        public void GetEmployee() 
        {
            // Create SQLConnection object
            SQLConnection oConnection = new SQLConnection("server=localhost;uid=sa;pwd=;database=pubs");
            
            // Create SQLCommand object
            SQLCommand oCommand = new SQLCommand("SELECT * FROM employee",oConnection);
            
            // Declare the SQLDataReader variable 
            SQLDataReader oDataReader;
            
            // Exception handler
            try 
            {
                // Open connection to SQL Server
                oConnection.Open();   
                
                // Create the DataReader 
                oCommand.Execute(out oDataReader);
                
                // Attempt to retrieve records
                while (oDataReader.Read()) 
                {
                    // Create array of objects
                    Object[] oObjArray = new Object[oDataReader.FieldCount];
                    
                    // Populate oObjArray with field values        
                    int nfieldcount = oDataReader.GetValues(oObjArray);
                    
                    // Process each field 
                    foreach (object oField in oObjArray)
                    {
                        // Write the current field to the console
                        Console.Write(oField.ToString());
                    }
                }
                
                // always call Close when done reading.
                oDataReader.Close();
            }
            catch (Exception e)
            {
                // Execute this logic if an error occurs.
                // Pass the exception up
                throw e;
            }
            finally
            {
                // Close the connection
                oConnection.Close();   
            }
        } 
    } 
}

The source code above mirrors the first example to the point where the GetEmployee() method is declared. Next, a new instance of the SQLConnection class is created and connection string properties are set.

// Create SQLConnection object
SQLConnection oConnection = new SQLConnection("server=localhost;uid=sa;pwd=;database=pubs");

A new instance of the SQLCommand class is initialized with the SQL statement text and a reference to the SQLConnection object, oConnection.

// Create SQLCommand object
SQLCommand oCommand = new SQLCommand("SELECT * FROM employee",oConnection);

A variable is declared to hold a reference to the the SQLDataReader class.

// Declare the SQLDataReader variable 
SQLDataReader oDataReader;

A connection to SQL Server is established by invoking the Open() method of the SQLConnection object oConnection.

// Open connection to SQL Server
oConnection.Open();

A SQLDataReader is created by using the Execute method of the SQLCommand oCommand, not through direct use of the constructor.

// Create the DataReader 
oCommand.Execute(out oDataReader);

The Read() method of oDataReader advances to the next record, returning true if another record could be read and false if not.

// Attempt to retrieve records
while (oDataReader.Read())

Once a record has been fetched, the individual fields are processed. The Object array oObjArray is declared and populated by making a call to the oDataReader.GetValues() method. The GetValues method returns the number of fields processed and accepts an Object array as a parameter.

// Create array of objects
Object[] oObjArray = new Object[oDataReader.FieldCount];

// Populate oObjArray with field values        
int nfieldcount = oDataReader.GetValues(oObjArray);

The foreach loop is similar to the one explained in the MySQLDATASET.cs example.

// Process each field 
foreach (object oField in oObjArray)
{
    // Write the current field to the console
    Console.Write(oField.ToString());
}

While the DataReader is in use, the associated connection is busy serving it until oDataReader.Close is called.

// always call Close when done reading.
oDataReader.Close();

Finally, the SQL Server connection is closed. The Close method attempts to close the connection cleanly by waiting for all transactions to clear first.

// Close the connection
oConnection.Close();

Creating MySQL.DLL from the source code is easy using Visual Studio.NET Beta 1. Create a new C# Class Library project named MySQL and select System.Data.DLL in Project References before building the solution.

Source Code for MYMAIN.EXE (MyMain.cs)

//---------------------------------------------
//	MyMain.cs
//---------------------------------------------
using System;	// Implements Exception Class
using MySQL;	// Implements Employee Class

class MyMain 
{
    public static void Main()
    {
        // Instantiate the oemployee object from Employee class
        Employee oemployee = new Employee();
        try 
        {
            // Attempt to run the code below
            oemployee.GetEmployee();
        }
        catch (Exception e) 
        {
            // Handle an exception
            Console.WriteLine("Error Message :" + e.ToString());
        }
    }
}

After reviewing the code above, you may have noticed a pattern emerging. We always declare our namespace references first. We reference the System namespace and the MySQL namespace created earlier. Remember, the MySQL namespace implements our Employee class.

using System;	// Implements Exception Class
using MySQL;	// Implements Employee Class

Functions and variables are not supported at the global level; such elements are always contained within type declarations (e.g., class and struct declarations). Therefore, the class MyMain is declared.

class MyMain {

The function Main is the entry point for any executable file. In our program, Main is declared public in scope and a static member of the class MyMain with no return type. The static modifier differentiates a method from an instance method.

public static void Main()

Before the Employee.GetEmployee() method can be invoked, an object based on the Employee class must be created.

// Instantiate the oemployee object from the Employee class
Employee oemployee = new Employee();

A method call to GetEmployee() displays the query results from the employee table to the console. The try/catch exception-handling approach is used when invoking the GetEmployee() method. In this case, however, any error message is displayed at the console.

try 
{
    // Attempt to run the code below
    // Display employee records to the console.
    oemployee.GetEmployee();
}
catch (Exception e) 
{
    // Handle an exception
    Console.WriteLine("Error Message :" + e.ToString());
}

The e.ToString() method returns a string representation of the exception object.

Again, use Visual Studio.NET Beta 1 to build the MyMain.EXE executable file. Create a C# Console Application project first, then select MySQL.DLL in Project References before building the solution.

Comparison of Namespaces and VFP Class Libraries

A C# Namespace is similar to a VFP Class Library. The using directive in C# can be compared to SET CLASSLIB TO in VFP, and the new command in C# can be compared to the CREATEOBJECT command in VFP.

Below is an example of similar functionality using VFP code and ADO. We created a VFP DLL to keep the overall structure similar, but we could have put everything into one EXE and used the SET CLASSLIB TO command to achieve the same results.

Below is the code for our VFP DLL: CSHARP.PRG.

*----------------------------------------------
* VFP Example - Querying data from SQL Server
* Authors: Wayne Myers, Aubrey Cote'
*----------------------------------------------

DEFINE CLASS employee AS custom OLEPUBLIC
    nError = 0
    cError = ""
    
    PROCEDURE GetEmployee
        lcRetVal = ''
        oConn = CREATEOBJECT('ADODB.connection')
        oConn.Open("DSN=pubs","sa","")
        oRs = CREATEOBJECT('ADODB.recordset')
        oRs.open("Select * From employee",oConn)
        DO WHILE ! oRs.EOF
            For Each ofield In oRs.Fields
                With oField
               	  lcRetVal = lcRetVal+.NAME + ' ' +TRANSFORM(.VALUE) 
                EndWith
            Next oField
            oRs.MoveNext
        ENDDO
        RELEASE oRs
        RELEASE oConn
        RETURN lcRetVal
    ENDPROC
    
    PROCEDURE Error()
        LPARAMETERS nError, cMethod, nLine
        THIS.nError = ERROR()
        THIS.cError	= Message()
        =ComReturnError()
    ENDPROC

ENDDEFINE

Let's take a look at the VFP GetEmployee() method and compare it to the C# version. The first thing we do after assigning our return value variable is to create an ADO connection object and open a connection to the SQL Server database “PUBS”.

oConn = CREATEOBJECT('ADODB.connection')
oConn.Open("DSN=pubs","sa","")

We then create an ADO RecordSet containing all the rows from the Employee table.

oRs=CREATEOBJECT('ADODB.recordset')
oRs.open("Select * From employee",oConn)

The same result was obtained in C# using the ADO.NET Dataset object and the SQLDATASETCOMMAND and FILLDATASET commands. While the syntax is very different, the results are similar. We now have a dataset containing all the rows and columns for the Employee table records.

The next few lines take us Row by Row, and assign the field NAME and corresponding VALUE to a string we eventually return to the calling program.

DO WHILE ! oRs.EOF
    For Each ofield In oRs.Fields
        With oField
            lcRetVal = lcRetVal+.NAME + ' ' +TRANSFORM(.VALUE) 
        EndWith
    Next oField
    oRs.MoveNext
ENDDO

To use a similar Error method, we overload the Error method of the object and assign the values of the error to properties of the object. The ComReturnError() method returns control to the calling program and does not continue processing.

PROCEDURE Error()
    LPARAMETERS nError, cMethod, nLine
    THIS.nError = ERROR()
    THIS.cError	= Message()
    =ComReturnError()
ENDPROC

Here is the code for our VFP program to call the VFP DLL and display the results: CALLMAIN.PRG. We made a crude attempt at a TRY/CATCH block by checking the value of nError. This could be much more elaborate, but I'm sure you get the point. Also, because VFP DLL's are not allowed to have any type of display capabilities, we returned a string from the DLL to the calling program and displayed the results with a MESSAGEBOX.

*----------------------------------------------
* VFP Example - Querying data from SQL Server
* Authors: Wayne Myers, Aubrey Cote'
*----------------------------------------------
ON ERROR *
ox = CREATEOBJECT('csharp.employee')
ln = ox.getemployee()

IF ox.nError = 0
    MESSAGEBOX(ln)
ENDIF

RELEASE ox

Conclusion

We hope this article has given you some insight into data access with C# and ADO.NET and how to compare the C# code to similar code in VFP. As you can see, C# is not terribly difficult, although VFP and VB programmers will have a bit of a culture shock the first time they use it.

Namespaces

The .NET runtime exposes services to programs via libraries called namespaces. Namespaces are a means for organizing elements. A namespace consists of the following elements: Enums, Structures, Interfaces, Classes and/or Delegates. The “using” directive enables unqualified use of the members of a namespace. Namespace declarations have no modifiers such as public or private, since namespaces are always public.

For example, the following code has nested namespaces and classes. The comment immediately following each entity has the fully qualified name.

namespace A1 
{ //A1
    namespace A2 
    { //A2
        class C1 {} //A1.A2.C1
    }
}

Exception (Error) Handling

Try-Block: Code that may cause an exception should be placed within a try-block. The block is executed until an exception is thrown or it is completed successfully.

try 
{
    code statements
}
catch (Exception e)
{
    // If an error occurs, run this code
    throw e;
    // Pass up the error to the calling //program
}
finally
{
    // release resources used by the try block
}

Catch-Block: The catch-block responds to an exception. The catch clause can be used without arguments, in which case it catches any type of exception. It also can take an object argument derived from System.Exception, in which case it handles a specific exception. The throw statement causes the exception to “bubble up” to the calling program, raising an exception there.

Finally-Block: The finally-block is used to guarantee that a block of code executes regardless of how the preceding try-block is exited. A common use of catch and finally is to obtain and use resources in a try block, deal with exceptional circumstances in a catch block, and release the resources in the finally block.

Building Data Access Components in C#

Published in:

Filed under: