Using Microsoft Data Access Application Block

Information access has become a major part when you are designing current day applications. There are fair reasons for this because you may live in a agile world or you have to be part of it. In that case time will be the most critical factor. In the other hand you'll have to consider the performance because it will be one of the most important factors when defining the success of your project or application. The bottom line is you have to invest minimum development time to gain a fair amount of performance when designing you application.

Fortunately I was not forced to agile process by my current environment but when I was designing the Data access layer of my new project, I was thinking about these 2 factors and finally decided to use Microsoft enterprise data access application blocks when designing the data access layer. I don't expect to talk about the advantages of enterprise data access application blocks or compare it with other technologies in this post but main points are it doesn't have performance issues in LINQ to SQL or EF like so much of cost/time it takes to view generation. Anyway according to my experience using an ORM reduces your development time but it gives you other headaches like performance issues and less flexibility when it comes to maintenance in the long run.

Enterprise data access application block is a well-tested data access layer. It manages the database connections pretty well and it doesn’t have any memory leaks so it's performance is quite good and It's easy to integrate to your application. Most of all with entlib 5.0 provides some ORM like features.

How to use Enterprise data access application block in your application

You can download the enterprise application blocks from codeplex. Then refer it in your application. Use the configuration tool or just add the relevant entries to web.config.

Now you can create an instance from Database class in entlib. This class consists of all 4 major methods you need to do database calls (ExecuteDataSet, ExecuteNonQuery, ExecuteReader, ExecuteScalar). You can execute SQL statements or stored procedures by using each of these methods.

Executing SQL statement






Executing Stored procedures

Here you don't need to specify the parameter types or names. You just need to pass the parameters to the stored procedure. Only thing you need to make sure is you need to pass the parameters according to the order they are declared in you stored procedure.

Execute stored procedures with output parameters

Those are the classic methods to execute sql statements or stored procedures but with entlib version 5.0 I have noticed 2 new methods called "ExecuteSprocAccessor" and "ExecuteSqlStringAccessor" where you can return user defined element type record sets which make it more interesting to use entlib DAAB. Here I'll explain how to use these 2 methods.

Accessor Methods

First you need to define an entity with properties which needs to be mapped with your table fields.












Write a stored procedure to retrieve data










Use the "ExecuteSprocAccessor" method to retrieve a list of users.




or use the ExecuteSqlStringAccessor method

SQL Server 2008 Management Studio installation failed

I just wanted to install SQL Server 2008 Management Studio on my notebook. I couldn't complete the installation at once since one of the setup rules were started to fail over and over.

I've done this installation numerous times in the past but as I remember I never came across this kind of an issue. I couldn't continue the installation since one of the setup rules were started to fail. Simply the setup couldn't restart the computer. I restarted the machine couple of times but It didn't do any change. Then I realized(assumed) system needs a reboot but it couldn't complete the job it needs to be done in the reboot. So when I do a search regarding the operations need to be done in the reboot, I came across this registry key.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager\PendingFileRenameOperations

Clearing the values in this key resolved the problem for me.

Value of this entry holds the filenames until the system reboots and they'll be renamed. These entries are not created by the operating system.

There is a cool link regarding this topic.

http://www.symantec.com/connect/articles/pending-file-rename-operations

Extending the .Net Menu Control

This is a cool trick that you can use to extend existing script libraries that do not have the built in extensibility features.

In one of the ASP.Net 2.0 projects I was using the ASP.Net menu control. ASP.Net menu control is a cool control with hell a lot of features and flexibility but I couldn't fulfill the need by using any of those.

When you go(mouse over) to a menu item in the menu control, it highlights the menu item. But when you navigate to the next level(sub menu) of the menu, highlighted menu item in the previous level will lose the highlighted characteristic. So if you have a quite big or a menu which has couple of levels and if you are in the menu item which belongs to a latter level of the menu control, you might not know the path of the menu. Bottom line is path of the menu item should keep highlighted(hovered) so the user can see the path by the highlighted menu items. The task was challenging because I could not find a built in feature or an extensibility hook in the menu control itself.

I was looking for the options to extend the control but menu control does not have client side object model or any support from DHTML side of the things which allows the extensibility. When I dig more into the problem I got to know that DHTML object model is just a big freaking hash table and interception will be a way to achieve flexibility/extensibility.

