rschandrastechblog: November 2010

Monday, November 29, 2010

Persistence Patterns

http://msdn.microsoft.com/en-us/magazine/dd569757.aspx

Persistence Patterns

Patterns in Practice

Persistence Patterns

Jeremy Miller

Contents

Mapping Objects to Databases
Active Record
Data Mapper
Using a Repository
Identity Map
Lazy And Eager Loading
Virtual Proxy Pattern
Taking the Next Step

Data access is a popular subject among developers. No doubt you've heard plenty of opinions on specific data access technologies and persistence frameworks, but what's the best way to consume these tools in your project? What criteria should you use to select the right tool for your project? What do you need to know conceptually about these tools before you use? What if you've got too much time on your hands and want to write your own persistence tool—what do you need to know?

Unsurprisingly, the answer to all of these questions is to examine the underlying design patterns of persistence.

Domain Models

When you think about how you are going to structure and represent the business logic in your system, you have a couple major choices. In this article, I'm largely assuming that you have chosen the domain model approach to organizing business logic into entity objects.

From the formal description, a domain model is an object model of the domain that incorporates both behavior and data.

For example, my current project involves Customer Relationship Management. (CRM) We have entity objects for Case and User and Solution that contain both data and implement business rules involving that data. A domain model can range from an anemic model that is simply a set of data structures to a very rich model that jealously guards the raw data behind a narrow interface (hardcore Domain-Driven Development). Where your domain model falls in this range is largely a matter of how complicated the business logic in your system really is and how prevalent reporting or data entry are in your system requirements.

An adequate discussion of the Domain Model pattern is beyond the scope of this article. I would strongly recommend reading Chapter 2 of Martin Fowler's Patterns of Enterprise Application Architecture book for a great discussion of the major design patterns for organizing business logic.

Before getting started, let's review the two main ways to perceive the role of the database and data access code in your system:

The database is the keystone of the application and a business asset. The data access code and even the application or service code are simply mechanisms to connect the database with the outside world.
The business objects in the middle tier and the user interface or service layer are the application, and the database is a means to reliably persist the state of the business objects between sessions.

Personally, I feel like the first, datacentric viewpoint is well understood in the .NET community, so I'd like to focus on the second viewpoint. In the second viewpoint, you're typically working with entity objects in the middle tier. One way or another, you're probably doing Object/Relational Mapping (O/RM) to map data from the business entities to the database tables and vice versa. You might be doing it by hand, but more likely with tools of some sort. Those tools are great and can potentially save a lot of development time, but there are some issues you should be aware of, and it's always nice to understand how a tool works beneath the covers. Hopefully, studying the patterns in this article will help on both accounts.

I'm a big proponent of Agile practices like Test Driven Development, Behavior Driven Development, Lean Programming, and Continuous Design. I say this just to be clear that I have a very specific bias in regards to the patterns I'll be discussing in this article, and it will probably show.

Mapping Objects to Databases

I've decided to model my system in the middle tier with entity objects that have identity, data, and related behavior. My business logic will be implemented either in these entity objects or in domain services that use these entity objects. Great, but how do you seamlessly move data back and forth between the database and the entity objects?

In the case of a relational database, you need to move the fields and properties of our objects to tables and fields in the database. You can write this code completely by hand, writing out separate INSERT, UPDATE, SELECT, and DELETE SQL statements, but you'll quickly realize that you're repeating yourself in the code quite a bit. Namely, you're repeatedly specifying that the data in an object property or field should be stored into a particular column in a database table.

This is where an Object/Relational Mapper (O/RM) steps in. When you use an O/RM, you simply create a mapping of the object properties to the database table and let the O/RM tool use that metadata to figure out what the SQL statements should be and how to move data from the object to the SQL statements.

Let's get concrete and look at a very basic sample. My current project has an Address class that looks like this:

Copy Code

public class Address 
{
  public long Id { get; set; }
  public string Address1 { get; set; }
  public string Address2 { get; set; }
  public string City { get; set; }
  public string StateOrProvince { get; set; }
  public string Country { get; set; }
  public string PostalCode { get; set; }
  public string TimeZone { get; set; }
}

When I set up the mapping for the Address class, I need to specify which table the Address class maps to, how the Address objects will be identified (the primary key), and which properties map to which database tables.

For this example, I'm using the Fluent NHibernate tool for mapping Address.

I'm purposely doing the mapping in a longhand manner to show all the details. (In real usage, I employ convention over configuration to eliminate much of the repetitiveness.) Here's the code:

Copy Code

public class AddressMap : ClassMap<Address> 
{
  public AddressMap() 
  {
    WithTable("Address");
    UseIdentityForKey(x => x.Id, "id");

    Map(x => x.Address1).TheColumnNameIs("address1");
    Map(x => x.Address2).TheColumnNameIs("address2");
    Map(x => x.City).TheColumnNameIs("city");
    Map(x => x.StateOrProvince).TheColumnNameIs("province_code");
    Map(x => x.Country).TheColumnNameIs("country_name");
    Map(x => x.PostalCode).TheColumnNameIs("postal_code");
  }
}

Now that you've made a mapping of the object model to the database model, something has to actually execute the mapping, and you roughly have two choices: Active Record or Data Mapper.

Active Record

When choosing a persistence strategy, the first decision you need to make is where to place the responsibility for carrying out the mapping. You have two very different options: you can either make each entity class itself responsible for the mapping, or you can use a completely separate class to do the mapping to the database.

