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;
     }
}