Saturday, June 20, 2009

Using Sql In Operator In Select Function of DataTable

During work related to storing the sql server tables in local cache like in DataSet, Data table, I often need to use the Select function of the data table. To which I have to pass some expression to filter the records of the table, I have used simple equality expressions to filter the records. But few days back I have to filter the record based on the Ids, Where I have to pass the multiple values of the key columns, and I have to use the in operator of the sql server in C#. Here is the simple example to share my knowledge with you.
For this example I have sued the region and the territory table of the northwind database and I have store the database in simple xml format so if anyone of you don't have sql server or northwind database the example still run fine. For the Gui I have added check box list control which is used to bind the region table and then I have the grid view control which is used to display the filtered record when user select the region from the check box list and then press search button. Here is the main screen of the example.

Here is the code for the search button which is used to built the comma separated list of the region id in a string format. In this example I have set strRegionIDs to globally static type, as I have set the allow paging of the grid to true, so if user set the filter criteria and move to the next page then same filter criteria will be used.
protected void cmdSearch_Click(object sender, EventArgs e)
{
strRegionIDs = string.Empty;
foreach (ListItem listItem in chkRegion.Items)
if (listItem.Selected)
{
if (strRegionIDs.Trim() == string.Empty)
strRegionIDs = listItem.Value.ToString();
else
strRegionIDs += "," + listItem.Value.ToString();
}
grdvResultGrid .DataSource = GetGridDataTable();
grdvResultGrid.DataBind();
}
And here is the definition of the GetGridDataTable function. This function will be used when paging on the grid is clicked and when user press the search button on the main screen. At the start of the function I have declared the tblReturnTable variable which is returned from this function. I have used the Clone function of the second table so that the structure of the tblReturnTable have same structure of the second table in the dataset have which is the territory table. Next I have the strexpression variable which is used to have the filter criteria in this case it will contain the in operator of the sql. Note that I have used same structur of the in operator just like sql used it.
private DataTable GetGridDataTable()
{
DataTable tblReturnTable = dsRegions.Tables[1].Clone();
string strExpression = string.Empty;

if (strRegionIDs.Trim() != string.Empty)
strExpression = "RegionID in (" + strRegionIDs + ")";

DataRow[] dtrMatchResult = dsRegions.Tables[1].Select(strExpression);
foreach (DataRow dtrCurrentRow in dtrMatchResult)
tblReturnTable.ImportRow(dtrCurrentRow);

return tblReturnTable;
}
After filter the records from the second table of the dataset (territory table) I have loop through the resultant rows and place them in the new table and then return that table. In this example I have also set the dsRegion dataset set as stattic so I have to populate it once the page is loaded. You can download the source code from here.

All and any comments / bugs / suggestions are welcomed!


1 comment:

Akash said...

Thank u ...