.NET Performance Blog

April 6, 2006

Database Access Layer

Filed under: XLib Framework — Eric P @ 3:21 pm

Using XLib framework I would like to build web applications consisting of the following Tiers:

Database Access

Business Logic

Presentation

In this post I will talk about Database Access layer of XLib. There are a couple of things that I tried to accomplish:

  1. Automatically convert Database values into INullable datatypes. Handle DBNull.
  2. Hide complexities of DataSet, DataAdapter, etc…
  3. Build queries using OOP

Dilemmas I had to face:

1. Should I use Typed DataSet for Data Access?

I have tried the new DAL generation tools in VS 2005. It is very quick to generate a DAL over one database table. It also automatically converts DB data types into INullables. But there were a couple of issues that I could not figure out how to do:

  • The <<Item>>Row object (ex. CustomerRow) doesn't use Nullable types for properties. So how do I set CustomerRow.Age to null? Before I would use a NIL constant (like -1), but with INullables I would rather not use that. The weird part is that .Insert() and .Update() methods of CustomerTableAdapter take INullable types.
  • Generator creates 3 classes: Customer, CustomerRow & CustomerTableAdapter. It makes it a little more complex than it needs to be.
  • If I want to load some lookup values (for example CustomerType Lookup name) and use inner join in query to generate typed DataSet — the generator would not automatically create UPDATE, INSERT, DELETE. It only works if data is loaded from one table. I could always add GetCustomerTypeName() method to Customer partial class, but I would like to be able to load all info using one query.

For now I will fore-go TypedDataSet, but it is a lot better then in .NET version 1.1.

2. Should I use ANSI convention or SQL-86 (tables are separated from joins) for query building.

I have been using SQL-86 for most of my query writing and used ANSI whenever OUTER JOINS would get too complex. At this point I would like to start using ANSI for most of the new queires. One reason is Microsoft seems to be trying to phase SQL-86 out. The other is that queries are a little cleaner in ANSI format.

For now, I have decided to support both formats .

3. For transaction handling should I use TransactionScope?

This one I researched for a while. The implementation is very straight forward. The main thing I looked at was Nested transactions. The only issue is – possible overhead of using distributed transactions. For example:

void Foo1()

{

using {TransactionScope ts1=new TransactionScope();}

{

using (SqlConnection cn1=new SqlConnection)

{

cn1.Open();
//Do some functionality
foo2();

}

ts1.Complete();

}

}

void Foo2()

{

using {TransactionScope ts2=new TransactionScope();}

{

using (SqlConnection cn2=new SqlConnection)

{

cn2.Open();
//Do some functionality

}

ts2.Complete();

}

}

Does it create a distributed transaction with ts1 and ts2? How much overhead is there? Would it be more efficient if I re-use open transaction and open connection instead of just re-creating them? I will need to run some tests.

Also how does it work internally? In the sample code Connection is disposed off before TransactionScope is disposed. How is it possible to commit transaction after connection is closed. TransactionScope probably keeps track of all the opened connections and doesn't let them close till transaction is completed.

I still need to do some more testing to see if I need to switch or not.

4. How much of DbCommand, DataAdapter, etc… functionality to hide?

Let's take a look at executing a sql statement using SqlCommand

using (SqlConnection connection1 = new SqlConnection(connectString1))
{
connection1.Open();

// Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand(commandText1, connection1);
returnValue = command1.ExecuteNonQuery();
}

In my code under any DataObject I would use the following:

XConnection cn=new XConnection();

cn.GetCmd().ExecuteSql(commandText1);

XConnection automatically load ConnectionString from config file. It can be overriden through a property.

GetCmd() returns XCommand object which provides common Sql methods: ExecuteSql, QueryLookupInt, LoadListStr, etc…

ExecuteSql method will generate the command and automatically open and close connection.
This is still a big question for me. Many of these functions I have written before .NET 2, so I still need to see what is available.

Here is the final list of XLib Db classes.

XLib.db Classes

  • XConnection
  1. Provides generic access to Sql Server, Access, Oracle, etc…
  2. Handle transaction start/end/commit
  • XDataObject
  1. Exposes connection object
  2. Implements nested transaction scope. So I re-use a transaction if it already exists
  3. Implements nested connection opens scope. So I re-use open connection if it is already open.
  • XDataReader
  1. Automatically convert db values into INullable data types.
  2. Internally use .NET DataReader.
  3. Efficient paging (use PAGE_INDEX() in Sql Server
  • XDataWriter
  1. Automatically convert INullable data into db values. Handle DBNull.
  2. Internally use DataSet and DataRow
  3. Can insert, update, delete data in one table
  4. Generate INSERT, UPDATE, DELETE statements dynamically. Don't use SqlCommandBuilder, since it is a huge hit to performance.
  • XQuerybuilder
  1. Allow for ANSI or SQL-86 notation.
  2. Parse text representing SQL query into the object
  • XDataRecordReadOnly
  1. Abstract class with Load method
  2. Uses XDataReader to read data from one table
  • XDataRecord
  1. Derived from XDataRecordReadOnly
  2. Abstract class with Save, Delete methods
  3. Uses XDataWriter to write/delete data to one table
Advertisements

1 Comment »

  1. Rosalind Garnder Super Affiliate Handbook Review

    Rosalind Garnder Super Affiliate Handbook Review

    Trackback by Rosalind Garnder Super Affiliate Handbook Review — March 15, 2008 @ 12:18 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: