Sample of VistaDB Code

What does VistaDB code look like?  We have two different API's you can use to write your code.  One is the standard ADO.NET 2 provider syntax (SQL), and the other is Direct Data Access (DDA).

ADO.Net Provider - For users who are used to programming against SQL Server and are looking for a serverless alternative.

DDA Provider - For users who are new to database programming, or coming from an XBase (Apollo, FoxPro) background.  DDA is an easy to use cursor based programming model.

Please view the tutorials page for more examples of both programming APIs. Also feel free to signup for the free 30 day trial and take a look at the NUnit tests we include, and sample applications.

Samples Included

Over 30 sample applications are included with the free trial showing how to use both DDA and ADO.NET objects.  Plus over 600 unit tests are included that are a great way to find task based solutions on how to program for VistaDB.

  ADO.NET Data Provider

The built-in VistaDB.ADO.NET Provider is fully compatible with ADO.NET 2.0. It provides full support for disconnected SQL-based data management, exactly like the SqlClient Provider. The VistaDB ADO.NET Provider components include VistaDBConnection, VistaDBDataAdapter, VistaDBDataReader, VistaDBCommand and each work with databound controls and 3rd party products that support ADO.NET managed provider factories.

If you are familiar with programming against SQL Server this is probably the API you should use.  The SQL language was designed to make expressive queries and set manipulation possible.  This is the interface we recommend to most users.  You will find many more examples of ADO.Net code and will be the only way to work with the client / server editions of VistaDB in the future.

C# sample using the VistaDB ADO.NET Data Provider

The key thing to recognize is that we use the same objects as SqlClient, but renamed with VistaDB.  This makes porting code very easy.

public static bool GetExistingAccountID( string Login, string Password, out int accountID )
{
    try
    {
        string connstring = ConnectionStrings["VDBSiteSQLDatabase"].ConnectionString;
        using( VistaDBConnection conn = new VistaDBConnection( connstring ) )
        {
            conn.Open();
            string SQL = "SELECT accountid from accounts
                where login = @p1 and locked = 0";

            if( Password != null )
                SQL += " and pwd = @p2";

            using( VistaDBCommand cmd = new VistaDBCommand(SQL, conn) )
            {
                cmd.Parameters.AddWithValue("@p1", Login);
                
                if( Password != null )
                    cmd.Parameters.AddWithValue("@p2", Password);

                object result = cmd.ExecuteScalar();

                if( result == null )
                {
                    accountID = -1;
                    return (false);
                }

                accountID = (int)result;
                return (true);
            }

        }
    }
    catch (System.Exception)
    {
        accountID = -1;
        return (false);
    }
}

  Direct Data Access (DDA)

Direct Data Access objects or DDA is unique to VistaDB. DDA consists of 3 .NET objects: VistaDBDatabase, VistaDBTable and the VistaDBDataSet that give you total control over a .VDB3 database. These classes provide an intuitive, yet powerful object-oriented method of managing data, sometimes referred to as "live cursors", "scrollable cursors" or "navigational control". DDA lets you navigate dynamically through data tables without having to load the entire table or dataset into memory. DDA provides more performance over ADO.NET's disconnected data model. The VistaDBDataSet allows DDA to be fully compatible with all databound controls.

You are new to database programming, or coming from another cursor based database environment this is probably the API you should use.  We also find it great for quick utilities that you want a minimum of code and quick linear access to data.  It is not well suited to dynamic or complex queries (that is what SQL is good at doing and we recommend the ADO.NET interface).

C# sample using the VistaDB DDA objects

private void AddRecordsDDA()
{
    try
    {
        // Open .VDB database file
        IVistaDBDatabase db =
            DDA.OpenDatabase(this.databaseName, 
            VistaDBDatabaseOpenMode.ExclusiveReadWrite, null);

        IVistaDBTable customerTable = db.OpenTable(this.customerTableName, true, false);
        IVistaDBTable ordersTable = db.OpenTable(this.ordersTableName, true, false);

        try
        {
            StartTimer("Adding 5,000 records to tables...");

            // add 1,000 customer records

            for (int i = 0; i < 1000; i++)
            {
                customerTable.Insert();

                string str = i.ToString();

                customerTable.PutString("First_Name", str + "
                    as string");
                customerTable.PutString("Last_Name", str + "
                    as char");
                customerTable.PutInt32("Age", i);
                customerTable.PutString("State", "CA");
                customerTable.PutString("Zip", "zip" + str);
                customerTable.PutString("Description", i.ToString() + " This is text");
                customerTable.PutDateTime("Input_Date", DateTime.Today);

                customerTable.Post();

                long custID = (long)customerTable.Get("ID_Customer").Value;

                // add 5,000 records (5 for each Customer)
                for (int j = 0; j < 5; j++)
                {
                    ordersTable.Insert();

                    // "ID_Orders" is an identity, so engine increments automatically

                    ordersTable.PutInt64("ID_Customer", custID);
                    ordersTable.PutString("Name", str);
                    ordersTable.PutInt32("Quantity", j);

                    ordersTable.Post();
                };
            }
        }
        finally
        {
            EndTimer();

            // Close tables and database
            db.Close();
        }
    }
    catch (VistaDBException ex)
    {
        MessageBox.Show(VdbException + ex.Message);
    }
    catch (Exception ex)
    {
        MessageBox.Show(NetException + ex.Message);
    }
    finally
    {
    }
}