How to process large amounts of data in the construction Material management Web system and display

Recently in the development of construction material management system, which involves a large number of material information needs to be managed and aggregated, the amount of data is very large. Before trying to make the original data, processing modeling, in the background code by grouping, transpose and then display to the Web page, but the amount of code you write very large, and poor performance simply unbearable. Later, the use of the moment table control is very good to solve the requirements, this article mainly describes how to show the data in the page by code, and the use of the moment table control to create row and column transpose and dynamic columns table, and displayed in the Web page.

The steps of displaying data in a page by code

1. The row and column transpose code snippet:

 Public StaticDataTable getcrosstable (DataTable dt) {if(dt = =NULL|| Dt. Columns.count! = 3 | | Dt. Rows.Count = = 0) {returnDt }Else{DataTable result =NewDataTable (); Result. Columns.Add (dt. Columns[0].   ColumnName); DataTable dtcolumns = dt. Defaultview.totable ("Dtcolumns",trueDt. COLUMNS[1]. ColumnName); for(inti = 0; i < DtColumns.Rows.Count; i++) {stringColName;if(Dtcolumns.rows[1][0] isDateTime) {colname = Convert.todatetime (Dtcolumns.rows[i][0]).    ToString (); }Else{colname = dtcolumns.rows[i][0].    ToString (); } result.    Columns.Add (colname); Result. Columns[i + 1]. DefaultValue ="0"; } DataRow drnew = result.   NewRow (); DRNEW[0] = dt. ROWS[0][0];stringRowName = drnew[0]. ToString ();foreach(DataRow DrinchDt. Rows) {stringColName = dr[1]. ToString ();DoubleDvalue = convert.todouble (dr[2]);if(Dr[0]. ToString ().    Equals (RowName, stringcomparison.currentcultureignorecase)) {Drnew[colname] = dvalue.tostring (); }Else{result.     Rows.Add (drnew); Drnew = result.     NewRow ();     Drnew[0] = dr[0]; RowName = drnew[0].     ToString ();    Drnew[colname] = dvalue.tostring (); }} result. Rows.Add (drnew);returnResult }}

2. Implement the multi-level grouping Logic sample code:

public  void  Groupbyclassleverl () {var        query = from Inforcode in  classreportdetail Group Infocode by Bigclassname into NewGroup    Newgroup.key Select NewGroup; foreach     (Var bigclassname in  classreportdetail)        {Console.WriteLine (, Namegroup.key); foreach         (Var ClassName in  namegroup) {Console.WriteLine ( "\t{0}, {1}" , student. Bigclassname, student.            ClassName);    ............ } }}

3. Implementation of the SQL statement to achieve a summary of the classification function, 7 tables of complex connections and summaries:

Each table contains multiple columns, requires multiple layers of joins and sorts, and filters the data based on user input

Select A.*,b.supplymode,h.classname,g.classname Bigclassname,e.receivedepname,f.w_receivedepname,isnull ( b.requantity,0) Requantity,isnull (b.rebooksum,0) rebooksum,isnull (d.mquantity,0) mquantity,isnull (d.mBookSum,0) ..., IsNull (f.w_allquantity,0) w_allquantity,isnull (f.w_allallotsum,0) w_allallotsum,isnull (f.w_allotbooksum,0) W_ Allotbooksumfrom (select A.infocode,a.infoname,a.infomodel,a.infounit,a.classnodebh,a.rate,a.nonrateprice,a. Bookprice,a.manufacturer,a.batchno,a.barcode,a.storeroom,a.inforemark from (select B.infocode,b.infoname,b.infomo Del,b.infounit,b.classnodebh,b.rate,b.nonrateprice,b.bookprice,b.manufacturer,b.batchno,b.barcode,b.storeroom, B.inforemark from dbo. M_monthstore bwhereLeft (B.projectid,len (@projectid)) [email protected] and B.month>[email protected] and B.month<[email protected] and b.monthbalancenum!=0 GROUP by B.infocode,b.infoname,b.infomodel,b.infounit,b.classnodebh,b.rate,b.nonrateprice , B.bookprice,b.manufacturer,b.batchno,b.barcode,b.storeroom,b.inforemark UNION ALL Select Infocode,i Nfoname,infomodel,infounit,classnodebh,rate,nonrateprice,bookprice,manufacturer,batchno,barcode,b.storeroom, Inforemark from dbo. M_receiveorder a INNER JOIN dbo. M_receiveorderitem b on A.projectid=b.projectid and A.orderid=b.orderidwhere Left (A.projectid,len (@projectid)) [email protected] and a.orderdate>[email protected] and a.orderdate <[email protected] and A.isaudit=1 UNION ALL ...) B on A.infocode=b.infocode and A.infoname=b.infoname and A.infomodel=b.infomodel and A.infouni T=b.infounit and A.classnodebh=b.classnodebh and a.storeroom=b.storeroom .... ORDER BY Supplymode,receiv Edepname,w_receivedepname desc

Finally, the data can only be rendered single, and the style is too simple, nearly tens of thousands of data rendering performance unbearable, often cause the page to crash.

Using the table control provided by the report to achieve row and column transpose, there is no need to write so complex rows and columns to transpose and block code, but also based on the supply of the material to automatically generate rows, the data presented in the final page.

Second, the use of the moment Table control implementation steps:

1. Add an RDL report

2. Adding a data source to a report

3. Add data sets

In the DataSet window, enter the SQL statement:

SELECT * from Classreportdatatable.

Get field

4. Adding a moment Table control

5. Build the report structure

5.1 add row groupings first by category one and level two

Select rows grouped cells, add row grouping-"sub-groups"

5.2 Add supply mode dynamic column

Right-click a column grouping cell, select Insert Column grouping, and insert three column groupings according to the design style.

5.3 insert static columns because they do not change dynamically with the data, so it is a static column, just right-click-Insert Column

Here, the basic structure of the data is formed, and the next thing to do is to bind the business data to the moment table control.

5.4 Data Binding

Think of the original need to write a variety of row and column transpose code, generate group code, the head hurts, now use the moment table control, directly drag data fields to the corresponding cell, you can dynamically generate rows and columns. Adding "Supplymode" to the column grouping cell automatically generates the number of columns based on the value of the Supplymode; the rows automatically merge the same cells according to the first and class two categories and automatically generate row data based on the content; After dragging the field to the cell, merge the cells and adjust the style.

No more pain writing front-end display and ultra-complex performance optimization code, the page can not be brushed for half an hour, the user is really unable to endure; the existing use of more mature report control, and did drag and drop to solve the problem.

How to process large amounts of data in the construction Material management Web system and display