GridView RowDataBound event

When we bind a data source to a GridView in asp.net, we often need to do some processing for each row. For example, we might need to change a row's background color based on some rules. GridView provides a RowDataBound event in which we can get the data of the row and do whatever required.

The event takes two parameters: RowDataBound(object sender, GridViewRowEventArgs e).

To determine if a row's type, we could use: if (e.Row.RowType == DataControlRowType.DataRow).
To get a field data for that row, we could use: string status = (string) DataBinder.Eval(e.Row.DataItem, "PropertyName").

Dead lock encountered today

I have met with a dead lock today in my web service application. It's very hard to find initially because each stored procedure is running fine individually but when tie them together a dead lock occurs.

What happens is I have several stored procedures having insert/update, others only have read access. I put all insert/update stored procedures inside a transaction using ado.net transaction, while decide to leave read only stored procedures outside of transaction. It worked fine until I put a insert statement in one of my read stored procedure, which insert into a same table which is under transaction control. Then dead lock occurs here because the table is locked.

I tried hard to figure out this bug and decided to put everything inside the transaction. Then the bug was fixed.

Some T-SQL cool tricks

1. How to find second highest number in a column:

SELECT MAX(OrderQty) FROM Orders where OrderQty not in (select top 1 OrderQty FROM Orders ORDER BY OrderQty DESC)

2. Using COALESCE to concatenate Comma-Delimited string, this has been discussed in my previous post. This is another solution.

DECLARE @EmployeeList varchar(100) SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + CAST(Emp_UniqueID AS varchar(5)) FROM SalesCallsEmployees WHERE SalCal_UniqueID = 1 SELECT @EmployeeList


3. SELECT * FROM CUSTOMERS ORDER BY 1.
This trick is from my collegue. Note order by 1 works in SQL Server, not sure about other databases. It will order by the first column. Similarly you could order by 2, 3 ..

.net Deployment project variables setup

I was struggling with finding the variebles of a .net compact framework cab deployment project. The variable I wanted to change is Manufacturer. Microsoft says to change it in properties window. I right clicked on the deployment project, and selected Properties. And I was not able to find the variebles.

Where on hell is the Manufacturer variable? I did ask so. After struggling for a while, I found out it's in "Property Window", which means I need to click on the project, and press F4 key. Then I got the window.

Play around with T-SQL decimal and float data types

Decimal data type in t-sql is an interesting thing. It automatically trunc data decimal part in a division operation if precision part is not defined. I have this query:

declare @result decimal
select @result = 2 / 10
select @result

The result is 0.

if I change @result to float, the result is 0.0.

To get what we need for a floating point value, we could do this:

declare @result float
select @result = cast(2 as float) / cast(10 as float)
select @result

Then the result is 0.200000000001

Code Access Security article

There is a great article explaining details of Code Access Security (CAS) in .NET.

http://www.codeproject.com/dotnet/UB_CAS_NET.asp

Infer schema from xml data file when loading data to a dataset

In a Windows Mobile project I have a strongly typed dataset to store relational data, store the data in a xml file, and reload the data from the xml file into the dataset when application starts. One of the requirements from business needs some dynamic table to be added to the dataset. That means the dataset consists of typed, well defined tables and undefined, dynamic tables as well.

When writing data to file system, there is no problem, I simply call dataset.WriteXml(fileName) method and all tables are saved correctly. The problem comes when re-loading data from xml file to the dataset again. When I call dataset.ReadXml(fileName), only defined tables are loaded. Those dynamically generated tables are not.

When looking into some overriding methods of ReadXml, I found dataset.ReadXml(fileName, XmlReadMode.InferSchema) solved my problem. When adding this parameter, all tables are read into the dataset.

Reading more information from Microsoft online resources, I found out this relates to the concept of inference. The actual structure of the xml data itself is the basis for a relational schema. The system tries to load the xml data file into dataset and generate table structures based on the xml data itself.