The first option is known as the Active Record pattern: an object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data. An Active Record approach puts persistence methods directly onto the entity object. In this case, the Address class would probably have methods like Save, Update, and Delete, as well as a static Load method that queries the database for an Address object.

The typical usage of the Active Record pattern is to essentially make a strongly typed class wrapper around a single row in a database table. As such, the Active Record classes are generally an exact mirror of the database structure (this will vary between tools). Many Active Record implementations will generate the entity objects directly from the database structure.

The most famous Active Record implementation is the ActiveRecord tool that comes as part of the Ruby on Rails Web development framework (which is usable in .NET programming with IronRuby; see "Getting Started With IronRuby And RSpec, Part 1"). If I were using ActiveRecord, I would first create a table named addresses in the database. Then, if all I care about is having access to the address data and methods for finding, saving, and inserting address data, the entire Address class would look exactly like this Ruby class:

Copy Code

class Address < ActiveRecord::Base
end

The Ruby implementation is using metaprogramming to dynamically create fields and query methods that match the database table named addresses (pluralized form of the class name Address) at runtime.

With a few exceptions, .NET implementations of the Active Record pattern generally work by using code generation to create .NET classes that map directly to database tables. The benefit of this approach is that it is very easy to keep the database and object model synchronized.

Lean Programming

Lean Programming teaches you to eliminate wasted effort in development projects by favoring "pull" design over "push" design. This means infrastructure concerns like persistence should only be designed and built to satisfy the needs of business requirements (pulled on demand) instead of building the data access layer code that you think the application will need later (pushed).

In my experience, this means you should develop the system incrementally by developing vertically—by building one feature at a time instead of building the system one horizontal layer at a time. By working this way you can be sure that infrastructure development is no more than what you absolutely need by tying all infrastructure code to a feature being built into the system. When you work horizontally by building the data access layer or the database before you write the user interface, service, or business logic layer, you risk the following:

Not getting adequate feedback early from project stakeholders because there is no working functionality to demonstrate
Writing unnecessary infrastructure code for features that might not actually get built
Building the wrong data access code because you don't understand the business requirement early on, or the requirements change before the other layers are created

Pull design also means that your choice of data access strategy should be determined by the needs of the application. Given a choice, I would choose an O/RM for a system where I was modeling the business logic in a domain model. For a reporting application, I would bypass persistence tools altogether in favor of just using SQL and datasets. For a system that is mostly data entry, I might choose an Active Record tool. The point is that the data access and persistence is shaped by the needs of its consumers.

Data Mapper

Nice as the Active Record pattern may be, it is often valuable to have an object structure that varies from the database model. You may want to map multiple classes to the same database table. You might have a legacy database schema that doesn't fit the shape of the objects that you would want to express in some business logic (a common occurrence for me on my last several projects).

This is where I would choose the Data Mapper pattern: a layer of mapper objects that move data between objects and a database while keeping them independent of each other and the mapper classes themselves. The Data Mapper pattern strips most of the responsibility of persistence from the entity objects in favor of classes external to the entities. With a Data Mapper pattern, you access, query, and save entity objects with some kind of repository (discussed later in this article).

How do you choose between the two? My personal bias is very strongly towards the Data Mapper solution. That aside, Active Record is best for systems with simpler domain logic, CRUD-intensive applications (create, read, update and delete, that is), and situations where the domain model doesn't need to diverge much from the database structure. Active Record may be more comfortable for many .NET development teams because it implies a datacentric way of working that is more common for .NET teams and, frankly, much better supported by .NET itself. I would characterize most persistence tools in the .NET space as Active Record tools.

On the other hand, Data Mapper is more appropriate for systems with complex domain logic where the shape of the domain model will diverge considerably from the database model. Data Mapper also decouples your domain model classes from the persistence store. That might be important for cases where you need to reuse the domain model with different database engines, schemas, or even different storage mechanisms altogether.

Most important to me as an Agile practitioner, the Data Mapper approach allows me to design the object model independently of the database with Test Driven Development more efficiently than an Active Record solution could. This is important to teams that want to take an incremental approach to design because it allows a team to work through the design in the object model first where refactoring tools and unit testing techniques are generally more effective, then create the database model later when the object model is stable. The Data Mapper pattern is also more applicable to the Domain Driven Design architectural approach that is gaining in popularity in the .NET community.

Using a Repository

When I was growing up as a developer in the Windows DNA days, I lived in mortal fear of forgetting to close an ADO Connection object in my code. On my first big enterprise project, I coded directly against ADO. Every time I needed to request or save data to the database, I had to do the following:

Find the connection string from some sort of configuration
Open a new connection to the database
Create a command object or a recordset object
Execute the SQL statement or stored procedure
Close the connection to release the database resources
And oh yeah, put some adequate error handling around the data access code

The first problem was the absurd amount of repetitive code I was writing. The second problem was that I had to write the code correctly every single time, because forgetting to close a single connection could, and unfortunately did, drive a mission-critical system offline under a heavy load. Nobody wanted to be the guy whose code collapsed out of poor database connection hygiene, but it still happened too frequently.

In my next project, my coworker and I got smarter. We implemented a single class that would do all the setup, teardown, and error handling code of the ADO Connection object. Any other class in the system could interact with the database by using this central class to invoke stored procedures. We wrote much less code and, better yet, we weren't plagued by problems arising from forgetting to close database connections because we had to get that connection management code right only one time.

