Using SQL's IN operation in ASP.NET

Author：Eve Cole Update Time：2009-06-30 15:39:36

This article will create a DataGrid containing a CheckBox control. This control allows users to select multiple columns for detailed browsing. If there is no way to recover this functionality for dynamic SQL, then the IN operation must be used.

At the end of the article, we wrote a SQL Server user-defined function (UDF) in order to break a string into delimited substrings. In this article, we can see how such a UDF can come in handy. We will create a web form where the user can select some records in the DataGrid by selecting a checkbox control. The details of these checked records will appear in another DataGrid in the form. This form looks like the picture below.

Shown below is the ASPX we used to create the form. Note: How to use TemplateColumn and Checkbox controls to increase DataGrid columns. We also use the DataGrid's DataKeyField property to tell the object which field in the database record will contain the key identifier of the first row.

<hr>

<asp:Button id="Orders" runat="server" Text="View Orders"></asp:Button>
<hr>
<asp:DataGrid ID="DataGrid2" Runat="server" AutoGenerateColumns="True" />
</form>

When the form is loaded and initialized, the top DataGrid needs to be assembled. The code uses the Enterprise Library to access the SQL Sever Northwind example database and executes the "SELECT EmployeeID, FirstName, LastName FROM Employees" statement. The code for the loading event is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper dbCommandWrapper;

using(dbCommandWrapper = db.GetSqlStringCommandWrapper(SELECT_EMPLOYEES))
{
using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
{
DataGrid1.DataSource = dataReader;
DataGrid1.DataBind();
}
}
}
}

When the user clicks the "Orders" button, we want to display a second data table that matches those in the database with Employees and is related to the Orders data. One way to do this is to create dynamic SQL and use the OR condition of all the EmployeeIDs required for the WHERE statement.

The second method is to use the IN operation of the WHERE statement. The IN operation compares a list of expressions. For example, the following statement returns information between employee IDS 7 and 4.

SELECT EmployeeID, FirstName, LastName FROM Employees WHERE EmployeeID IN (7, 4)

Conceptually, I would like to use a single string parameter to query the passed IDs, however, perhaps as a single string, it cannot be used for the IN operation A single string parameter. In that case, the SQL statement would be "WHERE Employee IN ('7, 4')" and the database would return an error message because EmployeeID is of type int - not varchar.

However, we use the split function constructed in the article to separate the string into different values. Pass the string '7, 4' to the split function and we will get two records corresponding to the values 4 and 7. A SQL query to select employees and count their order totals would be as follows:

SELECT count(*) AS Orders, E.FirstName, E.LastName

FROM Orders O

INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID

WHERE E.EmployeeID IN(SELECT Value FROM fn_Split(@employeeIDs, ','))

GROUP BY FirstName, LastName

ORDER BY count(*) DESC

What is needed to use the above query is that the @employeeIDs parameter must be established and passed. This parameter will be a comma separated list of IDs. To build this string, to find out if the row was selected by the user, we need to use a loop that iterates through the number of rows and checks each checkbox control. If the user selects a row, the key is saved in employee by extracting the examiner from the table's DataKeys property (which was created in the ASPX file to point to the EmployeeID field).

private string GetCheckedEmployeeIDs()
{
String delimiter = String.Empty;
StringBuilder employeeIDs = new StringBuilder();
for(int i = 0; i < DataGrid1.Items.Count; i++)
{
CheckBox checkbox;
checkbox = DataGrid1.Items[i].FindControl("EmployeeCheckBox") as CheckBox;
if(checkbox != null && checkbox.Checked == true)
{
employeeIDs.Append(delimiter + DataGrid1.DataKeys[i].ToString());
delimiter = ",";
}
}

return employeeIDs.ToString();
}

The above method returns a string, like "10, 7, 20". The Orders button click event handler will involve a method that passes information to SQL to get a list of employees and orders, and binds the results in a second DataGrid object.

private void Orders_Click(object sender, System.EventArgs e)
{
string employeeIDs = GetCheckedEmployeeIDs();
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper dbCommandWrapper;

using(dbCommandWrapper = db.GetSqlStringCommandWrapper(SELECT_ORDERS))
{
dbCommandWrapper.AddInParameter("@employeeIDs", DbType.String, employeeIDs);
using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
{
DataGrid2.DataSource = dataReader;
DataGrid2.DataBind();
}
}
}