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.