Simple C-Sharp Samples for VistaDB

Common C# Database Operations

The following are common simple database operations for .Net developers.  These are meant as code snippets and examples of how to use VistaDB objects, not a comprehensive tutorial on .Net database programming.

Opening a connection with VistaDBConnection

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = @"Data Source=C:\mydatabase.vdb4";

    try
    {
        connection.Open();
    }
    catch (Exception e)
    {
        throw e;
    }
}

Creating a VistaDBCommand and assigning its Connection

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = @"Data Source=C:\mydatabase.vdb4";

    try
    {
        connection.Open();

        using (VistaDBCommand command = new VistaDBCommand())
        {
            command.Connection = connection;
        }
    }
    catch (Exception e)
    {
        throw e;
    }
}

Using a VistaDBCommand to execute a T-SQL Query

using (VistaDBCommand command = new VistaDBCommand())
{
    command.Connection = connection;
    command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES ('Test')";
    command.ExecuteNonQuery();
}

Using a VistaDBCommand with Parameterized Queries

using (VistaDBCommand command = new VistaDBCommand())
{
    int Age = 21;

    command.Connection = connection;
    command.CommandText = "INSERT INTO MyTable (MyColumn) VALUES (@age)";
    command.Parameters.Add("@age", Age);
    command.ExecuteNonQuery();
}

Using a VistaDBDataAdapter to fill a DataTable

DataTable table = new DataTable();

using (VistaDBCommand command = new VistaDBCommand())
{
    command.Connection = connection;
    command.CommandText = "SELECT * FROM MyTable";

    using (VistaDBDataAdapter adapter = new VistaDBDataAdapter())
    {
        adapter.SelectCommand = command;
        adapter.Fill(table);
    }
}

Using a VistaDBConnection with GetSchema

DataTable table = new DataTable();

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = @"Data Source=C:\mydatabase.vdb4";

    try
    {
        connection.Open();

        table = connection.GetSchema("COLUMNS");
    }
    catch (Exception e)
    {
        throw e;
    }
}

Using VistaDBConnectionStringBuilder

VistaDBConnectionStringBuilder builder = new VistaDBConnectionStringBuilder();

// You can use Key based adds
builder.Add("Data Source", @"|DataDirectory|\SimpleDB.vdb4");

// Or the actual strongly typed methods on the class
builder.OpenMode = VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite;

builder.TransactionMode = VistaDBTransaction.TransactionMode.On;

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = builder.ConnectionString;

    try
    {
        connection.Open();
    }
    catch(Exception e)
    {
        throw e;
    }
}

Filling a DataSet with VistaDB and binding it to an ASP.Net GridView

DataSet myDataSet = new DataSet();

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = @"Data Source=C:\mydatabase.vdb4";

    try
    {
        connection.Open();

        using (VistaDBCommand command = new VistaDBCommand())
        {
            command.Connection = connection;
            command.CommandText = "SELECT * FROM MyTable";

            using (VistaDBDataAdapter da = new VistaDBDataAdapter())
            {
                da.SelectCommand = command;
                da.Fill(myDataSet);
            }
        }
    }
    catch (Exception e)
    {
        throw e;
    }
}

MyGridView.DataSource = myDataSet;
MyGridView.DataBind();

Using a VistaDBCommand to execute a stored procedure

using( VistaDB.Provider.VistaDBCommand command = new VistaDB.Provider.VistaDBCommand(
"CalcSalesTax", connection) )
{
   command.CommandType = CommandType.StoredProcedure;

   VistaDBParameter param = command.Parameters.AddWithValue("@SalesTotal", 100.00);
   param.Direction = ParameterDirection.Input;
   param.DbType = System.Data.DbType.Currency;

   VistaDBParameter outparam = command.Parameters.Add("@OrderTotal", VistaDBType.Money);
   outparam.Direction = ParameterDirection.Output;
   
   command.ExecuteNonQuery();
   Assert.AreEqual(outparam.Value, 106.00);
}

Using VistaDBCommandBuilder and InsertCommand

using (VistaDBConnection connection = new VistaDBConnection())
{
    connection.ConnectionString = "Data Source='|DataDirectory|database.vdb4'";

    try
    {
        connection.Open();

        VistaDBDataAdapter da = new VistaDBDataAdapter("SELECT
            * FROM users", connection);

        using (VistaDBCommandBuilder builder = new VistaDBCommandBuilder(da))
        {
            //VistaDBCommandBuilder allows me to get a pre built insert command
                for the table
            //that my VistaDBDataAdapter is referencing
            using (VistaDBCommand command = builder.GetInsertCommand(true))
            {
                //The insert command i get back will look something like
                //"INSERT INTO users (Name, Email, ...) VALUES (@Name, @Email, ...)
                //Now all i have to do is add the values i want to my insert command
                //through the parameters given to me.
                command.Parameters["@Name"].Value = name;
                command.Parameters["@Email"].Value = email;
                command.Parameters["@Website"].Value = url;
                command.Parameters["@LastLogin"].Value = null;
                command.Parameters["@JoinedDate"].Value = DateTime.Now;

                command.ExecuteNonQuery();
            }
        }
    }
    catch (Exception e)
    {
        throw e;
    }
}