asp.net This article will create a DataGrid that contains a CheckBox control that allows users to select multiple columns for detail browsing. If you do not recover a method that obtains this functionality for dynamic SQL, you must use the in operation.
At the end of the article, we wrote a SQL Server user-defined function (UDF) to decompose a string into a delimited substring. In this article, we can see how this UDF comes in handy. We will create a Web form where users can select some records in the DataGrid by selecting the CheckBox control. The details of these checked records will appear in another DataGrid in the form. This form looks like the figure below.
The following shows the aspx we used to create the form. Note: How to use the TemplateColumn and CheckBox controls to increase the DataGrid column. We also use the DataKeyField property of the DataGrid to tell the object which field in the database records will contain the first line of the keyword designator.
<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>
<asp:button id= "Orders" runat= "Server" text= "View Orders" > </asp:Button>
<asp:datagrid id= "DataGrid2" runat= "Server" autogeneratecolumns= "True"/>
</form>
The top DataGrid needs to be assembled when the form load is initialized. 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 to load the 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 the second data table that matches the employees in the database and is related to the orders data. One way to do this is to create dynamic SQL and use the or condition of the where statement required by all employeeids.
The second method is to use the in operation of the where statement. The in operation will compare a column of expressions. For example, the following statement returns information between IDs 7 and 4 in employee.
SELECT EmployeeID, FirstName, LastName from Employees WHERE EmployeeID in (7, 4)
In concept, I would like to use a single string parameter to query the transmitted IDs, however, perhaps as a single string, you cannot use a single string parameter for the in operation. If so, the SQL statement "WHERE Employee in (' 7,4 ')" and the database returns an error message because the EmployeeID belongs to the type int-does not belong to the varchar type.
However, we use the Split function constructed in the article to separate the strings into different values. Pass the string ' 7,4 ' to the split function, and we get two records corresponding to the values 4 and 7. SQL queries that select employees and calculate the total number of their orders will 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 required to use the above query is that @employeeids parameters must be established and passed. This parameter will be a comma-separated list of IDs. In order to create this string, in order to figure out whether the row was chosen by the user, we need to use a loop, which loops in number of lines, and examines each CheckBox control. If the user selects a row, the keyword is saved in employee by extracting the inspector from the table's DataKeys property (which is built into 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". Clicking the event handler on the Orders button will involve a way to pass the information to SQL to get a list of employees and orders and bind its results 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 ();
}
}
}