What my team did was to create a crude implementation of the Repository pattern that mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.

Basically, the Repository pattern just means putting a façade over your persistence system so that you can shield the rest of your application code from having to know how persistence works. My current project is using a Repository with a public interface that looks like the one in Figure 1.

Figure 1 Repository interface

Copy Code

public interface IRepository 
{
  // Find an entity by its primary key
  // We assume and enforce that every Entity
  // is identified by an "Id" property of 
  // type long
  T Find<T>(long id) where T : Entity;

  // Query for a specific type of Entity
  // with Linq expressions.  More on this later
  IQueryable<T> Query<T>();
  IQueryable<T> Query<T>(Expression<Func<T, bool>> where);

  // Basic operations on an Entity
  void Delete(object target);
  void Save(object target);
  void Insert(object target);

  T[] GetAll<T>();
}

When some class in the system needs to access an entity object, it can simply use IRepository to fetch that entity by its ID, or query for a list of entity objects with a LINQ expression.

When I use the concrete class of IRepository, which uses the NHibernate library for O/RM, I'm pulling a connection string from memory, loading the mapping definitions from an assembly, building the NHibernate SessionFactory (once and only once because it's a big performance hit), and wrapping the low-level ISession interface from NHibernate. With some help from the Repository class, NHibernate manages database connection lifecycle issues.

Whew. That's a lot of stuff going on behind the scenes. It's a good thing that I've swept all of the direct interaction with NHibernate behind the IRepository interface. I don't have to know all that bootstrapping NHibernate stuff just to load, save, and query objects. Even better, since every class depends on the abstract IRepository interface for data access and persistence, I can slide in an InMemoryRepository implementation of IRepository that uses LINQ to Objects internally to stub the database during testing.

Identity Map

Let's look at a common scenario. Inside a single logical transaction in some sort of shipping system, you have two completely different classes that work independently, but both classes will need to retrieve the same Customer entity during the transaction. Ideally, you want only a single Customer object inside a single transaction for each logical Customer so that each object is working off of consistent data.

In persistence tooling, preventing duplicate logical references is the job of the Identity Map pattern. As stated by Martin Fowler, an Identity Map ensures that each object gets loaded only once by keeping every loaded object in a map and looks up objects using the map when referring to them.

For the Customer class, you might build a naïve implementation of Identity Map like the one in Figure 2. I'm purposely leaving out thread locking here just to simplify the code. A real implementation would require adequate thread safety measures.

Figure 2 Identity Map Customer class

Copy Code

public class CustomerRepository 
{
  public IDictionary<long, Customer> _customers = 
    new Dictionary<long, Customer>();

  public Customer FindCustomer(long id) 
  {
    if (_customers.ContainsKey(id)) 
  {
      return _customers[id];
    }

    var customer = findFromDatabase(id);
    _customers.Add(id, customer);

    return customer;
  }

  private Customer findFromDatabase(long id) 
  {
    throw new System.NotImplementedException();
  }
}

In this example, a Customer object is identified by its ID. When you request an instance of Customer by ID, the CustomerRepository first checks an internal dictionary to see whether it has that particular Customer. If so, it returns the existing Customer object. Otherwise, CustomerRepository will fetch the data from the database, build a new Customer, store that Customer object in its dictionary for later requests, and return the new Customer object.

Fortunately, you generally won't write this code by hand because any mature persistence tool should include this feature. You do need to be aware that this is happening behind the scenes and scope your persistence support objects accordingly. Many teams will use the lifecycle management feature of an Inversion of Control tool (StructureMap, Windsor, Ninject, and others) to ensure that all classes in a single HTTP request or thread are using the same underlying Identity Map. The Unit of Work pattern is another way to manage a single Identity Map across multiple classes in the same logical transaction.

Just to illustrate this pattern farther, Figure 3 shows an example of how an Identity Map works. The code is written against the architecture of my current project. The instances of the IRepository interface shown in the code below wrap a single NHibernate ISession, which in turn implements the Identity Map pattern. When I run this test the output is this:

Copy Code

1 passed, 0 failed, 0 skipped, took 5.86 seconds.

Figure 3 Using an Identity Map

Copy Code

[Test]
public void try_out_the_identity_map() 
{
  // All I'm doing here is getting a fully formed "Repository"
  // from an IoC container and letting an IoC tool bootstrap 
  // NHibernate offstage.
  IRepository repository = ObjectFactory.GetInstance<IRepository>();

  // Find the Address object where Id == 1
  var address1 = repository.Find<Address>(1);

  // Find the Address object where Id == 1 from the same Repository
  var address2 = repository.Find<Address>(1);

  // Requesting the same identified Address object (Id == 1) inside the 
  // same Repository / Identity Map should return the exact same
  // object
  address1.ShouldBeTheSameAs(address2);

  // Now, let's create a completely new Repository that has a 
  // totally different Identity Map
  IRepository secondRepository = ObjectFactory.GetInstance<IRepository>();

  // Nothing up my sleeve...
  repository.ShouldNotBeTheSameAs(secondRepository);

  var addressFromSecondRepository = secondRepository.Find<Address>(1);

  // and this is a completely different Address object, even though
  // it's loaded from the same database with the same Id
  addressFromSecondRepository.ShouldNotBeTheSameAs(address1);
}

Lazy And Eager Loading