So the basic idea behind the solution is in the javascript interpreter, everything from objects to functions is an entry in the globally defined dictionary. Because of this structure and the fact that javascript is loosely typed, you can substitute anything with anything else. So the inject point of the extensibility of the menu control will be the DHTML portion of the menu control. i will store some of the built in javascript functions in to variables and then set my own functions to those built-in functions. Then I will call the built in function from my own functions then do the rest I need. Actually I'm overriding the built-in function. What a cool way to entend the control.

Then comes the next problem. How do I know the functions I need to override? This is bit cumbersome in ASP.Net 2.0 because of the way it stores and sends stylesheet and javascript files to the browser. I had to open the page source and get the reference to a webResources.axd file. By copying and pasting this reference into the browser, I was able download the stylesheet or javascript file that's being referred.

After downloading the relevant javascript file associated with the menu control, I see the 2 functions I want to intercept.
Menu_HoverDynamic - This is the function that calls when you move the mouse over a menu item
Menu_Unhover - This is the function that calls when you move the mouse off a menu item

Algorithm is pretty simple to retain the "hovered" characteristic in the parent menu item. I used a built-in javascript function in menu control. In mouse over event it finds the item's parent menu item and fires the "mouseover" event of that parent menu item as well. Conversely, when the user moves the mouse off the menu items, it finds the parent menu item of it and fires the "onmouseout" event on that parent menu item. There is no need of any recrusive processing thanks to event bubbling mechanism provided by the DHTML DOM.

The final code follows.


1: <'script' type="text/javascript" language="javascript">
2:
3: var fw_Menu_Unhover;
4: var fw_Menu_HoverDynamic;
5:
6: function SetupInterceptors(){ // called by onload event
7: fw_Menu_HoverDynamic = Menu_HoverDynamic;
8: Menu_HoverDynamic = my_Menu_HoverDynamic;
9: fw_Menu_Unhover = Menu_Unhover;
10: Menu_Unhover = my_Menu_Unhover;
11: }
12:
13: function my_Menu_HoverDynamic(item) {
14: fw_Menu_HoverDynamic(item);
15: var x = Menu_FindParentItem(item);
16: if(x && x.tagName.toLowerCase() != "body")
17: x.fireEvent("onmouseover");
18: }
19:
20: function my_Menu_Unhover(item) {
21: fw_Menu_Unhover(item);
22: var x = Menu_FindParentItem(item);
23: if(x && x.tagName.toLowerCase() != "body")
24: x.fireEvent("onmouseout");
25: }
26:
27: <'/script'>


Here it goes. My custom functions replace the menu control's built-in functions and provide the desired result.

nigella.com has selected to the 101 most useful websites

nigella.com has selected to the 101 most usable web sites by daily telegraph. You can read the full story http://www.telegraph.co.uk/technology/3356874/The-101-most-useful-websites.html

Passing a string of IDs to a WHERE IN clause

Scenario: There is a stored procedure that accepts a comma separated string of Ids as ‘1589, 1586, 1587’.


Stored procedure:

CREATE PROCEDURE [dbo].[Employee_GetByIds]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (@IDs)
END


Issue: application threw an error since the Id field of the Employee table is in the type of int but the parameter (@IDs) is with the type of varchar. From our application where pass the string of ID list will be something like ‘1585,1586,1587’. So we have to hold this value in a string type variable and pass the value to the stored procedure. Stored procedure accepts the value as a varchar type parameter but it uses it in a int type field. That’s where the problem arises.

Solution: Use a table-valued function to insert the list of IDs to a table and use the function within the stored procedure to select the list of IDs from that table instead of comparing the raw ID list.


Splitter function that inserts the list of IDs to a table:

ALTER FUNCTION [dbo].[Splitter] (@IDs VARCHAR(100) )
RETURNS @Tbl_IDs TABLE(ID INT) AS

BEGIN
-- Append comma

SET @IDs = @IDs + ','
-- Indexes to keep the position of searching

DECLARE @Pos1 INT
DECLARE @pos2 INT

-- Start from first character

SET @Pos1=1
SET @Pos2=1

WHILE @Pos1 < LEN(@IDs)
BEGIN
SET @Pos1 = CHARINDEX(',',@IDs,@Pos1)
INSERT @Tbl_IDs SELECT CASE(SUBSTRING(@IDs,@Pos2,@Pos1-@Pos2) AS INT)
-- Go to next non comma character

