O/R Mapping with NHibernate

I just downloaded an O/R Mapper, NHibernate from http://wiki.nhibernate.org. Its a pretty cool stuff. After using this or any other O/R Mapper you will see that you don't need stored procedures in your application and everything can be done using relationship mapping. I am writing series of articles about using NHibernate which will be published on www.codersource.net so don't forget to check them out ;)

Exporting DataGrid to excel and keeping the format intact

I had to export datagrid to excel which is a pretty simple task. The problem was that excel was formatting the numbers in its own format. Meaning that 09898987 in the datagrid is written as 9898987. There is a way to fix this problem.

Follow the link below (Thanks to Sonu kapoor and juss55) :

http://forums.asp.net/893621/ShowPost.aspx

Numbering Column when returned from Database

Sometimes we need to return the sequence numbers along with the records. If we are returning 10 rows based on some condition and we need to add a new column which represent each row uniquely we can use the following query.

(Special thanks to Adam Machanic)

SELECT
COUNT(*) AS "New Column",
P1.ArticleID,
P1.Title,
P1.Author
FROM Articles AS P1
JOIN Articles AS P2 ON
P2.Author = P1.Author
AND P2.ArticleID <= P1.ArticleID WHERE P1.Author = 'AzamSharp' GROUP BY P1.ArticleID, P1.Title, P1.Author

MSDN Event in Houston

Just came from the Microsoft MSDN Event that happened in Houston. It was about Web Services, SQL SERVER 2005 and Reporting Services, Visual Studio 2005 Beta 2 . It was mainly about web services and how we can secure the webservices using WS SECURITY. Introduction to Visual Studio 2005 Beta 2 was also kinda cool althought the presenter pointed out some bugs.

This was my first attendence at the event and it turned out pretty good. Looking forward to go again and learn more stuff. :)

Migration from independent data access blocks to Enterprise Library

As we all know that Enterprise Library has been released and is being used widely. At my job I also need to migrate the existing application which is made using Data Access Application Block to the Enterprise Library. There are many methods of doing so but the most reliable is to do it manually and check each unit behaviour after conversion. You can also use find-replace methods but these techniques are not error free.

If you come up with a better solution for this migration than post it here.

Enterprise Library Caching Application Block

Caching application block of Enterprise Library is pretty cool. Using the common patterns that Microsoft have already given in quick start tutorial I was able to make a simple application using caching. You can also have multiple caching expiration policy.

Some of them are given below:

