Introduction to Vanatec OpenAccess

David Foderick

Net Knowledge, Inc.

Introduction

 

Persisting a rich object model demands the services of a smart data access layer. ADO.NET, the traditional data access mechanism for .NET, leaves too much to be desired when working with class hierarchies and interconnected objects with abundant associations. Developers who require a smart data access layer need look no further than OpenAccess to provide persistence services for rapidly building well-architected object-oriented applications. This article looks at the main features of Open Access .NET and provides an introduction on how to install and get started using the product.

 

Microsoft recognized the deficiencies of ADO.NET and had been working on the ObjectSpaces project. The strictly relational view of data consisting of columns and rows exposed by the DataSet falls short of the requirements for creating an object-oriented application. Instead of a purely relational view of data, ObjectSpaces was designed to provide the necessary persistence services for working with complex objects. Unfortunately, ObjectSpaces has been cancelled, which makes the case for Vanatec. Based on their long history of providing object persistence for Java and .NET, they provide that expertise to .NET with Vanatec Open Access, an object relational mapping tool and framework.

 

OpenAccess delivers valuable persistence services to your custom business objects allowing the developer to focus on their application’s unique business logic. These low-level persistence services consist of retrieving objects from storage, tracking changes to your objects and saving those changes back to the database. The custom business objects form an object model where business logic can be written in terms of the properties and methods of objects in the object model instead using an arcane SQL syntax. OpenAccess handles the mechanics of translating changes to your objects at runtime to SQL statements that get executed against the database. Moreover, you are free to develop an object model independent from your relational model; Open Access will translate between those two models. Eliminating the mismatch between an object model and a relational model increases developer productivity significantly.

 

So what makes OpenAccess so smart when working with your objects? With OpenAccess you provide metadata about your business objects in the form of .NET attributes. For example, you mark your business objects that get stored to a data table as persistent by adding a Persistent attribute. You also describe inheritance relationships and associations between your domain classes using .NET attributes. In the following example, the Order class uses the Persistent attribute to tell OpenAccess that instances of Order will be persistent, that is, they will be stored in the database in between sessions of the application. By default all fields are Persistent unless marked with the Transient attribute.

 

 [Persistent]

public class Order

{

       private IList<OrderItem> orderItems = new List<OrderItem>();

       private DateTime shippingDate;

       private string customer;

 

       public string Customer

       {

              get { return customer; }

              set { customer = value; }

       }

 

       public DateTime ShippingDate
       {

              get { return shippingDate; }

              set { shippingDate = value; }

       }

      

       public IList<OrderItem> OrderItems

       {

              get { return orderItems; }

       }

}

 

 

Listing 1

 

Notice that there is no data access logic inside your business class. Maintaining a clean separation of concerns between business logic and data access code makes it easier to focus on your business logic. Let OpenAccess handle the messy details. As an added benefit, there is less code to maintain.

 

Armed with this metadata that describes your objects, OpenAccess can now work its magic. First, you can query for persistent objects using a convenient Object Query Language (OQL) in terms of your business objects instead of writing SQL queries. Open Access will translate the object query into an SQL query on the backend database and will return to your application fully formed graphs, which are interconnected networks, of domain objects, for example Orders and OrderDetails. Furthermore, as your application modifies the state of these persistent objects, OpenAccess is invisibly tracking the changes you have made. On your command, OpenAccess will write the changes back to persistent storage without you having to write a single SQL statement. As it writes the changes, it also intelligently handles object relationships. For example, when saving an order it will be sure to save the order header and order detail as one atomic transaction while maintaining the foreign key relationship between the tables. What enables this productivity is the intelligence in which OpenAccess can handle your custom objects through the knowledge that it has of your object model’s metadata. This is smart data access!

 

Listing 2 is a short example of Open Access code that shows its agility in retrieving and updating the Order persistent object.

 

using (IObjectScope scope = Database.Get(connectionId).GetObjectScope())
{
    scope.Transaction.Begin();
    string query = "SELECT * FROM OrderExtent";
    IQueryResult result =
        scope.GetOqlQuery(query).Execute();
    foreach(Order o in result)
    {
        Console.WriteLine(o);
         foreach (OrderDetail d in o.details)
         {
                 d.Qty = 1;
         }
         scope.Update(o);
    }
    scope.Transaction.Commit();
} // scope is disposed here
 

 

Listing 2

 