One of the best things about using a persistence tool is the ability to load a root object (Invoice, perhaps), then navigate directly to its children (InvoiceLineItem) and related objects just by using properties of the parent class. However, sooner or later you're going to have to care about the performance of your application. Fetching an entire object graph when you may need only the top-level object most of the time or can forgo parts of the object graph isn't efficient.

That's OK. In that case, you can use the Lazy Loading pattern in which you defer initializing the object until just before it's needed.

Let's put this in more concrete terms. Say that you have a class named Customer that references an Address object:

Copy Code

public class Customer : DomainEntity 
{
  // The "virtual" modifier is important.  Without it,
  // Lazy Loading can't work
  public virtual Address HomeAddress { get; set; }
}

In most use cases involving the Customer object, the code never needs the Customer.HomeAddress property. In that case, you could set up the database mapping to make the Customer.HomeAddress property lazy loaded like in this Fluent NHibernate mapping:

Copy Code

public class CustomerMap : DomainMap<Customer> 
{
  public CustomerMap() 
  {
    // "References" sets up a Many to One
    // relationship from Customer to Address
    References(x => x.HomeAddress)
      .LazyLoad() // This marks the property as "Lazy Loaded"
      .Cascade.All();
  }
}

With Lazy Loading turned on, the Customer object is fetched without the Address data. However, as soon as any caller tries to access the Customer.HomeAddress property for the first time, that data will be transparently loaded.

Do note the virtual modifier on the Customer.HomeAddress property. Not every persistence tool does this, but NHibernate implements lazy loaded properties by creating a dynamic subclass of Customer that overrides the HomeAddress property to make it lazy loaded. The HomeAddress property needs to be marked as virtual in order to allow a subclass to override the property.

Of course, there are other times when you request an object and you know that you will most likely need its children at the same time. In this case, you will probably opt for Eager Loading and have the children data loaded at the same time as the parent. Many persistence tools will have some sort of ability to optimize Eager Loading scenarios to fetch a hierarchy of data in a single database round-trip. If you need the Customer.HomeAddress data most of the time you use a Customer object, then you would be better off doing Eager Loading to get the Customer and Address data at the same time.

At this point, I should repeat the old maxim that the only way to reliably tune an application for performance is to use an empirical measurement of performance with a profiler.

Virtual Proxy Pattern

Lazy Loading is often implemented by using a virtual proxy object that looks just like the real object to be loaded later. Let's say that the domain model includes a class named CustomerRepresentative that references a list of Customer objects. Part of that class is shown in Figure 4.

Figure 4 CustomerRepresentative

Copy Code

public class CustomerRepresentative 
{
  // I can create a CustomerRepresentative directly
  // and use it with a normal List
  public CustomerRepresentative() 
  {
    Customers = new List<Customer>();
  }

  // Or I can pass an IList into it.
  public CustomerRepresentative(IList<Customer> customers) 
  {
    Customers = customers;
  }

  // It's not best practice to expose a collection
  // like I'm doing here, but it makes the sample
  // code simpler ;-)
  public IList<Customer> Customers { get; set; }
}

There are many times when the system uses an instance of CustomerRepresentative without needing the list of Customers. In that case, you could simply construct a CustomerRepresentative with a virtual proxy object that looks like an IList<Customer> object and use that virtual proxy class without making any changes to CustomerRepresentative whatsoever. That virtual proxy class might look something like Figure 5. A CustomerRepresentative object could then be created with Lazy Loading as shown in Figure 6.

Figure 5 Virtual proxy for CustomerRepresentative

Copy Code

public class VirtualProxyList<T> : IList<T> 
{
  private readonly Func<IList<T>> _fetcher;
  private IList<T> _innerList;
  private readonly object _locker = new object();

  // One way or another, VirtualProxyList needs to 
  // find the real list.  Let's just cheat and say
  // that something else will pass it a closure
  // that can find the real List
  public VirtualProxyList(Func<IList<T>> fetcher) 
  {
    _fetcher = fetcher;
  }

  // The first call to 
  private IList<T> inner 
  {
    get 
    {
      if (_innerList == null) 
      {
        lock (_locker) 
        {
          if (_innerList == null) 
          {
            _innerList = _fetcher();
          }
        }
      }

      return _innerList;
    }
  }


  IEnumerator IEnumerable.GetEnumerator() 
  {
    return inner.GetEnumerator();
  }

  public IEnumerator<T> GetEnumerator() 
  {
    return inner.GetEnumerator();
  }

  public void Add(T item) 
  {
    inner.Add(item);
  }

  // and the rest of the IList<T> implementation

}

Figure 6 Lazy Loading CustomerRepresentative

Copy Code

public class CustomerRepresentativeRepository 
{
  private readonly ICustomerRepository _customers;

  public CustomerRepresentativeRepository(
      ICustomerRepository customers) 
  {
    _customers = customers;
  }

  // This method will "find" a CustomerRepresentative, and
  // set up the Virtual Proxy for the Customers
  public CustomerRepresentative Find(long id) 
  {
    var representative = findRepresentative(id);
    representative.Customers = 
      new VirtualProxyList<Customer>(() => 
      _customers.GetCustomersForRepresentative(id));

    return representative;
  }
}

Like most of the patterns in this article, the virtual proxy isn't something that you're likely to write by hand, but be aware that it's there in the background of your persistence tool.

Taking the Next Step