SET @Pos2=@Pos1+1
-- Search from the next charcater

SET @Pos1 = @Pos1+1
END
RETURN
END


Stored procedure has been altered to use the list of IDs converted to a table value by the splitter function:

ALTER PROCEDURE [dbo].[SourceDocument_GetByIDs]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (SELECT ID FROM Splitter(@IDs))
END

Application Domain

One of the badly behaved DLLs/components of your application can bring your whole application or everything else down. One of the things we can do to resolve this issue is isolate the DLL/component from everything else. What does this isolation means? In pre-.Net days this only means isolating the code through processes.

If you don’t put an extra effort to isolate the components in your application, when you run the application, your whole application will run within a context of a single process. Since windows isolates process from each other through memory address, it’ll share the same memory space. All the components in the application have access to this common memory space shared though out the application. Because of this, a badly behaved piece of code can bring the whole application down.

How can you isolate processes? In pre.Net days COM is one of the technologies that enabled you to isolate processes by allowing a process to call a COM component which is an executable. But the main disadvantage of this method is since the processes can’t share memory or use the same address space, a complex marshalling process has to be used to copy data between the processes. Tough processes are great by considering security, the disadvantage is the performance. Because often number of processes will normally working together and you have to develop data marshalling processes to ensure the communication between those.

So the two main problems need to be addressed were the isolation of the processes and to ensure the marshalling process between processes to copy data between them.

In .Net, application domains are designed in a way that separating the processes without resulting performance problems with passing data between them. The whole idea behind applications domains are a process can be divided in to several application domains(containers). Most probably application domain corresponds to single application. Even though there are different executables, if they are running in the context of the same process, theoretically they can directly see each other’s data and it should share the same address space. But CLR makes sure that it does not happen. .Net Remoting is one of the areas application domains come in to action.

81st Battle of the maroons created the history in sri lankan sports

81st Battle of maroons made another memorable moment in sri lankan sports history by introducing a RF ID for the first time in a sporting event. Spectators were given a RF ID instead of conventional tear off tickets. The gates were equipped with the readers and the movements of the spectators will be monitored as the move in and out though the gates. This will streamline the ticketing process in future. Credit should go to the Battle of Maroons joint committee IT team for designing and implementing the system.

SQL Server structure change scripts

I've been doing re-engineering work of a project for the past couple of months. It's bit difficult to do re-engineering work rather than doing sometihng from the scratch because you'll have a hell of lot of limitations while doing re-engineering work.

These re-engineering work included some database changes like adding relationshipes among tables, changing datatypes, lengths of existing columns. Since the project is in the production enviornment I wanted to do these changes without effecting or dropping existing data in the database. I've used couple of tools.

When you are doing changed to the database objects. As an example when you are changing the datatype of an existing column you can generate the alter script for that change. it will create the alter script without effecting your data.

Step 1 : Changing the datatype of the column RegisteredDate from datetime to smalldatetime

Step 2 : Before saving the table design, right clink on a column and click on Generate Change Script...

Step 3 : You'll see the change script on a popup window

Analyze the genereted script

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Customer

(

Id int NOT NULL,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NULL,

WorkPhone varchar(50) NULL,

MobilePhone varchar(50) NULL,

RegisteredDate smalldatetime NOT NULL

) ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.Customer)

EXEC('INSERT INTO dbo.Tmp_Customer (Id, FirstName, LastName, WorkPhone, MobilePhone, RegisteredDate)

SELECT Id, FirstName, LastName, WorkPhone, MobilePhone, CONVERT(smalldatetime, RegisteredDate) FROM dbo.Customer WITH (HOLDLOCK TABLOCKX)')

GO

DROP TABLE dbo.Customer

GO

EXECUTE sp_rename N'dbo.Tmp_Customer', N'Customer', 'OBJECT'

GO

ALTER TABLE dbo.Customer ADD CONSTRAINT

PK_Customer PRIMARY KEY CLUSTERED

(

Id

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

If you analyse the generated script, there are 4 major operations.

1. create a temporary table with the new column definitions.

2. Inserts data to the temporary table from the existing table.

3. Drop the existing table

4. Rename the new/temporary table to the name of the existing table.

So you won't lose any data.

This is a feature provided by SQL Server but you can download this database publishing tool as a separate software.