private DataSet LoadData()
{
DataSet ds = (DataSet) myCache.GetData("MyData");


if(ds!=null)
{
return ds;
}
else
{
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper selectCommandWrapper = db.GetSqlStringCommandWrapper("SELECT * FROM Articles");

ds = db.ExecuteDataSet(selectCommandWrapper);
myCache.Add("MyData",ds,CacheItemPriority.Normal,null,new Microsoft.Practices.EnterpriseLibrary.Caching
.Expirations.AbsoluteTime(TimeSpan.FromMinutes(5)));

myCache.Add("MyData",ds,CacheItemPriority.Normal,null,new Microsoft.Practices.EnterpriseLibrary.Caching.Expirations
.SlidingTime(TimeSpan.FromMinutes(2)));

myCache.Add("MyData",ds,CacheItemPriority.Normal,null,new Microsoft.Practices.EnterpriseLibrary.Caching.Expirations
.FileDependency(Server.MapPath("MyXmlFile.xml")));


myCache.Add("MyData",ds,CacheItemPriority.Normal,null,new Microsoft.Practices.EnterpriseLibrary.Caching.Expirations
.ExtendedFormatTime("* * * * 0 0 "));

return (DataSet) myCache.GetData("MyData");
}

Populating multiple dropdownlists using one data access

Few days ago I had to populate the DropDownLists in one webform with data from the database. The problem was that there were 10 dropdownlists and I had to populate them in one access to the database. Good thing datatable was there to save me.

I used select statements to get the result into the dataset and simply used datatable to populate the appropriate dropdownlists.

Here is the code:

Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper("Test");

DataSet ds = db.ExecuteDataSet(selectCommandWrapper);



ddlList.DataSource = ds.Tables[0];
ddlList.DataTextField = "Name";
ddlList.DataValueField = "ArticleID";
ddlList.DataBind();

ddlList1.DataSource = ds.Tables[1];
ddlList1.DataTextField = "Name";
ddlList1.DataValueField = "CodeSampleID";
ddlList1.DataBind();

Displaying Messages to the user

Most of the time we need to display messages to the user these messages can be as simple as "Data has been inserted". The most common pattern that we use is something like this:

int result = Get the value from the database identifying that if the data has been inserted or not

if(result==1)
Label1.Text = "Data has been inserted";
else
Label1.Text = "Data not inserted";

This is a common pattern that is being repeated all over the application. You can replace this by making a class which deals with messages. You can also store the messages in Sql Server 2000 as well as Xml file.

I wrote an article on this issue which will be soon published on www.codersource.net.

Using FxCop in your .NET Applications

I just started using FxCop with my applications and I have to say that its awesome. FxCop is basically a small application that reads your project's dll file and let you know that if its compliant with the Microsoft.net rules.

It's very easy to use and can benefit you inheriting good programing practices. You can download it for free

http://www.gotdotnet.com/team/fxcop/

Using View State or Caching

Today I ran into a confusion that whether to use Caching or ViewState. I have a page that postback to itself and displays certain events in the events calendar. Each time on the page load I need to find out that if any new events had been added or not. First I was sending requests to the database but this was not a good idea because database operations on each page load event were costing me a furtune. I turned to caching and solved the problem. But later I realized that since the page is posting back to itself hence making it a perfect candidate for viewstate.

After some thinking I decided to go with caching instead of viewstate. The problem with viewstate is that it stores the information in the hidden fields and hence makes the size of the page larger. If you have lot of data which is to be stored in viewstate than the page execution will be very slow since the page size will be large.




Here is final code which uses Caching to retrieve the data:

if(Cache["Events"] == null)
{
// Binding from the Database
Response.Write("Binding from the Database");
BindData();
}
else
{
Response.Write("Binding from the Cache");
myList = (ArrayList) Cache["Events"];
}

}

private void BindData()
{
Events events = new Events();
myList = events.GetEventsInCalendar();
// Put the list in the Cache
Cache["Events"] = myList;
}

Event Calendar User Control

Event Calendar has really been helpful to organize your events. So I decided to make one of my own. Its pretty much simple and is based on the Asp.net Calendar Server Control. You can download the "Event Calendar" user control from my website with complete source code:

http://azamsharp.net/ServerControls.html

Scroll Bar in the datagrid control

Sometimes we need to mainted the size of the datagrid to one page this can be done using paging. But paging occurs with the postback which is not good as performance point of view. I am pretty sure that you can also do paging using client side code.

Anyway here is another way which solves the problem by introducing scroll bars to the datagrid control. Here is a simple code that you can use in the division tag to introduce the scroll bars.


Check out this link:
http://geekswithblogs.net/aferrandiz/archive/2004/08/09/9498.aspx

Here is another link that tells that how you can maintain the scroll position in the datagrid scroll bar. This only works for the Internet Explorer Browser:

http://authors.aspalliance.com/JimRoss/Articles/MaintainScrollPos.aspx

In order to maintain the position for most of the browsers you can use the "SmartScroller" control. Check out this link http://www.dotnet-news.com/lien.aspx?ID=8622

Updating multiple records in the database

I came to a small problem when I had to update multiple records in the database. Offcourse I can just make a for loop and go to database and update the records. But think about it what if you have 100000 records. Each time going to the database is not a good idea. I came to a solution which only goes to the database once and updates the records. This is not the best solution but it works.

Here is the snippet of the code:

updateTasks is an arraylist which contains the primary keys which are to be updated.

StringBuilder command = new StringBuilder();

for(int i=0;i<=updateTasks.Count-1;i++) { command.Append("UPDATE Tasks SET STATUS = 0 WHERE TaskID="+updateTasks[i]); } the command object is later executed using the SqlCommand instance. I will be posting different methods of performing the same feature in my blog. One other method that I used is using the User Defined Function. You can view the article about using this approach at the following Url: http://www.4guysfromrolla.com/webtech/031004-1.shtml


And the final method that I found was making the use of the XML document. This is the most effiecient method of doing the mutiple inserts, updates and deletes.

Here is the link to the code sample:

http://weblogs.sqlteam.com/travisl/archive/2005/01/04/3931.aspx