When I began programming with Windows DNA technologies, I probably spent over half of my time working with raw ADO code. Today, persistence coding and infrastructure is a very small percentage of my team's time. So what changed over the years? We use persistence tools and these design patterns to eliminate so much of the repetitive coding we used to do.

Most of these patterns were taken from Martin Fowler's book, Patterns of Enterprise Application Architecture. I highly recommend reading this book if you have anything to do with writing enterprise applications. Due to length limitations, I was unable to cover some other important patterns like Unit of Work, Specifications, and Persistence Ignorance. In addition, there are quite a number of ways to use the Repository pattern and design considerations (a single generic repository versus specific, "narrow" repository classes, whether a repository should even expose "Save" methods, etc.) I would urge you to research these topics as well, and I may write a follow-up article to continue this discussion.

Lastly, I'd like to say that the .NET ecosystem is richer than just Entity Framework and LINQ to SQL. I'm happy with NHibernate as a Data Mapper that knows relatively little about persistence. SubSonic is a popular Active Record implementation for .NET programming. iBatis.Net is fantastic for existing databases or occasions when you want full control over authoring the SQL statements. LLBLGen Pro is a very mature tool with unique querying abilities. Many of the other tools also have the ability to use LINQ queries.

Send your questions and comments to mmpatt@microsoft.com.

Jeremy Miller, a Microsoft MVP for C#, is also the author of the open-source StructureMap (structuremap.sourceforge.net) tool for Dependency Injection with .NET and the forthcoming StoryTeller (storyteller.tigris.org) tool for supercharged FIT testing in .NET. Visit his blog, "The Shade Tree Developer," part of the CodeBetter site.

Sunday, November 28, 2010

Subquery columns are available from outside of a subquery

Subquery columns are available from outside of a subquery

e.g. the following perfectly works :
select Routename from (
select distinct RouteName , RouteId from Sec45.dbo.SCHULE_ROUTEMASTER
) a
order by a.RouteId
Eventhough you will notice that the outer query has only one column, Routename,
we can have an ORDER BY cLAUSE in the outer query, which refers to a column
within a subquery,
NOTE that we cannot put the ORDER BY in inner subquery, since it is invalid to
put ORDER BY in a subquery.
We cannot have something like this :
select distinct Routename from Sec45.dbo.SCHULE_ROUTEMASTER
order by Routeid
This is because RouteId is not contained in
aggregates list, hence we cannot order by it.

Thursday, November 25, 2010

How to get Today's Start time in the night e.g. Today's date + '00:00:00' ?

How to get Today's Start time in the night e.g. Today's date + '00:00:00' ?
select convert(datetime , CONVERT(nvarchar(20) , getdate(), 101))
select convert(datetime,CONVERT(nvarchar(10) , getdate(), 120))
Both will output '2010-11-26 00:00:00.000'
You can get some particular time in the day also. e.g. you want to get
afternoon (12:00) and evening (18:00)

select dateadd(HH , 12, convert(datetime,CONVERT(nvarchar(20),getdate(), 101) ) )
select dateadd(HH , 16, convert(datetime,CONVERT(nvarchar(20),getdate(), 101) ) )

BETWEEN AND CONVERT

What happens when there are more characters in the output of a CONVERT call than specified ?
e.g. convert(nvarchar(10) , 123456789122345678)
select convert(nvarchar(10) , 123456789122345678)
Arithmetic overflow error converting expression to data type nvarchar.
select convert(nvarchar(10) , '123456789122345678')
=> '1234567891'
if O/P type is string then it is trucated to specified length
BETWEEN is inclusive or exclusive ?
->inclusive
BETWEEN does not support exclusive mode, you have to use > <
if any of the values in the range contain NULL, the result is UNKNOWN as per the documentation.
WHAT IS UNION, EXCEPT and INTERCEPT

AKASH KAVA _ NEUROSPEECH

http://akashkava.com/blog/

http://www.neurospeech.com/Services/PhotoRetouch.html

CROSS APPLY

CROSS APPLY --- What is this ?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148717

Monday, November 22, 2010

[CLIENT GRIDVIEW III] : http://www.aspboy.com/Categories/GridArticles/Excel_Like_GridView.aspx

http://www.aspboy.com/Categories/GridArticles/Excel_Like_GridView.aspx

reating an Excel Like GridView.

Author: Srikanth Reddy

There are many posts in the forums where people ask how to make a Grid which behaves like an excel sheet i.e. a Grid which has resizable columns, a Grid which has resizable rows, a Grid which has editable cells, a Grid whose cells can be navigated by using the arrow keys and the Tab key, a focussed cell in the Grid which responds to the key strokes when a user starts typing. When you Google for an “Excel Like GridView” or “Excel Like DataGrid” what you get is a link which doesn’t meet your expectations or a link which is already dead. I hope you will enjoy this article on making “An Excel Like GridView.” I would like thank Matt Berseth whose blog provoked my thought on making “An Excel Like GridView.”

So, let’s get it started.

The features this GridView provides are:

Resizable columns.
Resizable rows.
Editable cells.
Navigating through cells using the arrow keys and the tab key.
Focussed cell which responds to the key strokes when the user starts typing.

Before you start reading the article, Click here to view the demo. Currently the demo works only on Internet Explorer (version 6 and 7).

Now that you have seen the demo lets see on how An Excel Like GridView can be created. Add a GridView to your page which looks as shown below:

