VistaDB 3.0: Size doesn’t matter

By Mahesh Chand May 03 2007
VistaDB 3.0 is the world's first fully managed and typesafe embedded SQL database engine for Microsoft .NET Framework, Compact Framework and Mono . Developed entirely in fully managed and typesafe C#, VistaDB 3 lets developers build managed ASP.NET and WinForms database applications that run on desktops, mobile devices and web servers.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 11.5k
  • 0

VistaDB 3.0 is the world's first fully managed and typesafe embedded SQL database engine for Microsoft .NET Framework, Compact Framework and Mono (.NET for Linux, Mac OS X and Solaris). Developed entirely in fully managed and typesafe C#, VistaDB 3 lets developers build managed ASP.NET and WinForms database applications that run on desktops, mobile devices and web servers.

Key features

  • Fully managed and verifiably typesafe embedded SQL database for .NET
  • Small 600KB footprint with truly embedded ZeroClick™ deployment 9
  • C# architecture built exclusively for .NET, Compact Framework and Mono 
  • Microsoft SQL Server 2005 compatible data types and T-SQL syntax 
  • Royalty-free distribution 
  • Single-file assembly engine and single-file data store
  • CLR Procs™, CLR Triggers, and Views
  • Transaction Processing
  • Managed Data Migration Wizard, managed Data Builder tool 
  • Free 30 days trial offer
  • No CPU or RAM Limitations

Why VistaDB?

As a hard-core programmer, I have always been a squirrel of system resources. We all are familiar with SQL Server is a pig when it comes to the system resources running on a development machine and I really don’t want to talk about Oracle. Oracle is definitely out of league of programmers.

At Kruse Inc., the company I worked for in my early days, we had a product called kWise. The product was a client-server application and master database was SQL Server and local database was MS-Access. We had to deploy a local copy of database on each client machine. We were using MS-Access and that choice was just fantabulous. No installation, no extra resources, just a simple .mdb file in KBs.
However, there were many limitations of MS-Access and one of them was no stored procedures. All database access layer code had to be embedded SQL queries and for SQL Server we were using stored procedures. So we had some redundant code.

Here comes VistaDB, which is in size is like MS-Access and features like SQL Server.

Installing VistaDB

Installation of VistaDB is pretty straight-forward. Go to www.vistadb.com and download a free trial copy and run the installation.

Create a Database

Let’s see how to create a database and add some data to it. Select Data Builder menu item from the VistaDB menu added to your machine. It will open VistaDB Data Builder (Figure 1), here you can create database and database items including tables, views, stored procedures, permissions and so on. 


Figure 1. VistaDB Data Builder

Select New Database from File menu to create a new database. The next screen pops up is Create Database dialog, which asks you database name, description, path, password, and other properties. See Figure 2.

 
Figure 2. Create Database dialog

I call my database mcb and after creation, the database properties look like Figure 3.

 
Figure 3. Database properties

After a database is created, now you can add tables, views, stored procedures, and other properties. Click on Table and right click on New Table to create a new table. It will open Create Table dialog. You may select Alter Table to change the table’s schema. The Column Details tab let you create, update, and delete table columns. See Figure 4. 

 
Figure 4. Create and alter table dialog

I add four columns – ID, Name, Email, and Password columns to the table and final table schema looks like Figure 5. 

 
Figure 5. Users table columns

Now clicking on Data menu item on the Table menu lets you add, update, and delete data. I add some data to the table as shown in Figure 6. 

 
Figure 6. Users table data

I like how Query option is right there because this is the option I use most of the time. Selecting Query menu items opens the Query window, where you can type your query. I type my SELECT query and click Execute Query button and it shows me the results. See Figure 7.


Figure 7. Executing a query

The Query tool also allows you to save and load queries.

So this was a preview of how simple is it to create a database, its objects, and data. The size of this database is 18KB only.

Access VistaDB from a C# Application

Now I will create a Windows Forms application to access VistaDB database.

I create a Windows Forms application and add a DataGrid control to the Form. Before we can access VistaDB library, we have to add a reference to the assembly. Installation program of VistaDB deploys assembly to the GAC. When you right click on the project in Solution Explorer and select Add Reference -> .NET, you will see VistaDB.Net Provider in the list. See Figure 8.

 
Figure 8. VistaDB.Net Provider reference

After adding reference, now VistaDB classes are available to use after importing these two namespace in the code:

using VistaDB;
using VistaDB.Provider;

I declare following two variables for connection string and SQL statement respectively:

private string connectionString = @"Data Source=C:\temp\mcb.vdb3";

private string sql = "SELECT * FROM Users";

Now on the Form’s load event, I write the following code:

// Create a connection

VistaDBConnection conn = new VistaDBConnection(connectionString);

// Create an adapter with SQL Statement

VistaDBDataAdapter adapter = new VistaDBDataAdapter(sql, conn);

DataSet ds = new DataSet();

// Open connection

conn.Open();

// Fill data in a DataSet                                  

adapter.Fill(ds);

// Bind to the DataGrid

dataGrid1.DataSource = ds.Tables[0];

// Close connection                

conn.Close();

And run the application. Output looks like Figure 9.


 
Figure 9. Data display from a VistaDB database

VS 2005 Server Explorer Access

You won’t believe how often I use Server Explorer tool of Visual Studio 2005. I hardly go to a database. I rather do all my view, add, update, and database management operations from Server Explorer.

Guess what? VistaDB provides Server Explorer accessibility. It lets you build visual queries using the VS 2005 environment natively.  The ability to create visual queries inside the VS environment makes VistaDB feel like any other database.

The Verdict

If you are looking for a lightweight database which is easy to deploy on target servers and client machines and want to avoid licensing issue of SQL Server, VistaDB perhaps may be a good choice for you. Being a small company, VistaDB team is also ready to work with you on new feature requests and feedback. Visit www.vistadb.com for a 30 days free trial download.