I did some research. We currently use DB2 ODBC Provider through ADO (which in turn uses ODBC Provider for OLEDB – MSDASQL). Both ODBC and OLEDB have roles in connection pooling.

By default, DB2 ODBC connection pooling is on with timeout = 60 sec. That is, a connection will be removed from the pool if the connection is not used for 60 sec. There is no max/min bound for ODBC connection pool; it is unbounded. OLEDB resource pooling for MSDASQL is also on. That means that connection could be pooled on both at OLEDB level and at ODBC level. The behavior of OLEDB resource pool is different to ODBC. It does not have max/min bound either. However, OLEDB will drop the entire pool when there are no active connections. In order to keep the pool, Microsoft suggests having code to keep one connection open (see http://msdn2.microsoft.com/en-us/library/ms810829.aspx).

IBM suggests that we disable OLEDB resource pooling for MSDASQL (see http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0010958.htm) so that it does not interfere with ODBC resource pooling. By default, both OLEDB resource pooling and ODBC connection pooling on.

By default, neither DataReader.GetSchema() nor DataTable.PrimaryKey contains the info of the Primary Key after executing a data select command.

To retrieve Primary Key using DataReader with data, use:

SqlSelectCommand1.ExecuteReader(CommandBehavior.KeyInfo)

To retrieve Primary Key using DataReader without data, use:

SqlSelectCommand1.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.SchemaOnly)

To retrieve Primary Key using DataReader with data, use:

SqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey

SqlDataAdapter1.Fill(ds)

To retrieve Primary Key using DataReader with data, use:

SqlDataAdapter1.FillSchema(ds, SchemaType.Mapped)

In part 3 of the article, we enhanced the DataGridHelper control to support custom columns.

I wrote this article for aspalliance. It is regarding of building a asp.net server control called DataGridHelper that takes care much of the repetitive coding in asp.net 1.x. Published for less than a week, the article is already among 1/3 of the articles in number of views and on the Popular Articles list. The Part 2 is coming soon.

It is generously easier to use DataSet. We can use automatic paging. We can sort and filter DataSet using DataView. DataSet is excellent for dealing with small recordsets. DataSet can be cached and used more than once though do not forget to refresh the cache when updated. Large recordset is a problem because DataSet will load the entire recordset into memory.

We needs a little bit more code with DataReader if we need paging and sorting. We have to sort in the SQL server. We can only use custom paging when we build to DataReader. Also, all the automatically generated bound columns are read-only in edit mode.

The asp.net datagrid suppports built-in paging or custom paging. Built-in paging is easy to use, but it requires the datasource to support ICollection, such as a datatable. Note, a datareader does not support ICollecton and thus cannot be used with built-in paging. The problem with a large recordset is that it takes lots of memory to load the recordset into a object that support ICollection. We can page through large recordsets using custom paging. There are fundamentally two ways. The first way is a client-side technique. It uses a firehose recordset and skip through the recordset and then only use the records of interest. The skipped records will go to the client-side but this may not be a big problem if the connectivity between the web server and database server is good. The second method is to use a server-side technique so that the record will never go the the client side. This technique requires additional server resources.

This article contains a comprehensive survey.

This site contains a large number of datagrid related links.

This article has a comprehensive summary.

In the past weekend, I set up the Visual Studio 2005 CTP March 2004 edition. It was not a smooth ride. I am still slowly converting code written with PDC bits. Here are some notes.

1. The readme file is extremely valuable. Have a copy ready all the time and it would save lots of time fighting know bugs.

2. VS2005 CTP is not compatible with Yukon beta 1. Yukon would not start. I had to unintall Yukon beta 1 and installed SQL2K with sp3.

3. IIS Manager would not run due to a corrupted registry key. See the readme file for fix.

4. Web Admin would not run. There are no known fixes.

5. master attribute of Pages element in web.config file had been renamed as masterPageFile.

6. app.sitemap has been renamed web.sitemap.

7. Change ConfigurationSettings.ConnectionStrings[connectionStringName] to ConfigurationSettings.ConnectionStrings[connectionStringName].ConnectionString.

8. GridView: columnfields collection now becomes columns collection.

9. DetailView: columnfields collection now becomes fields colleciton.

I just converted some code that updates database with dynamic sql to stored procedure calls. It add many lines of code. The previous code use SqlCommandBuilder to build the insert, update and delete statements from the select statement. The SqlCommandBuilder needs an extra trip to the SQL server to fectch the meta data.

To convert the dynamic SQL call to stored procedure call, we need to create the SqlCommand objects manually and populates its parameters collection. It is important to set the SourceColumn property of the parameter because the SqlDataAdaptor uses the SourceColumn to map the dataset columns to the parameters of the stored procedure.

Why database independence

MSSQL server is expensive for a start-up project. There are a few “free products“: MSDE and MySQL, and to a less degree, Access database. MSDE has the advantage that is mostly compatible with SQL server and thus could be later upgraded into MS SQL server.

Access mdb is the easiest one to setup and require no maintainence.

Use MySQL if need to run lots of queries but do noe want to spend money on a dedicated Windows Server. If serious thinking Linux, before sure the read this IDC study.

I think the database independent thing is mostly a cultural thing, e.g, some companies would not allow MSSQL.

Developing a database independent layer

Both the ADO.NET DataReader and DataSet are database independent.

SQL statements: Select, update and delete statements are not big problems. Many databases supports SQL92 style syntax.  Identity insert is a problem because each product uses a different mechanism. Need to encapsulate the identity insert.

Commands: Need to encapsulate. SQL server and Oracle has different stored procedure syntax. Access supports Parameterized Query definition. MySQL does not support any.

Querying the catalog: Need to encapsulate. Each database has a different mechanism for querying catalog.

Some Links

http://weblogs.asp.net/cnagel/archive/2003/10/30/34657.aspx

http://www.michaelrichardson.org/articles/Using%20Interfaces%20to%20Develop%20a%20Generic%20Data%20Access%20Layer.pdf

http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp