View Summary.cs
View Summary.aspx
The ASP.NET DataGrid control presents a multi-column, fully templated grid, which is the most versatile and flexible control in all data binding Web controls in the .NET framework. DataGrid's user interface is some extent similar to the Microsoft Excel worksheet. Although the DataGrid has advanced programming interfaces and a complete property set, it only generates an HTML table containing a staggered hyperlink to provide interoperability (eg, sorting and paging commands).
Using the DataGrid control, you can create a simple data binding column (display data from the data source), template column (allowing you to design the layout of cell content), and finally, it is also important to command-based columns (allow you Add a specific function to the grid).
The DataGrid control is ideal for reporting data, and its adequate flexibility allows you to build complex and professional data tables, which can be used in these data tables, such as paging and sorting functions can be implemented. However, other features (for example, deepening and master / minus) require less work. In this month's column, I will discuss an unable to be provided by the control itself, but is quite popular with many people. Therefore, let us study how to automatically generate complex reports, in these tables, must be displayed with some of the total summary lines.
DataGrid item
You can bind the actual data to the example of the control by setting the DataSource property of the DataGrid control. This property is a general Object type and supports two configuration schemes. It is usually set to set it with a data object that implements the ICollection interface. Dataable and DataView objects will usually use. Another method is to set it with another object type (eg, Data Reader object). However, in this case, the custom paging mechanism must be opened; otherwise the exception will be triggered. Simply put, you either bind DataGrid to the paging data source (ie, the collection object used to implement the number of enumerations), or you must provide paging for yourself.
For web controls, the data binding is enabled, and only when the DataBind method is called, the user interface is refreshed. During the refresh, the control will traverse the data source and copy some rows into its Items collection. The items property represents the content of the currently displayed page. If the data source supports paging (ie, implementing iCollection), DataGrid selects the correct row of subsets that suits the current page from DataSource. Otherwise, it assumes that all content of DataSource is suitable for the current page and load them all into items. The user interface of the control will be presented after filling the items.
What lesson is there? The DataGrid control is secure and consistently displayed is the row included in the binding data source. Therefore, if you want to insert a summary line to group a total of some records in accordance with a public key, you must point out how the part of the data source is directly inserted into the data source.
However, it is not enough to insert the summary bank into the data source. In fact, you must distinguish between summaries and ordinary rows, and present the former with different visual styles.
Make sure that the data source contains all the summary banks they need before attaching the data to the control. Then, the itemcreated event is hooked, detects each summary line, and then draws them with different layouts and patterns. Let us see how to insert a summary line between different lines of SQL queries. I will use the sample application based on the Northwind SQL Server database to explain my point of view. The app lists all orders that each customer has been issued at a given year. The order is grouped by year and the customer ID. For each customer, there is an additional line to summarize the total number and total amount. Data packet
The following SQL commands select all orders issued in a given year. Only the sum of each item price of each order.
Select O.Customerid, Od.orderid, SUM (Od.quantity * Od.Unitprice) AS PRICE
From Orders O, [Order Details] OD
WHERE Year (o.orderdate) = @theyear and odd.orderid = o.orderID
Group by o.customerid, odd.orderid
Order by o.customerid
In the T-SQL language, the Group By clause of the SELECT statement provides the WITH ROLLUP clause to add a predefined summary line to the result set. Of course, such a summary row has a layout of all other columns, but the content of each column can perform some degree of customization. The following statement explains how to modify the above command to allow it to use the summary bank.
Declare @Theyear Int
Set @theyear = 1998
SELECT
Case Grouping (O.Customerid) When 0
THEN O.CUSTOMERID ELSE '(TOTAL)' End as mycustomerid,
Case grouping (odd.orderid) when 0
Then Od.Orderid else -1 End as myorderid,
SUM (Od.quantity * Od.Unitprice) as Price
From Orders O, [Order Details] OD
Where yeardate = @theyear and odderid = o.orderID
Group by o.customerid, Od.Orderid with rollup
Order by O.Customerid, Price
If you copy the code snippet and paste into the SQL query analyzer, you will see the content shown below.
Figure 1. With rollup clause adds summary to the result set
Grouping is a T-SQL aggregate function that works with Rollup in the main body of Group By clauses. Using the Grouping operator will add the new column to the result set. If the row has been added by the RollUp operator and therefore become a summary line, the new column will contain a value of 1. Otherwise, the column will contain a value of 0. Use the Case..when..end statement to merge this new column with the group column.
In the above example, the MyCustomerID column contains the value of the CustomerID column and the string "(TOTAL)" in the row created by the group. Similarly, when the line represents a small meter, the MyorderID column contains orders ID and -1.
In order to summarize the data, SQL Server also provides several options, such as with Cube operators, and compute BY clauses. As you think, although an option is functional to cross another option, all of these options are not completely equivalent. In particular, with Cube generates a summary line for each possible combination of the results integral group and subgroups. The WITH ROLLUP is packet according to the specified order of the group. Finally, Compute By (SQL Server 2000 supports its purpose only in the working mode of rear compatibility) is substantially the same as with rollup, and the difference is that it returns a plurality of result sets, and is not as good as ROLLUP efficiency when processing by query optimizer. high. Display group data
When bind to the DataGrid control, the result set returned by the SQL command seems to be shown below.
Figure 2. Result set displayed by the DataGrid control
The DataGrid control used in the sample application is declared as follows:
AutogenerateColumn = "false" AllowPaging = "true" Pagesize = "15" Font-size = "xx-small" Cellspacing = "0" Cellpadding = "4" gridlines = "Both" BorderStyle = "Solid" bordercolor = "skyblue" borderwidth = "1" OnItemcreated = "itemcreated" OnpageIndexchanged = "PageIndexchange"> Font-size = "10pt" prevpageText = "3" nextpagetext = "4" /> DataFormatString = "{0: C}"> The data source obtained using the WITH ROLLUP operator already contains all the information necessary to generate a valid report. You may have already noticed that the statement adds a top row, which contains the total set of orders issued by all customers. When using the WITH ROLLUP operator, if you modify the order of the packet line, the number and structure of the generated line may change significantly. This extra line is that I choose the result of using a specific syntax. If you don't need this information, just remove it from the result set before the binding. Alternatively, the row can be moved to the bottom of the data set. The code displayed below explains how to execute the ROLLUP statement. The parameters read from the text box are the year to consider. The result set is temporarily stored in the DataSet object. In this example application, I will cache the DataSet object in the Session slot. In practical environments, this should be an emphasis. Typically, any bytes stored in the session have a sufficient reason to be there. Private Dataset PhysicalDataRead () { String strcnn = "server = localhost; database = northwind; uid = sa;"; SqlConnection Conn = New SqlConnection (STRCNN); // Command Text Using With Rollup StringBuilder SB = New StringBuilder (""); Sb.append ("SELECT"); Sb.Append ("Case Grouping (O.Customerid) When 0); Sb.append ("THEN O.CUSTOMERID ELSE '(Total)' end as mycustid,"); Sb.append ("Case Grouping (Od.Orderid) WHEN 0); sb.append ("" "" "); Sb.append ("SUM (OD.quantity * Od.Unitprice) as price"); Sb.Append ("from Orders O, [Order Details] OD"); Sb.append ("Where Year (ORDERDATE) = @ nyear and orderid = o.orderid"); Sb.append ("GROUP BY O.CUSTOMERID, OD.Orderid with rollup); Sb.append ("Order By O.Customerid, Price"); String strcmd = sb.toString (); SB = NULL; SQLCommand cmd = new sqlcommand (); Cmd.comMandText = strcmd; cmd.connection = conn; SqlDataAdapter Da = New SqlDataAdapter (Strcmd, Strconn); Da.selectCommand = CMD; // set the "year" parameter SQLParameter P1 = New Sqlparameter ("@ nyear", sqldbtype.int); P1.direction = parameterDirection.input; p1.value = convert.toint32 (txtYear.text); cmd.Parameters.Add (P1); DataSet DS = New Dataset (); Da.fill (DS, "Orders"); Return DS; } In order to make the summary bank clearly display in the page of the grid, you need to change the style and layout of the summary bank. This can be done in the ItemCreated event handler. The design idea is to detect the summary bank by checking the order ID, and then modify the layout and style of the cell. In the result set, the summary line is characterized by the order ID to -1. Value -1 is any value from the used statement. Case Grouping (Od.Orderid) when 0 Then Od.Orderid Else -1 End As MyorderId If you do not use the Grouping operator for the ORDERID column, the value of this column will be NULL for the summary bank. Modify layout and style DataGrid allows you to modify the style and layout of the component cell, which can be done by hook the itemcreated event. This event will be excited each time the control is handled each time (header, footer, line, page navigation). The event handler receives the parameters of the type DataGriditeMeventArgs, which you can extract the types of the project processed from this parameter. The summary of the summit is a DataGrid line, and the same type can be Item or AlternatingItem. Therefore, when writing the ItemCreated handler, make sure that the corresponding cell is handled only when the type of the item is correct. The following list summarizes the required code. Public Void Itemcreated (Object Sender, DataGriditeMeventArgs E) { // Get The Type of the Newly CREATED ITEM ListitemType ItemType = E.Item.ItemType; IF (itemtype == ListItemType.Item || ItemType == ListItemType.alternatingItem) { // Get the data bound to the current row DataRowView DRV = (DATAROWVIEW) E.Item.DataItem; IF (DRV! = NULL) { // check here the app-specific Way to detect WHether THE // Current Row Is A Summary Row : } } } If the created item is a DataGrid item (or alternate), you can access data binding to rows over the DataITEM property. DataItem properties point to different row objects based on the type of object bound to DataGrid. If the grid is bound to the DataView, you will get a DataRowView object; if the source is represented by a DataTable object, a DATAROW object is obtained. In this sample application, I fill the grid using the DataView object. Later, a single line of data becomes a DataRowView object. After you have a data line object, you can apply some application-specific rules to determine if the line is a summary line. In this example application, the MYORDERID field of the summary bank is set to -1. IF ((int) DRV ["MyorderID"] == -1) { // modify style and layout here. // -> set the background color to white and use bold font E.Item.backcolor = color.white; E.Item.font.bold = true; } DataGrid now looks as shown below. Figure 3. Summary of the white display with bold The DataGrid line is actually just a line in the table. Similarly, use it can make cell deletion and other adjustments. Let's take a look at how to use a single cell across all existing columns to present the summary bank. IF ((int) DRV ["MyorderID"] == -1) Figure 4. Summary of the custom layout In these three original cells, the first two deleted, the third (now containing index 0) is correctly aligned and spans the outer table. If you want to display some custom text on the summary line, you need to do your preparation for other issues. Suppose you need to add some text to comment on the small meter, and at the same time, the small tap is in the same column with a single set. In this case, just delete a cell. E.Item.cells.removeat (1); // Remove the Order # Cell E.Item.cells [0] .Columnspan = 2; // span the custom CELL E.Item.cells [1] .hizontalalign = horizontalalign.right; E.Item.cells [0] .Text = "Total IS"; The results of this code are as follows. As you can see, it is not exactly the same as your expected results. The first cell of the summary bank does not have the text you just set. What is going on? Figure 5. Summary line with a modified custom layout An important point to consider here is that Item and AlternatingItem are binding. Their clarity text is only set during the onItemDatabase event. You may have guess that onItemDatabase is excited after creating this item. Therefore, any text assigned to a cell when processing itemcreated is later rewritten by a silent manner later. OnItemDatabase is hooked by setting the DataGrid onItemDatabase. AutogenerateColumn = "false" : OnItemcreated = "itemcreated" OnItemDatabaseD = "itemdatabase" OnpageIndexchanged = "PageIndexchange"> THE STRUCTURE OF THE CODE for ItemDatabase is Shown Below. Public Void ItemDatabase (Object Sender, DataGriditeMeventArgs E) { DataRowView DRV = (DATAROWVIEW) E.Item.DataItem; IF (DRV == null) Return; IF ((int) DRV ["MyorderID"] == -1) { IF (DRV ["Mycustomerid"]. TOSTRING () == "(TOTAL)") { E.Item.backcolor = color.yellow; E.Item.cells [0] .Text = "ORDERS TOTAL"; } Else E.Item.cells [0] .Text = "Customer Subtotal"; } } The top one is drawn on a yellow background, which shows another text in other summary lines. The final DataGrid is shown below. Figure 6. Final DataGrid summary Effective Web database applications can be achieved with specific doses and ASP.NET technology with specific doses. The DataGrid control is a frontier tool that can be used to build a perfect and powerful web application for programming features it provides, and more use for custom levels it supports. Dialogue: Confirmation of Key Tasks How can I display a dialog box that enforces a confirmation before starting a critical task (for example, record deletion)? Suppose you need to display this dialog after the user clicks a button. Ok, this only needs to be obtained through some client JavaScript code used to handle the OnClick event. Any ASP.NET control can be evaluated as one or more HTML tags. The lower pressure button is mapped to the "Button" tag. The link button is mapped to a hyperlink driven by the script. Use the ATTRIBUTES collection of the ASP.NET control, you can register the script code segment for these two tags. For example, if you have a button (whether it is a LinkButton object or Button object), you can define the JavaScript code that runs after the button is clicked, as shown below: String JS = "Return Confirm ('do you really want to delete the record?');" Btn.attributes ["onclick"] = js; In this way, the HTML tag contains an attribute: When the user clicks the link, the ONCLICK client code is run, and if the user clicks NO, the event will automatically give up. This behavior is embedded in the logic of the browser, it is almost independent of ASP.NET. If the OnClick client handler is successfully exited, the __dopostback function is executed, and the page will return to the server.
