Wednesday, April 15, 2009

Replicate the SQL IN keyword in LINQ to SQL



LINQ To SQL has made the life of .NET developers easier by replicating most of the familiar SQL keywords (SELECT,FROM,WHERE,JOIN..) within the scope of the language.It is easier to construct a LINQ to SQL expression for the developers who are used to in raw SQL syntax against different databases.One of the important SQL keyword is "IN" which is used frequently in the "WHERE" clause of SQL.

For example:

SELECT CustomerID,Name FROM Customers WHERE City IN ('Dhaka','Rajshahi','Chittagong','Khulna')

While constructing this query in LINQ to SQL, the usual solution may come to the mind like this:

C#

string listOfCity = "Dhaka,Rajshahi,Chittagong,Khulna";

MyDataContext context = new MyDataContext ();

var result = from cust in context.Customers
where cust.City in listOfCity
select cust.CustomerID,cust.Name;

return result.ToList();


Actually the above expression does not work and with a little
twist we can make it up and running.The correct code is:

List<string> listOfCity = new List<string>();

listOfCity.Add("Dhaka");
listOfCity.Add("Rajshahi");
listOfCity.Add("Chittagong");
listOfCity.Add("Khulna");

/* We can also use array of string as the following

string[] listOfCity = { "Dhaka","Rajshahi","Chittagong","Khulna" }; */

MyDataContext context = new MyDataContext ();

var result = from cust in context.Customers
where listOfCity.Contains(cust.City)
select cust.CustomerID,cust.Name ;

return result.ToList();


The trick is that we define a generic list of string or an array of string.Then we use the <List>.Contains(<Database column name>) syntax to replicate the SQL "IN" syntax in LINQ To SQL.

Monday, April 6, 2009

Decision making in SSIS depending upon condition



SSIS(SQL Server Integration Service) is not much flexible in the case of decision making based on a condition. In many cases you feel the need of a “Condition” block that can be used in the control flow to decide the alternative paths. For example if you want to copy a file named “AnyFile.txt” into a folder named “Current” if the file does not exist in a folder named “Previous”, otherwise move the file to a folder named “Archive”, there is no direct way to test the “If File Exists in Previous Folder” condition. One possible solution is to use the “Script Task”. The steps are as follows:

1. Declare a package level Boolean variable named “IsFileExistsInPrevious” for example.

2. Create a script task and pass the “IsFileExistsInPrevious” variable as “ReadWriteVariable” into it. Pass other “ReadOnly” variables that contain the name of the file if necessary.

3. Using the components in the .NET “System.IO” namespace, check the existence of the file in the “Previous” folder. If the file exists, set the value of the “IsFileExistsInPrevious” variables to “True”. Otherwise set it to “False”.

VB .NET Script:

Dim srcFilePath As String = ""

If File.Exists(srcFilePath ) Then
Dts.Variables("IsFileExistsInPrevious").Value = True
Else
Dts.Variables("IsFileExistsInPrevious").Value = False

End If
Dts.TaskResult = Dts.Results.Success


4. Declare two “File System” tasks that copies files to the “Current” or “Archive” folders depending on the result of the previous “Script Task”. Drag the “Precedence Constraint” line from the previous “Script Task” to any of the following “File System” tasks.

5. Right click the “Precedence Constraint” line and Select “Edit”. In the “Precedence Constraint Editor” window, Select the value of “Evaluation Operation” to “Expression”. Type the following into the “Expression” box.

@[User::IsFileExistsInPrevious]==false

6. Drag the “Precedence Constraint line to the “File System” task that copies the file to the “Current” folder.

7. Right click the “Precedence Constraint” line again and Select “Edit”. In the “Precedence Constraint Editor” window, Select the value of “Evaluation Operation” to “Expression”. Type the following into the “Expression” box.

@[User::IsFileExistsInPrevious]==true

8. Drag this “Precedence Constraint line to the “File System” task that copies the file to the “Archive” folder.

The key point to note here is the usage of the “Precedence Constraint” editor.The “Expression” option enables to select alternative paths depending on condition (the value of a user variable “IsFileExistsInPrevious” in this case).