AdFactum ObjectMapper .NET Blog

Official blog of the AdFactum ObjectMapper .NET

Tutorial 2: Setting up the database, store and query some data

Posted by Gerhard Stephan on May 31st, 2006

This tutorial shows how to execute an SQL file in order to create the tables. Furthermore it shows a simple example how the query API works and how to store data. But first things first.

In Tutorial 2 I decided to use NUnit instead of a console application. The advantage of NUnit is, that we can build different test cases in order to show different aspects of the ObjectMapper .NET. So If you load the Tutorial 2 you have to setup NUnit correctly in order to run the examples.

Project Configuration

Before we can insert data, it’s necessary to create a new database. In the tutorial we are going to create a new Microsoft Access Database by copying an empty database and than executing the DDL file.

            public void CreateAccessDatabase()

            {

                  /*

                   * Copy the template database as the new database

                   */

                  File.Copy(@"..\..\emptyAccessDb.mdb", "accessDb.mdb", true);

 

                  /*

                   * Create the schema

                   */

                  new ExportSchema().ExportAccessDDL();

 

                  /*

                   * Open the access db and execute the created script file

                   */

                  AccessPersister persister = GetAccessPersister();

                  SqlFile file = new SqlFile("Marketplace.access.sql");

                  file.ExecuteScript(persister);

                  file.Dispose();

                  persister.Dispose();

            }

 Now we created a working database – with all relations a database must have.

Tutorial02 - E/R Model

In our example we want to add a new user to the database. Looking at the source code of the User entity you see that the "Logon" property is defined as unique. So we have to care for duplicate values when storing new users.

            /// <summary>

            /// Gets or sets the logon.

            /// </summary>

            /// <value>The logon.</value>

            [PropertyLength(32)]

            [Required]

            [Unique]

            public string Logon

            {

                  get { return logon; }

                  set { logon = value; }

            }

In order to prevent a sql exception because of duplicated logon values, we have to check if a logon already exists and then to cancel the save operation. Furthermore this has only to be checked if we want to add new users to database. Existing Users don’t need to be checked, because they are only getting updated.

As you can see within the following code, every entity has a field named IsNew which is set by the ObjectMapper .NET. This field defines if a entity has already been stored to database. Our first query condition is a check to the Logon property of the User entity. So we are going to count all users with the specified logon column. If the count is greater than 0, there has already been stored a user entity to database.  

            /// <summary>

            /// Creates the user.

            /// </summary>

            /// <param name="mapper">The mapper.</param>

            /// <param name="user">The user.</param>

            /// <returns>True, if the user could be stored</returns>

            private bool StoreUser (ObjectMapper mapper, User user)

            {

                  /*

                   * If the user is new, check if the logon name does already exists

                   * If the logon name does exist, return false

                   */

                  if (user.IsNew)

                  {

                        ICondition logonCondition = new AndCondition(

                             typeof(User),    

                             "Logon",

                              QueryOperator.Like_NoCaseSensitive,

                             user.Logon);

                       

                        int count = mapper.Count(typeof(User), logonCondition);

                        if (count > 0)

                             return false;

                  }

 

                  /*

                   * if the logon name is not used or it is an existing user, than store the user

                   */

                  mapper.Save(user);

 

                  return true;

            }

The outcoming SQL looks like :

SELECT COUNT([USERS].[ID]) FROM [USERS] WHERE UCASE(TRIM([USERS].[LOGON])) like UCASE(TRIM(?))
 
INSERT INTO [USERS] ([ID],[NAME], [MD5PASSWORDKEY], [LOGON]) VALUES (?,?,?,?)
 

This statements have been created for Microsoft Access. If you are using an other database persister like Oracle, this SQL statements will change.

The whole tutorial can be download from here:
Tutorial 2: Setting up the database, store and query some data

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>