ODS v2.2.1 DAL Designer

Source code

Creating a Database

Exploring the automatically generated DAL source code

Using your DAL to create a database

To demonstrate how to create a database from your DAL you will need to generate a DAL using ODS Design Studio. We've created a new ODS solution named "DemoDAL" with just the default class. We then created a demo application in Visual Studio 2005 that will use DemoDAL.

Referencing the DAL

The first thing to do is reference the generated DAL from your application. You can reference the compiled DAL source code (either compiled automatically by ODS or by yourself). Alternatively, you can add the DAL VS2005 project (generated by ODS) to your VS2005 solution.

You'll need to add a "using" statement to your code. Use the namespace that you selected in the new solution wizard. The namespace can be viewed and edited using the solution properties.

Data store providers

Now that we have a DAL, we'll need to get it to create a database that we can store our objects in. To do this you'll need to choose a data store provider. Each data store provider talks to a different type of database.

When we generated our DAL in ODS Design Studio, we checked all four database plug-ins so the following data store provider classes have been generated:

SQLServerDataStoreProvider
MySQLDataStoreProvider
MSJet4DataStoreProvider
SQLite3DataStoreProvider

Although we will initially create an instance of one of the providers, none of our code will access it directly. Instead, we will access it through the data store provider interface, IDemoDALDataStoreProvider. Since all data store providers implement the IDemoDALDataStoreProvider interface, our code will work with any of the data store providers and therefore any database type.

Initialisation Strings

When we create a new instance of a data store provider we must provide an initialisation string to tell it which specific database to deal with. Each data store provider expects a different initialisation string which can either be a connection string or an ODS XML initialisation string. For more information about initialisation strings see the data store providers and the MS Jet 4, MySQL, SQL Server and SQLite 3 pages.

Creating a data store provider

In our example we will use the SQLServerDataStoreProvider data store provider to create a SQL Server 2000 database. We will create a new instance of the data store provider using the following code:


                        // A SQL Server connection string for the demo database

                        string connectionString = "data source=mySQLServer;" +

                            "initial catalog=demo; integrated security=true;";

                        

                        // Create a data store provider object

                        IDemoDALDataStoreProvider dataStore = new

                            SQLServerDataStoreProvider(connectionString);

The IDemoDALDataStoreProvider object that we've created acts as a proxy to the database specified in the initialisation string. We will use it throughout our code to tell the DAL which database to target.

Creating or upgrading a database

Creating a new blank database is achieved very easily with a single line of code:


                    // Create a new database

                    dataStore.Create();

However, in many cases you might consider using the following code instead:


                        // Does the database already exist?

                        if (dataStore.Exists)

                        {

                            // The database already exists.  Make sure the schema is

                            // upto-date

                            dataStore.Upgrade();

                        }

                        else

                        {

                            // The database does not exist.  Create a new database

                            dataStore.Create();

                        }

The code firstly checks to see if the database pointed to by the dataStore object already exists. If the database already exists then we tell the DAL to upgrade it. Otherwise, we tell the DAL to create a new, blank database. You can safely run the upgrade method as often as you like against a database that is already up to date.

Note that for database creation to work, the user that you specify in the initialisation string must have the correct permissions. For example, in the case of SQL Server the user must have access the Master database and in SQL server 2005 the user must also have the "create database" permission on the Master database.