The ElevateDB 2.0 .NET Data Provider is a 100% managed-code data provider that provides both local and remote C/S access to ElevateDB databases. It includes all of the required functionality for the database-agnostic classes in .NET, including a provider factory class. In addition to the base functionality that is the same for every .NET Data Provider, the ElevateDB 2.0 .NET Data Provider offers much more in the way of extended classes, properties, and functionality, and that is what we will cover in this article.

Loading SQL From a File

---

You can load an SQL statement or script directly from a file by using the EDBCommand LoadFromFile method. The file that you are loading must be a Unicode text file without any BOM (Byte Order Mark) at the beginning of the file. The following is a C# example of using the LoadFromFile method to load a script from disk and execute it using the EDBCommand ExecuteNonQuery method:

{
   // Execute the insertdevicedata.sql script that is located in the
   // application folder with the proper parameter
   //
   // SetupCommand object was created earlier for the entire form

   SetupCommand.LoadFromFile(@"\Program Files\CFQuotesApp\insertdevicedata.sql");
   SetupCommand.CommandType = CommandType.Text;
   SetupCommand.Prepare();
   SetupCommand.Parameters["SalesPersonID"].Value = SalesPersonID;

   SetupCommand.ExecuteNonQuery();
   }
}

Automatic Parameter Population

---

Some .NET Data Providers offer automatic parameter population based upon using named parameters in SQL statements. The ElevateDB 2.0 .NET data provider offers automatic, named parameter population for SQL statements and automatic parameter population for both scripts and stored procedures. This functionality is enabled by setting the EDBCommand ParamCheck property to True (the default is False), and by specifying any parameters in an SQL statement using a colon (:) prefix, like this:

{
   // Find the orders placed on that date
   
   EDBCommand DataCommand = new EDBCommand;

   DataCommand.ParamCheck = true;
   DataCommand.CommandText = "SELECT * FROM Orders WHERE OrderDate = :OrderDateParam";
   DataCommand.Prepare();
   DataCommand.Parameters["OrderDateParam"].Value = new DateTime(2008, 1, 1);

   DataCommand.Execute();

   // Show the number of orders placed in a message dialog box

   MessageBox.Show("Number of orders placed is " + 
                   DataCommand.RowsAffected.ToString());

   DataCommand.Dispose();
}

To use the automatic parameter population with a stored procedure, you would do so in a similar way by calling the EDBCommand Prepare method after populating the name of the stored procedure in the EDBCommand CommandText property:

{
   // Finally, be sure to set up the proper identity ranges for the tables
   //
   // SetupCommand object was created earlier for the entire form

   SetupCommand.CommandText = "UpdateIdentityRanges";
   SetupCommand.CommandType = CommandType.StoredProcedure;
   SetupCommand.Prepare();
   SetupCommand.Parameters["SalesPersonID"].Value = SalesPersonID;

   SetupCommand.ExecuteNonQuery();
}

In the above example, the actual UpdateIdentityRanges stored procedure looks like this:

CREATE PROCEDURE "UpdateIdentityRanges" (IN "SalesPersonID" INTEGER)
BEGIN
   DECLARE SalesPersonCursor CURSOR FOR SalesPersonStmt;
   DECLARE CustomerNoStart INTEGER DEFAULT 0;
   DECLARE QuoteNoStart INTEGER DEFAULT 0;

   -- Select the correct salesperson

   PREPARE SalesPersonStmt FROM 'SELECT * FROM SalesPerson WHERE ID = ?';
   OPEN SalesPersonCursor USING SalesPersonID;

   -- Verify that the salesperson was found, and then alter the tables to
   -- ensure the proper seed value for the identity columns

   IF ROWCOUNT(SalesPersonCursor) = 0 THEN
      RAISE ERROR CODE 10000 MESSAGE 'Invalid salesperson ID';
   ELSE
      FETCH FROM SalesPersonCursor (CustomerNoStart) INTO CustomerNoStart;
      FETCH FROM SalesPersonCursor (QuoteNoStart) INTO QuoteNoStart;

      -- Need to make sure to close SalesPerson table completely
      -- because it will have both the Customer and Quotes tables
      -- open due to the foreign key constraints

      CLOSE SalesPersonCursor;
      UNPREPARE SalesPersonStmt;

      EXECUTE IMMEDIATE 'ALTER TABLE Customer
                        ALTER COLUMN No RESTART WITH ' +
                        CAST(CustomerNoStart AS VARCHAR);

      EXECUTE IMMEDIATE 'ALTER TABLE Quotes
                        ALTER COLUMN QuoteNo RESTART WITH ' +
                        CAST(QuoteNoStart AS VARCHAR);
   END IF;

