Using the CMS Persistence Framework Part I: Database Queries

by dan 21. May 2009 20:29

Some days ago my director asked me to transfer some data from an excel file to an existing SQL database. Beeing an AxConsultant I tend to use the CMS persistence framework to accomplish such a task. That gave me the idea for this post series: I will try to give a brief outline on the CMS persistence framework and how to use it.

Part one will deal with the subject of direct database queries:

The CMS Persistence Framework

When you tend to use the CMS persistence framework inside your own projects you need to reference the Axinom.Framework.dll1 that can be found inside the components subfolder of the AxCMS.net Premium Template Project or the AxCMS.net Standard Template Project.

The Axinom.Framework.dll defines all relevant framework components inside the Axinom.Framework.Data namespace. Therefore every class using the CMS persistence framework needs to use this namespace:

1: using Axinom.Framework.Data;

1Naturally you need to ask Axinom first if you are allowed to use the CMS persistence framework particularly if it is a productive customer project :).

The Connection Manager Object

To connect to a database you need a connection manager that handles the database connection first. Inside AxCMS.net projects all that is done in the background. Inside your own projects you need to handle the connection manager by yourself:

1: ConnectionManager manager = new ConnectionManager(

2: new SqlDbFactory(),

3: "Data Source=(local);Initial Catalog=AxCMS_PremiumSample;Integrated Security=True");

The connection manager class constructor accepts an IDbFactory instance as the first parameter. Following IDbFactory classes are available in the Axinom.Framework.Data namespace:

The second parameter accepted by the connection manager class constructor is the connectionstring to the database itself. For this blog series i chose to use the AxCMS.net Premium Template Project management database.

Once you have created a connection manager instance you can use it to perform CRUD2 operations using the DbQuery classes.

2CRUD: Create, Read, Update, Delete

The Database Query Classes

Inside the CMS persistence framework basic CRUD operations are performed using instances of classes derived from the DbQuery class.

Selecting Database Data

Database data selection is accomplished using the DbSelectQuery class:

1: DbSelectQuery query = new DbSelectQuery();

2: query.ConnectionManager = manager;

3: query.AddTable("AxUsers", "U");

4: query.AddField("U", "EMail");

5: query.AddField("U", "Username");

6: query.AddWhere("U.CountryCode = @CountryCode");

7: query.AddParameter("@CountryCode", "de");

8: query.ExecuteQuery(0, 50);

First we create a select query instance and assign a connection manager instance. In the next line we add the table to select the data from. In this example we would like to select data from the AxUsers data table. The data fields we are interested in are the fields Email and Username. As we can select fields from several data tables we refer to the table shortcut we provided while adding the data table to the query.

The next thing done is assigning a where clause: In our example we only want to select data rows having a german country code. Please turn your attention to the fact that the where clause is formulated using a sql parameter. In the example we know that we want to compare the country code to the value "de". In real world implementations we would compare the country code to a parameter value that in worst case will be entered by a anonymous internet user. This is where bad guys could try to inject bad sql code. Using SQL parameters you can totally omit SQL injection. Be aware that using OleDB databases parameters might not be used

Executing the Query 

The last line of code executes the query selecting 50 rows of data beginning with the first result line. The result object is of type System.Data.DataTable and can be handled by any .Net Framework object that binds to data sources.

Besides the ExecuteQuery Method following execute alternatives are available throughout the DbSelectQuery class:

To be Continued...

That has been the first part of the cms persistence framework series.
The next part will touch on the rest of the CRUD operations: Create, Update and Delete.
Part three will look at more complex queries using JOIN, ORDER BY and GROUP BY clauses. Additionally we will look at stored procedures.
Part four will touch object relational mapping using the CMS persistence framework.

If you like keep me updated on any relevant questions related to the topics above!

Till then
Dan

Tags:

Submit to DotNetKicks...
Permalink | Comments (0)

Comments

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Dan Wucherpfennig - AxCMS.net evangelist

Dan Wucherpfennig is an IT consultant employed at EDV-Partner (http://www.edvpartner.de), a Hamburg based system integration and consulting company. 

Having many years of experience in developing projects with AxCMS.net, Dan has been awarded as an AxConsultant during the AxDays 2008.

Calendar

<<  July 2010  >>
MoTuWeThFrSaSu
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

View posts in large calendar