Pi

January 4, 2010

.net C#4 Dynamics, cool but slow

Filed under: C#, Programming — rqmedes @ 10:58 am

Recently I have been playing with DynamicObjects one of the new features in C# 4.0.
DynamicObjects once extended allows you to specifying dynamic behavior at run time.


By overriding the  TryGetMember and TrySetMember method you can dynamically add and remove properties.


dynamic person = new DynamicClass;

person.Name = “John Smith”;

person.Phone = “32345690”;

By overriding the TrySetIndex and TryGetIndex you can access properties you don’t know about at compile time.
dynamic person = new DynamicClass;
person[“Name”] = “John Smith”;

A basic implementation.

    
public class DynamicClass : DynamicObject
{
        public Dictionary _dictionary = new Dictionary();

        public override bool TryGetMember(GetMemberBinder binder, out object result)
        {
            return _dictionary.TryGetValue(binder.Name, out result);
        }

        public override bool TrySetMember(SetMemberBinder binder, object value)
        {
            _dictionary[binder.Name] = value;
            return true;
        }

        public override bool TrySetIndex(
            SetIndexBinder binder, object[] indexes, object value)
        {
            if (dictionary.ContainsKey((string)indexes[0]))
                _dictionary[(string)indexes[0]] = value;
            else
                _dictionary.Add((string)indexes[0], value);
            return true;
        }
        public override bool TryGetIndex(
            GetIndexBinder binder, object[] indexes, out object result)
        {
            return _dictionary.TryGetValue((string)indexes[0], out result);
        }
}

I found a nice implementation on David Ebbo’s blog.
He is using dynamic to simplify data access by writing a wrapper for SQL queries.

I particularly like the Yield return within the data connection. I was at first concerned about this leaving open connections but used correctly connections area automatically cleaned up for you, check this out.

It all works well however it runs really slow. I would be wary of using dynamic if performance is a consideration.
I will post benchmarks soon.

October 23, 2009

Sql Server Pagination With Total Number of Records

Filed under: SQL — Tags: , — rqmedes @ 1:00 am

Pagination is  easy in most scenarios

You usually handle it in code letting your favourite  ORM such as NHibernate  take  care of it for you.
There are times when you are required to hand-roll your own sql which is as simple as

SELECT * FROM <Table>  WHERE <Criteria> LIMIT 0, 10

or for SQL Server

ROW_NUMBER() OVER (ORDER BY <Column>) AS RowNumber

But this doesn’t  return the total number of  records,  important for  presenting   ‘Page 1 of 380‘.
I assume  most would  do an additional sql call.

SELECT  COUNT(*)  FROM  <Table>  WHERE <Criteria>

In SQL Server it is easy to get both in one query.

WITH Results AS (
SELECT ROW_NUMBER() OVER (ORDER BY <Column>) AS RowNumber,
ROW_NUMBER() OVER (ORDER BY <Column> DESCAS RowNumberDesc,
* FROM <Table>
WHERE <Criteria>)
SELECT * FROM Results  WHERE RowNumber BETWEEN <Start> AND <End>

This will return the results  between <Start> and  <End>  ordered by <Column>.
You can  get the Total Number of records  by adding the <Start> value to the  RowNumberDesc  value of the first record returned.
Preliminary benchmarking shows that it performs faster than running the two queries method.

Create a free website or blog at WordPress.com.