Let’s analyze this code. The C# using statement wraps an object that contains an expensive resource and ensures that the object is properly disposed when we are done working with it, thereby releasing the resource. The object we are using in this case is an instance of ObjectScope that is conceptually our connection to the database. Next, we begin a transaction. The transaction boundaries delineate a unit of work. Inside the unit of work, an OQL query is constructed to return all orders from the OrderExtent. An extent consists of all instances of a persistent class, in this case Orders. The results are retrieved with the Execute method and then iterated over in the foreach statement. Note that Open Access knows how to construct our domain objects from persistent storage and allows us to work with our custom objects directly from the query. Also note how OrderDetail objects are retrieved automatically without any effort on our part. We can make changes to our domain objects as we do with the OrderDetail object in this example.  As long as the change takes place within a transaction, Open Access will track the change in the persistent object and will persist the changes to the data store on commit. The C# using statement will ensure that the Dispose method is called on the ObjectScope instance thus closing the connection to the database.

Beyond the basics

More than just basic object persistence, OpenAccess also provides many advanced features for building enterprise capable applications.

 

 

Once you have familiarized yourself with the basics presented in this document, you can explore the full array of features. The rest of this article will walk through everything you need to install and use the product.

Downloading and Installing the Demo

OpenAccess is available for download at http://www.vanatec.com/en/community-support/download. You will need to register yourself and enter your email address so that a license key can be sent to you. You can choose between a key for the free Express Version (limited to free databases) or the Evaluation Version to test against commercial databases.

 

To Run OpenAccess and the samples provided, you need:

 

·         Visual Studio.Net 2005

·         Additional for the VOA Express Version one of the following database servers:

o         Microsoft SQL Server 2005 Express

o         Oracle 10g Express and Oracle Data Provider for .NET

o         MySQL 5.0.19 or higher

·         Additional for the Evaluation Version one of the following database servers:

·         Microsoft SQL Server 2000

·         Microsoft SQL Server 2000 Desktop Engine

·         Microsoft SQL Server 2005

·         Microsoft SQL Server 2005 Express

·         Oracle Server release 9.2 or higher
and Oracle Data Provider for .NET 9.2.0.4

·         MySQL 5.0.19 or higher

·         Advantage Database Server (ADS) 8.1

 

Installing OpenAccess is simple. You run the setup.exe from the .zip download. Attached to the email you receive from Vanatec will be a key file. After installing OpenAccess you can activate OpenAccess from inside your Visual Studio by selecting "OpenAccess | License Info" from the Visual Studio menu.

 

With the installation package you get the following features installed.

 

 

An overview of the product as well as last minute configuration information is available through the Windows Program Start -> Vanatec OpenAccess menu called “Welcome to Open Access”.

 

To make sure everything is installed and working correctly start Visual Studio and open First.sln in <installation path>\examples\dotnet20\CSharp\console\First. Press the F5 key to run the project. Successful execution of this simple example will display a console screen similar to the Figure 1.

 

Figure 1

 

If you get an exception instead of the expected output, please check, if the SQL login have permissions to create a database. Check that the server name, user name and password are set correctly in the app.config file. The SQL Server user account must have permission to create and alter tables on the server. You must specify the "dbcreator" server role for the user account.

 

Now that you are able to run the samples, let’s take a tour of the goodies installed with OpenAccess.

Tour of the IDE

To give you the most productive environment possible, OpenAccess includes an AddIn for Visual Studio that automates several mundane tasks. First, notice that you now have a new menu called OpenAccess.

 

Figure 2

 

The purpose of each menu option is described below.

 

Enable Project

Enables a project to be used with OpenAccess. Adds project references to the OpenAccess DLLs and enables the post build enhancement step. Adds or modifies the project app.config to include configuration settings specific to OpenAccess.

Create Database

Creates a Database in the data store configured in the app.config file. The database login must have permission to create a database.
The database creation step is normally done automatic within the build of the solution.

Update Database

Synchronizes your table schema with the persistent classes. This option reflects over the persistent classes then creates and runs the DDL necessary to make the database appropriate for storing persistent objects.

Metadata Properties

Shows the OpenAccess Mapping Properties of the selected element

Backend Configuration

This dialog enables the user to specify values for various global settings, such as caching or logging levels etc.

Check Settings

This dialog checks and resolves conflicted settings within projects. It checks for things, such as, project not enabled, or an OpenAccess Version conflict, etc.

OQL Query Browser

An OQL Query Browser, which enables you to parse and execute your

OQL queries and get its corresponding SQL statements and results.

Reverse Engineering

Creates persistent objects with mapping information’s from an existing database with the help of a GUI tool.

Add DataForm

Creates a Form with a DataGrid, Save and Cancel Button

Add IObjectContext
Provider

Opens the Object Context Provider Wizard which implements the IObjectContextProvider interface. An ObjectContext provides you with a helper structure, which enables you to connect to a database.

Update Config References

The Update Config References updates the App.config file on your machine.

License Info

The License Info allows you to view and change your license information.

Version Info

Displays the version number of the Open Access .NET DLLs

 