END

Sensitive and Insensitive Result Sets

---

ElevateDB supports two types of result sets - sensitive and insensitive. For a primer on sensitive and insensitive result sets, please see the Result Set Cursor Sensitivity topic in the ElevateDB SQL Manual. To request that a result set be sensitive or insensitive using the ElevateDB .NET Data Provider, you can use the EDBCommand RequestSensitive property. To find out whether the result set is actually sensitive or insensitive, you can use the EDBCommand Sensitive property. The following is an example of using both properties to control, and report, on the sensitivity of the result set:

{
   // Find the orders placed on that date
   
   EDBCommand DataCommand = new EDBCommand;

   DataCommand.ParamCheck = true;
   DataCommand.CommandText = "SELECT * FROM Orders WHERE OrderDate = :OrderDateParam";

   DataCommand.RequestSensitive = true;
   DataCommand.Prepare();
   DataCommand.Parameters["OrderDateParam"].Value = new DateTime(2008, 1, 1);

   DataCommand.Execute();

   if (DataCommand.Sensitive)
   {
      MessageBox.Show("Result set is sensitive");
   }
   else
   {
      MessageBox.Show("Result set is insensitive");
   }

   DataCommand.Dispose();
}

Progress and Status Updating

---

The ElevateDB .NET Data Provider provides functionality for reporting progress, status, and log message information back to the application during SQL execution via the EDBCommand OnProgress, OnStatusMessage, and OnLogMessage events (delegates). Each event has a specific set of arguments, each of which is derived from the System.EventArgs class. The following is an example of using the OnProgress event to display a progress bar (progressBar1) during the execution of a query:

private void button1_Click(object sender, EventArgs e)
{
   // Set up the connection and command

   EDBConnection DataConnection = new EDBConnection("Type=Remote;Database=Test; " +
                                                    "Address=127.0.0.1;Port=12010;" +
                                                    "UID=Administrator;PWD=EDBDefault");
   EDBCommand DataCommand = new EDBCommand();
   DataCommand.Connection = DataConnection;
   DataCommand.CommandText = "SELECT * FROM customer INNER JOIN Orders " +
                             "ON Customer.CustNo=Orders.CustNo " +
                             "WHERE Customer.State='FL'";
   DataConnection.Open();

   // Add the OnProgress event handler

   DataCommand.OnProgress += QueryProgress;

   DataCommand.Execute();

   DataCommand.Dispose();
   DataConnection.Dispose();
}

private void QueryProgress(object sender, EDBProgressEventArgs e)
{
   progressBar1.Value = e.PercentDone;
}

Retrieving Execution Plans

---

ElevateDB supports the retrieval of execution plans for any SQL DML statements (SELECT, INSERT, UPDATE, or DELETE). The ElevateDB .NET Data Provider exposes this functionality via the EDBCommand RequestPlan and Plan properties. The following is an example of requesting and displaying the execution plan for a simple SQL SELECT query:

{
   // Find the orders placed on that date
   
   EDBCommand DataCommand = new EDBCommand;

   DataCommand.ParamCheck = true;
   DataCommand.CommandText = "SELECT * FROM Orders WHERE OrderDate = :OrderDateParam";

   DataCommand.RequestPlan = true;
   DataCommand.Prepare();
   DataCommand.Parameters["OrderDateParam"].Value = new DateTime(2008, 1, 1);

   DataCommand.Execute();

   MessageBox.Show(DataCommand.Plan);

   DataCommand.Dispose();
}

Using the Bi-Direction EDBDataCursor Class

---

ElevateDB supports dynamic, live, navigational access to entire tables without forcing a fetch of a portion or all of the rows in the table, and in the ElevateDB .NET Data Provider, this functionality is surfaced via the EDBDataCursor class. You can review the makeup of the EDBDataCursor class in the ElevateDB Data Access Components manual link below:

EDBDataCursor Class

You create a new instance of the EDBDataCursor class by calling the EDBCommand ExecuteCursor method. ElevateDB supports instantiating bi-directional cursors on tables (EDBCommand CommandType property set to TableDirect), SQL statements and scripts (EDBCommand CommandType property set to Text), and/or stored procedures (EDBCommand CommandType property set to StoredProcedure).

The following is an example of using the various properties and methods of the EDBDataCursor class to allow for bi-directional navigation and updating of tables. This example is from a .NET Compact Framework quotes application whose interface looks like this:

public EDBConnection QuotesConnection = null;
public EDBCommand CustomerCommand = null;
public EDBDataCursor CustomerCursor = null;
public EDBCommand QuoteCommand = null;
public EDBDataCursor QuoteCursor = null;