<asp:GridView ID="gvExcel" runat="server" Width="50%" OnRowDataBound="gvExcel_RowDataBound">

</asp:GridView>

Now do the following in the page load event:

protected void Page_Load(object sender, EventArgs e)

{

Char[] alpha ={ ' ', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J' };

DataTable dtExcel = new DataTable();

for (int i = 0; i < alpha.Length; i++)

{

dtExcel.Columns.Add(alpha[i].ToString());

}

for (int i = 0; i < 10; i++)

{

DataRow drRow = dtExcel.NewRow();

drRow[0] = i + 1;

dtExcel.Rows.Add(drRow);

}

gvExcel.BorderColor = System.Drawing.Color.FromArgb(0, 0, 0);

gvExcel.DataSource = dtExcel;

gvExcel.DataBind();

}

What am I doing here is, we are creating a DataTable which can be bound to the GridView. The character array is used to create columns in the GridView. In the example shown above I have created columns from A to J and an empty space column at the beginning. And then I have created 10 rows in the DataTable whose first column holds the sequence number of the row.

Now that the DataTable is bound to the GridView lets handle something in the RowDataBound event. The following is the RowDataBound event of the GridView:

protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e)

{

e.Row.Cells[0].Width = Unit.Pixel(5);

e.Row.Cells[0].BorderColor = System.Drawing.Color.Black;

e.Row.Cells[0].BackColor = System.Drawing.Color.FromArgb(242, 242, 242);

if (e.Row.RowType == DataControlRowType.DataRow)

{

for (int i = 1; i < e.Row.Cells.Count; i++)

{

TextBox txt = new TextBox();

txt.TextMode = TextBoxMode.MultiLine;

txt.Rows = 1;

txt.CssClass = "textBox";

txt.BorderWidth = Unit.Pixel(0);

txt.Width = Unit.Pixel(70);

txt.Text = "";

e.Row.Cells[i].Controls.Add(txt);

e.Row.Cells[i].BorderColor = System.Drawing.Color.Black;

txt.Attributes.Add("onclick","javascript:setFocus(this)");

}

else if (e.Row.RowType == DataControlRowType.Header)

{

e.Row.BackColor = System.Drawing.Color.FromArgb(242, 242, 242);

for (int i = 1; i < e.Row.Cells.Count; i++)

{

e.Row.Cells[i].BorderColor = System.Drawing.Color.Black;

}

Every first cell of the Gridview in the RowDataBound event is set to 5 pixel width and its BorderColor set to Black and BackColor set to RGB values 242,242 and 242. And I have set each header cell’s BorderColor to black in the “else if” part(the header part) of the RowDataBound event.

Now in the “if” part(the DataRow part), Im creating Multiline TextBoxes in each cell of the row with the TextBox’s BorderWidth set to Zero and Width set to 70 pixels and I have added the TextBox to each cell of the Row with each cell’s border color set to Black. Pay attention to the Bolded and underlined part (txt.CssClass = "textBox";). This is a css class assigned to the TextBox which makes the scrollbar of the Multiline TextBox invisible. The textBox class is as shown below:

.textBox

{

overflow:hidden;

}

Finally, the onclick event is added to each text box to set the focus when the user clicks a particular cell in the GridView.

If you have seen the demo of this article, you may have noticed that unlike the Grids you have seen which restrict you to resize the columns within the boundaries of the Grid, this Grid lets you resize the column to any extent. i.e as you resize the column the GridView keeps on growing horizontally and as you resize the row the GridView keeps on growing vertically. So, now lets make the GridView resizable.

Add a script manager to your aspx page as shown below.

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

Script manager implicitly calls the client side pageLoad event.

Add the following script tag to your page:

var headers;

var rows;

var grid;

var resizing;

var element;

var cursorType;

var currentCellReference;

function pageLoad(object)

{

grid=document.getElementById("gvExcel");

if(grid!=null)

{

$addHandler(document,'mousemove',mouseMove);

$addHandler(document,'mouseup',mouseUp);

$addHandler(document, 'selectstart', selectStart);

headers=document.getElementsByTagName('TH');

if(headers!=null)

{

for(i=0;i<headers.length;i++)

{

headers[i].style.width=Sys.UI.DomElement.getBounds(headers[i]).width+'px';

$addHandler(headers[i],'mousedown',mouseDown);

}

rows=document.getElementsByTagName('TR');

{

if(rows!=null)

{

for(i=1;i<rows.length;i++)

{

rows[i].style.width=Sys.UI.DomElement.getBounds(rows[i]).width+'px';

$addHandler(rows[i].children[0],'mousedown',mouseDown)

}

currentCellReference=rows[1].children[1];

rows[1].children[1].style.borderWidth=3;

}

function mouseMove(object)

{

if(resizing)

{

if(cursorType=='e-resize')

{

var bounds=Sys.UI.DomElement.getBounds(element);

var newColumnWidth =(object.clientX+document.documentElement.scrollLeft)-bounds.x;

if(newColumnWidth>0)

{

element.style.width=newColumnWidth;

for(i=1;i<rows.length;i++)

{

rows[i].children[element.cellIndex].children[0].style.width=newColumnWidth;

}

var newGridWidth=0;

for(i=0;i<headers.length;i++)

{

newGridWidth+=parseInt(headers[i].style.width);

}

grid.style.width=newGridWidth;

}

else if(cursorType=='n-resize')

{

var bounds=Sys.UI.DomElement.getBounds(element);

var newRowHeight=(object.clientY+document.documentElement.scrollTop)-bounds.y;

if(newRowHeight>0)

{

element.style.height=newRowHeight;

for(i=1;i<element.children.length;i++)

{

element.children[i].children[0].style.height=newRowHeight;

}

else

{

var bounds=Sys.UI.DomElement.getBounds(object.target);

if(Math.abs((bounds.x+bounds.width)-(object.clientX+document.documentElement.scrollLeft))<=2)

{

if(object.target.tagName=='TH' && object.target.cellIndex!=0)

{

object.target.style.cursor='e-resize';

}

else if(Math.abs((bounds.y+bounds.height)-(object.clientY+document.documentElement.scrollTop))<=2)

{

if(object.target.tagName=='TD' && object.target.cellIndex==0)

{

object.target.style.cursor='n-resize';

}

else

{

object.target.style.cursor='auto';

}

function mouseUp(object)

{

resizing=false;

}

function mouseDown(object)

{

if(object.target.style.cursor=='e-resize')

{

resizing=true;

cursorType=object.target.style.cursor;

element=object.target;

}

else if(object.target.style.cursor=='n-resize')

{

resizing=true;

cursorType=object.target.style.cursor;

element=object.target.parentElement;

}

function selectStart(object)

{

if(resizing)

{

object.preventDefault();

return false;

}

</script>

Now let me explain you what this code does.

In the pageLoad event I get the grid by using it’s id. If the Grid is not null then I have added three events (mousemove, mouseup and selectstart) to the document.

Note: The mousemove and mouseup events can be registered only to the header and row cells of the GridView but I came up with the idea of adding it to the document because while you resize the GridView (for example a column), if the mousemove event is registered just to the header cell, the user while resizing the column will have to take care that the mouse pointer doesn’t go out of the header and if it goes, the resizing stops. So by adding this event to the document ensures unattentive resizing i.e. even if the mouse pointer moves out of the header or row the resizing still continues. The resizing stops when the mouse button is released. And if the mouse button is released out of the window the resizing still continues when you bring back the pointer because this window still has not received the mouseup event.

Now the following line of code:

headers=document.getElementsByTagName('TH');

if(headers!=null)

{

for(i=0;i<headers.length;i++)

{

headers[i].style.width=Sys.UI.DomElement.getBounds(headers[i]).width+'px';

$addHandler(headers[i],'mousedown',mouseDown);

}

gets all the header cells in the document. As you can see that Im adding the mousedown event for each header cell.

Note: If your page contains only one GridView then using headers=document.getElementsByTagName('TH'); is fine but if your page contains multiple GridViews or HTML tables then using headers=$get('gvEmployees').getElementsByTagName('TH'); is a good practice as you will not end up in retrieving unnecessary headers in a document.

The following code gets all the rows in the document:

rows=document.getElementsByTagName('TR');

{

if(rows!=null)

{

for(i=1;i<rows.length;i++)

{

rows[i].style.width=Sys.UI.DomElement.getBounds(rows[i]).width+'px';

$addHandler(rows[i].children[0],'mousedown',mouseDown)

}

currentCellReference=rows[1].children[1];

rows[1].children[1].style.borderWidth=3;

}

A mousedown event is also added for the first cell of each row, this is where the user gets the control to resize the row.

The following two lines in the above code are used to set the current focussed cell and to set its borderWidth to 3 pixels. Here I have set the second row’s(remember that the first row is the header with cells as THs) second cell as the curentCellReference to make it as the defaul selection while the page first loads.

currentCellReference=rows[1].children[1];

rows[1].children[1].style.borderWidth=3;

Now that the pageLoad part is completed lets move on to the mouseMove part.

Let us start in a step by step process of understanding how each event is fired:

First the user moves his mouse pointer, at this point of time the else part of the mouseMove keeps on running to decide whether the mouse pointer is at the edge of the header cell or on the edge of the row cell and if it is, then the pointer itself changes to ‘e-resize’ or ‘n-resize’ indicating the user that the column or the row can be resized. The decision on how to change the cursor to e-resize is done by the following line of code:

if(Math.abs((bounds.x+bounds.width)-(object.clientX+document.documentElement.scrollLeft))<=2)

bounds.x – Gives the x coordinate value of the current column which is being resized.

bounds.width – Gives the current width of the column(‘TH’).

object.clientX – Gives the current x coordinate value of the mouse pointer.

document.documentElement.scrollLeft – Gives the value of the extent to which the window is horizontally scrolled. If the window is not scrolled then this returns zero.

So by adding bounds.x+bounds.width I get the x coordinate value of the egde of the column (TH. Now if this added value is less than or equal to 2 of the current x coordinate value of the mouse pointer then the pointer of the mouse is changed to e-resize.

Similarly, the decision on how to change the cursor to n-resize is done by the following line of code:

else if(Math.abs((bounds.y+bounds.height)-(object.clientY+document.documentElement.scrollTop))<=2)

bounds.y – Gives the y coordinate value of the current row which is being resized.

bounds.height – Gives the current height of the row(‘TR’).

object.clientY – Gives the current y coordinate value of the mouse pointer.

document.documentElement.scrollTop– Gives the value of the extent to which the window is vertically scrolled. If the window is not scrolled then this returns zero.

So by adding bounds.y+bounds.height I get the y coordinate value of the bottom egde of the row(TR) now if this added value is less than or equal to 2 of the current y coordinate value of the mouse pointer then the pointer of the mouse is changed to n-resize.

Now when the user sees the resize pointer, he clicks and drags it horizontally or vertically based on what he wants to resize(either a column or a row). So, when the user sees the e-resize or n-resize pointer he clicks in an attempt to resize. As soon as the user clicks, the mouseDown event is raised and the execution control instantly jumps to the mouseDown event. In the mouseDown event we are checking whether the user is trying to resize the column or resize the row. This can be determined just by the current cursor that is being displayed. i.e., if the current cursor is ‘e-resize’ then it means that the user is trying to resize a column and if it is ‘n-resize’ then it means that the user is trying to resize a row. So here we are setting resizing variable to true, cursorType variable to ‘e-resize’ or ‘n-resize’, and the element is a reference to the column or a row. Pay attention to how the element is being set in the ‘e-resize’ and ‘n-resize’ sections.

The line element=object.target; sets the element to the header(TH) and the line element=object.target.parentElement; sets the element to ‘TR’. To set the element to ‘TR’ we need to use the parentElement because the one which captures the mouseDown event is the cell ‘TD’(of a row), but when the user wants to resize he intends to resize the entire row but not a single cell in a row.

Now as the mouseDown event is finished the user continues dragging which makes the mouseMove event be raised, as the resizing variable is true, the control enters the if block of the mouseMove event. In the if block we determine whether its column resize or a row resize.

If it is column resize, then we get the bounds of the column(‘TH’) using the following line of code:

var bounds=Sys.UI.DomElement.getBounds(element);

The bounds of a column are nothing but the X,Y locations and Height and Width attributes of a Column(i.e TH). After that we calculate the new column width which is explained below:

var newColumnWidth =(object.clientX+document.documentElement.scrollLeft)-bounds.x;

Now I check if the new column width is greater than zero, if it is then the column which the user intends to resize is set to the newly calculated width. After that I set the width of the MultiLine textboxes present in that column to the newly calculated width. Also the entire GridView’s width is set to the summation of each column’s width.

And if the user wants to resize a row a similar calculation to determine the new height as shown below is done:

var newRowHeight=(object.clientY+document.documentElement.scrollTop)-bounds.y;

The new row height is set based on the above calculation. After that the multiline textboxes present in the resized row are set to the new row height.

As soon as the user releases the mouse pointer the mouseUp event is raised which sets the resizing variable to false.

The selectStart event simply avoids the text on the headers of the GridView being selected while the GridView is in resizing mode.

Now the last method which needs to be included in the script is the NavigateCell method which is as shown below:

Include this method within the script tags:

function NavigateCell()

{

if(event.keyCode==37)

{

if(currentCellReference.previousSibling.cellIndex!=0)

{

currentCellReference.style.borderWidth=1;

currentCellReference.previousSibling.style.borderWidth=3;

currentCellReference=currentCellReference.previousSibling;

grid.focus();

}

else if(event.keyCode==38)

{

if(currentCellReference.parentElement.previousSibling.rowIndex!=0)

{

currentCellReference.style.borderWidth=1;

currentCellReference.parentElement.previousSibling.children[currentCellReference.cellIndex].style.borderWidth=3;

currentCellReference=currentCellReference.parentElement.previousSibling.children[currentCellReference.cellIndex];

grid.focus();

}

else if(event.keyCode==39 || event.keyCode==9)

{

if(currentCellReference.nextSibling!=null)

{

currentCellReference.style.borderWidth=1;

currentCellReference.nextSibling.style.borderWidth=3;

currentCellReference=currentCellReference.nextSibling;

grid.focus();

}

else if(event.keyCode==40)

{

if(currentCellReference.parentElement.nextSibling!=null)

{

currentCellReference.style.borderWidth=1;

currentCellReference.parentElement.nextSibling.children[currentCellReference.cellIndex].style.borderWidth=3;

currentCellReference=currentCellReference.parentElement.nextSibling.children[currentCellReference.cellIndex];

grid.focus();

}

else

{

if(currentCellReference!=null)

currentCellReference.children[0].focus();

}

function setFocus(object)

{

currentCellReference.style.borderWidth=1;

object.parentElement.style.borderWidth=3;

currentCellReference=object.parentElement;

}

Modify your body tag so that it calls this method when the user presses a key:

What am I doing here is im comparing the ASCII values of the arrow keys 37, 38, 39, 40 and the tab key which is 9. If one of these values are encountered then the focus is navigated to the related cell in the Grid. And if other key codes are encountered then I assume that the user is trying to type and I set the focus to the multiline textbox which is present in the currently selected cell. Finally, the setFocus method is called when the user clicks on any particular cell.

With this we complete on how we can make a GridView behave like an Excel spreadsheet.

Note: A DataGrid can also be extended to behave like an excel sheet. The only difference between a GridView and DataGrid rendering is, GridView renders <TH>s where as a DataGrid doesn't. A DataGrid renders even the column names as <TD>s. So, the code must be slightly changed accordingly to treat the first row's <TD>s as columns.

I hope you enjoyed reading this article. Please post your valuable comments.

reating an Excel Like GridView.

Author: Srikanth Reddy

So, let’s get it started.

The features this GridView provides are:

Resizable columns.
Resizable rows.
Editable cells.
Navigating through cells using the arrow keys and the tab key.
Focussed cell which responds to the key strokes when the user starts typing.