At the end of our last article, we wrote a SQL Server user-defined function (UDF) to split a string into sub-strings based on a delimiter. In this article, we will see how such a UDF can come in handy. We will be building a web form where the user can select a number of records in a DataGrid by checking check box control. Details about these checked records will appear in a second DataGrid on the form. The form will look like the following figure.
The ASPX we use to build the form is shown below. Notice how we are using a TemplateColumn to add the DataGrid column with the Checkbox controls. We also use the DataKeyField property of the DataGrid to tell the object which field in the database recordset will contain the primary identifier for each row.
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid1" runat="server"
AutoGenerateColumns="False" DataKeyField="EmployeeID">
<Columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:CheckBox runat="server" ID="EmployeeCheckBox" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "LastName") %>
,
<%# DataBinder.Eval(Container.DataItem, "FirstName") %>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
<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 initially loads, we need to populate the top DataGrid. Our code is using the Enterprise Library to access the SQL Server Northwind sample database and execute "SELECT EmployeeID, FirstName, LastName FROM Employees". The code for the Load event is shown below.
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 on the ‘Orders’ button, we want to display the second data grid with just those selected Employees and the number of Orders they have in the database. One way to do this would be to build a dynamic SQL and with a WHERE clause OR-ing all the employee IDs we need.
A second approach is to use an IN operator with the WHERE clause. The IN operator will take a list of expressions to compare against. For example, the following statement would return information on employee IDs 7 and 4.
SELECT EmployeeID, FirstName, LastName FROM Employees WHERE EmployeeID IN (7, 4)
Ideally we would like to pass the IDs to look for in a single parameter, perhaps as a single string, however, we cannot use a single string parameter for the IN operator. If we did, the SQL statement would look like “WHERE EmployeeID IN ('7, 4')”, and the database will throw back an error message since EmployeeID is of type int – not varchar.
We can, however, use the split function we built in the last article to break apart the string into distinct values. Pass the split function the string ‘7,4’ and we will get back two records with the values of 7 and 4. The SQL query to select the employees, and count their total orders, will look like the following.
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
All we need to do to use the above query is to setup and pass the @employeeIDs parameter. The parameter will be a comma delimited list of IDs. To construct the string, we need to loop through the rows of the grid and inspect each checkbox control to see if the row was selected by the user. If the user selected the row, we save the primary key for the employee by pulling their identifier from the grids DataKeys property (which was setup 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 will return a string, like “10,7,20”. The event handler for the Orders button click event will invoke the method, pass the information to SQL to get back the list of employees and orders, and bind the result to the 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();
}
}
}
Subscribe
Twitter
Search
About
Learn C#