public MainForm()
{
   InitializeComponent();

   // Create the connection with the proper connection string

   QuotesConnection = new EDBConnection(@"Type=Local;ConfigPath=\quotes;" +
                                        "Database=Configuration;" +
                                        "UID=Administrator;PWD=EDBDefault;" +
                                        @"TempTablesPath=\temp");

   // Create the customer cursor

   CustomerCommand = new EDBCommand();
   CustomerCommand.Connection = QuotesConnection;
   CustomerCommand.CommandType = CommandType.TableDirect;
   CustomerCommand.CommandText = "Customer";

   // Create the customer quotes cursor

   QuoteCommand = new EDBCommand();
   QuoteCommand.Connection = QuotesConnection;
   QuoteCommand.CommandType = CommandType.TableDirect;
   QuoteCommand.CommandText = "Quotes";
}

~MainForm()
{
   // Close all of the cursors and dispose of all command and connection objects

   CloseCursors();

   if (QuoteCommand != null)
   {
       QuoteCommand.Dispose();
       QuoteCommand = null;
   }

   if (CustomerCommand != null)
   {
       CustomerCommand.Dispose();
       CustomerCommand = null;
   }

   if (QuotesConnection != null)
   {
       QuotesConnection.Dispose();
       QuotesConnection = null;
   }
}

private void OpenCursors()
{
   // Open the cursors and assign the event handlers for updating the display

   CustomerCursor = CustomerCommand.ExecuteCursor();
   CustomerCursor.OnMove += CustomerCursor_Move;
   CustomerCursor.OnStateChange += CustomerCursor_StateChange;

   QuoteCursor = QuoteCommand.ExecuteCursor();
   QuoteCursor.OnMove += QuoteCursor_Move;
   QuoteCursor.OnStateChange += QuoteCursor_StateChange;

   CustomerCursor.ReadFirst();
}
        
private void CloseCursors()
{
   // Close and dispose of all of the cursor objects

   if (QuoteCursor != null)
   {
       QuoteCursor.Dispose();
       QuoteCursor = null;
   }

   if (CustomerCursor != null)
   {
       CustomerCursor.Dispose();
       CustomerCursor = null;
   }
}

private void LoadCustomer()
{
   // Display the customer information

   CustomerNoTextBox.Text = CustomerCursor.GetString(0);
   CustomerNameTextBox.Text = CustomerCursor.GetString(1);
   Address1TextBox.Text = CustomerCursor.GetString(2);
   Address2TextBox.Text = CustomerCursor.GetString(3);
   CityTextBox.Text = CustomerCursor.GetString(4);
   StateTextBox.Text = CustomerCursor.GetString(5);
   ZipTextBox.Text = CustomerCursor.GetString(6);
   CountryTextBox.Text = CustomerCursor.GetString(7);
}

private void SaveCustomer()
{
   // Save all customer information into the current row

   CustomerCursor.SetString(1, CustomerNameTextBox.Text);
   CustomerCursor.SetString(2, Address1TextBox.Text);
   CustomerCursor.SetString(3, Address2TextBox.Text);
   CustomerCursor.SetString(4, CityTextBox.Text);
   CustomerCursor.SetString(5, StateTextBox.Text);
   CustomerCursor.SetString(6, ZipTextBox.Text);
   CustomerCursor.SetString(7, CountryTextBox.Text);

   if (CustomerCursor.State == EDBDataCursorState.Inserting)
   {
       CustomerCursor.SetInt32(8, SalesPersonID);
   }
}