You can also set properties on your persistent classes and fields with the OpenAccess Properties context menu. Switch to Class View, drill down into your namespace hierarchy, right click on a persistent class (such as MyClass from the First project) and select OpenAccess Properties at the bottom of the context menu.

 

 

Figure 3

 

You will get a screen similar to the following.

 

Figure 4

 

The Properties screen in Figure 4 allows you to change the name of the table where persistent objects are saved and has other advanced mapping options that are beyond the scope of this article.

 

To aid in designing your forms, there are also visual components for data binding.

 

 

Figure 5

 

In the following sections we will make full use of these tools as well as several wizards to quickly and efficiently create a sample solution.

Your first project using OpenAccess

To see these tools in action, let’s create a new project that uses a Windows form to modify business objects in two DataGridViews. As you work though this sample, keep in mind what we will accomplish.

 

·         Step 1
Create a project to hold your business objects and enable it to work with OpenAccess.

 

·         Step 2
Create another project to hold your UI. The UI project will need a reference to the OpenAccess DLL and an app.config file but doesn’t need to run the post build enhancement process since it does not contain persistent objects.

 

·         Step 3
Set a reference from the UI project to the business object project and build the User Interface that will query, modify and save the business objects.

 

Note the following conventions. Menu options and buttons are bolded. Text that you have to enter through the keyboard is in Courier font. Also, as there are usually multiple ways to complete a task through the Visual Studio UI, you can use an alternate method to complete the step if you are more comfortable doing it a different way.

 

Optionally you can also download the corresponding Visual Studio Project at http://www.vanatec.com/en/community-support/resources/OR_Mapping_FirstSteps_ExampleProject_MasterDetail.zip

 

Or just watch the demo video listed at http://www.vanatec.com/en/community-support/examples/

 

Step 1: Set up the Business Object Project

 

·         In Visual Studio, click on the File menu, click New, and then Project. Choose  Class Library and enter BusinessObjects for the name of the class library and MasterDetail for the name of the solution. Click the OK button to create the solution.

·         We start for this sample with two classes (Order and OrderItem).Right click on the BusinessObjects project in the Solution Explorer, select Add, then select New Item, select the Class template from the “Add New Item” Form.

·         Name the class “OrderItem.cs” and enter the following code into the class body:

 

public class OrderItem

{

       private int count;

       private string product;

 

       public int Count

       {

              get { return count; }

              set { count = value; }

       }

 

       public string Product

        {

              get { return product; }

              set { product = value; }

       }

}

 

 

·         Add a second class with the name “Order.cs” and enter the following code:

 

public class Order

{

       private IList<OrderItem> orderItems = new List<OrderItem>();

       private DateTime shippingDate;

       private string customer;

 

       public string Customer

       {

              get { return customer; }

              set { customer = value; }

       }

 

       public DateTime ShippingDate

       {

              get { return shippingDate; }

              set { shippingDate = value; }

       }

      

       public IList<OrderItem> OrderItems

       {

              get {  return orderItems; }

       }

}

 

 

 

·         Build your solution now to make sure you have done everything correctly up to this point.

·         The next thing we need to do with the BusinessObject project is enable it for use with OpenAccess. Click on the OpenAccess menu and select Enable Project. The following wizard guides you through the process. Click Next to get to Page 2 of the wizard:

 

 

Figure 6

 

This screen needs you to think about your project and then choose the modules that are contained within. OpenAccess makes changes and/or additions to your project based on your choices made on this page.

Since this is a project is a data model containing classes that you would want to persist to the database, so the appropriate option has been chosen by OpenAccess, and this assembly has been marked for enhancement.

 

Note: Based on your project type, OpenAccess pre-selects certain suitable options, however you can change these choice of modules, or choose more modules based on your requirements.

 

 

 

Figure 7

 

Your answers to the following prompts will determine what properties get set:

 

Connection Id

The name of the connection that is stored in the configuration file. You will use this name to refer to the connection properties in code.

Database Backend

Currently OpenAccess connects to MS SQL Server 2000 / MSDE, MS SQL Server 2005 / EXPRESS

(only in case of Visual Studio 2005), MySQL 5.0 (version 5.0.19 or higher), Oracle (version 9.2

or higher) and Advantage Database Server 8.1, i.e., your client application can connect to the MS SQL Server family, MySQL, Oracle or to ADS.

Server Name

Name of your database server, default “(local)\sqlexpress”

Database Name

Name of the database, default “Database1”

Use Integrated security

The genericADO2 and ODP drivers support integrated security.
We can use it with the MS SQL Express server

User

The user name to use to connect to SQL Server

Password

The password for the user account

 

You can test the connection from this page by clicking on the Test Connection button. If you get an ‘invalid host’ error message then the server could not be reached. If you get a ‘No Database’ error message don’t worry, because OpenAccess will create the database from your domain classes.