Brain.Serialize

Google Book Search

2005-11-21T06:37:00.000-08:00

Google has renamed its Google Print to Google Book Search.
Finding the book you want has never been easier.

The OLAP penalty for GUID primary keys

2005-11-15T15:00:00.000-08:00

Guids are a nice choice for primary keys of relatively small databases. It avoids the problem of retrieving Identity value from database after each inserts. And because keys are universally unique, it's extremely useful for distributed databases.

However, Microsoft's SQL Server 2000 Analysis Service is not able to handle Guid keys.
See link to KB article.

Although there is a work around (I.e. creating a view for these tables and convert Guids into varchars), this can become a major hassle and there is performance penalty. Think twice before you go with Guids if OLAP is down the list...

Get current DataRow from a DataSet that is bound to a DataGrid

2005-11-10T08:18:00.000-08:00

Intuitively, one would use DataGrid.CurrentRowIndex to get the DataRow in the bound DataSet:


  dataset.Tables["MyTable"].Rows[datagrid.CurrentRowIndex];

This, however, won't work when the DataGrid is sorted. The correct way to get this done is through CurrencyManager (Or BindingManagerBase):


 DataViewRow current =
 (DataViewRow)datagrid.BindingContext[dataset, "MyTable"].Current;
 DataRow row = current.Row;

The part of databinding that involves CurrencyManager is particularly confusing ---- aside from the name of the class itself ---- it seems there are more ways to pull a (different) CurrencyManager object out of BindingContext than to kill a cat:


     datagrid.BindingContext[dataset];

     datagrid.BindingContext[dataset, "MyTable"];

     datagrid.BindingContext[dataset, "MyTable.MyTableRelation"];

     datagrid.BindingContext[dataset, "MyTableRelation"];

     //...

Auditing Triggers and Typed DataSets

2005-11-08T08:04:00.000-08:00

A simple way to leave an audit trace in the database is to add LastUpdateUser and LastUpdateTime columns in each DB table, and then use Triggers to update them for each insert and update operation. (However, datetime is NOT a good candidate for Optimistic Concurrency Management, Because it's only accurate to 3.33ms, which still leaves room for concurrency issues)

But when combined with Typed DataSet, this gives a little bit of headache: every table now has these columns that are basically readonly. The function signature for adding a row in the table now becomes something like:

CustomerDataSet.AddCustomer(string FirstName, string LastName, string LastUpdateUser, DateTime LastUpdateTime);

Which is very confusing. I have tried msdata:ReadOnly in xsd, but it doesn't work. And you can't pass nulls for these parameters as it will generate an exception.

One way to get around this is to use the AddCustomer(CustomerRow row) interface, but that involves several more lines of code than the above function call.

Nested/Recursive Triggers

2005-11-04T15:38:00.000-08:00

In SQL Server, Triggers cascade by default. That is, if you do an update in insert AFTER TRIGGER, this will invoke the update TRIGGER.

However, Triggers don't recursive by default. Doing Update in an Update Trigger won't lead you into an infinite loop.

Link to MSDN

Moving In...

2005-11-03T15:38:00.000-08:00

Having deserted my old (Chinese) blog for more than a year, I finally put myself togather to start blogging all over again.

Welcome to my new dumping ground for technical articles, study notes, (hopefully) brilliant ideas and absolute nonsenses...