private void UpdateCustomerButtons()
{
   // Update the navigation and editing buttons

   if (CustomerCursor.State == EDBDataCursorState.Browsing)
   {
       FirstCustomerButton.Visible = true;
       PreviousCustomerButton.Visible = true;
       NextCustomerButton.Visible = true;
       LastCustomerButton.Visible = true;
       InsertCustomerButton.Visible = true;
       DeleteCustomerButton.Visible = true;
       SaveCustomerButton.Visible = false;
       CancelCustomerButton.Visible = false;

       if (CustomerCursor.BOF)
       {
           FirstCustomerButton.Enabled = false;
           PreviousCustomerButton.Enabled = false;

           if (CustomerCursor.EOF)
           {
               NextCustomerButton.Enabled = false;
               LastCustomerButton.Enabled = false;
               DeleteCustomerButton.Enabled = false;
           }
           else
           {
               NextCustomerButton.Enabled = true;
               LastCustomerButton.Enabled = true;
               DeleteCustomerButton.Enabled = true;
           }
       }
       else if (CustomerCursor.EOF)
       {
           NextCustomerButton.Enabled = false;
           LastCustomerButton.Enabled = false;

           if (CustomerCursor.BOF)
           {
               FirstCustomerButton.Enabled = false;
               PreviousCustomerButton.Enabled = false;
               DeleteCustomerButton.Enabled = false;
           }
           else
           {
               FirstCustomerButton.Enabled = true;
               PreviousCustomerButton.Enabled = true;
               DeleteCustomerButton.Enabled = true;
           }
       }
       else
       {
           FirstCustomerButton.Enabled = true;
           PreviousCustomerButton.Enabled = true;
           NextCustomerButton.Enabled = true;
           LastCustomerButton.Enabled = true;
           DeleteCustomerButton.Enabled = true;
       }
   }
   else if (CustomerCursor.State == EDBDataCursorState.Inserting ||
            CustomerCursor.State == EDBDataCursorState.Updating)
   {
       FirstCustomerButton.Visible = false;
       PreviousCustomerButton.Visible = false;
       NextCustomerButton.Visible = false;
       LastCustomerButton.Visible = false;
       InsertCustomerButton.Visible = false;
       DeleteCustomerButton.Visible = false;
       SaveCustomerButton.Visible = true;
       CancelCustomerButton.Visible = true;
   }
}

private void UpdateCustomerRowCount()
{
   // Update the displayed row count

   if (CustomerCursor.State == EDBDataCursorState.Inserting)
   {
       CustomerRowCountLabel.Text = "Inserting";
   }
   else if (CustomerCursor.State == EDBDataCursorState.Updating)
   {
       CustomerRowCountLabel.Text = "Updating";
   }
   else
   {
       CustomerRowCountLabel.Text = "Browsing (" +
            Convert.ToString(CustomerCursor.RowCount) + ")";
   }
}

private void FilterQuotes()
{
   // Reset the filter on the quotes cursor

   QuoteCursor.Filter = "CustomerNo = " + CustomerCursor.GetString(0);
   QuoteCursor.Filtered = true;
   QuoteCursor.ReadFirst();
}

private void CustomerCursor_Move(object sender)
{
   LoadCustomer();
   UpdateCustomerButtons();
   UpdateCustomerRowCount();
   FilterQuotes();
}

private void CustomerCursor_StateChange(object sender)
{
   LoadCustomer();
   UpdateCustomerButtons();
   UpdateCustomerRowCount();
   FilterQuotes();
}

private void FirstCustomerButton_Click(object sender, EventArgs e)
{
   CustomerCursor.ReadFirst();
}

private void PreviousCustomerButton_Click(object sender, EventArgs e)
{
   CustomerCursor.ReadPrevious();
}

private void NextCustomerButton_Click(object sender, EventArgs e)
{
   CustomerCursor.Read();
}

private void LastCustomerButton_Click(object sender, EventArgs e)
{
   CustomerCursor.ReadLast();
}

private void InsertCustomerButton_Click(object sender, EventArgs e)
{
   CustomerCursor.Insert();
}

private void DeleteCustomerButton_Click(object sender, EventArgs e)
{
   if (MessageBox.Show("Delete this customer ?", "Confirm Deletion",
       MessageBoxButtons.YesNo, MessageBoxIcon.Question,
       MessageBoxDefaultButton.Button2) == DialogResult.Yes)
   {
       try
       {
       CustomerCursor.Delete();
       }
       catch (Exception Error)
       {
           MessageBox.Show(Error.Message, "Error", MessageBoxButtons.OK,
                           MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
       }
   }
}

private void CancelCustomerButton_Click(object sender, EventArgs e)
{
   CustomerCursor.Cancel();
}

private void SaveCustomerButton_Click(object sender, EventArgs e)
{
   SaveCustomer();
   CustomerCursor.Post();
}

private void CustomerNameTextBox_KeyPress(object sender, KeyPressEventArgs e)
{
   // Automatically put the cursor into an insert or update state when
   // any key is pressed in an edit box (assigned to all edit boxes)

   if (CustomerCursor.State == EDBDataCursorState.Browsing)
   {
       if (CustomerCursor.RowCount == 0)
       {
           CustomerCursor.Insert();
       }
       else
       {
           CustomerCursor.Update();
       }
   }
}

Summary

---

The ElevateDB .NET Provider offers a lot of very useful functionality that will make application development much easier and allow you to concentrate on the core functionality instead of fighting limitations of your data provider and/or database engine. As always, if you have any questions at all, please feel free to post them in the ElevateDB newsgroup here:

ElevateDB Connectivity Support Forum

This will allow everyone to share in any new ideas or comments that you may have regarding this article.

The next technical article will be entitled "Building a Windows CE Application with Lazarus and ElevateDB", so look for it soon.