Ext.net Gridpanelhelper

<pre name= "code" class= "CSharp" >///<summary>///gridpanelhelper

<span style= "font-family:arial, Helvetica, Sans-serif;" >///date:2014.7.22 by:wyl</span>

Gridpanel in our system is the child table information///</summary>public class gridpanelhelper{///<summary>//Gridpanel cell double-click Thing    Pieces//</summary> Public const string fn = "function (e) {E.cancel=true;return false;}"; #region Save Gridpanel//<summary>//Save Gridpanel Sub-table data///</summary>//<param name= "Jsondata" > JSON data for the sub-table store </param>//<param name= "store" > Child table Store (Ext.Net.Store) </param>//<param NA Me= "TableName" > Child table name </param>//<param name= "ParentID" > Parent table record id</param> public static int Savegr  Iddata (String jsondata, Ext.Net.Store Store, string tableName, int parentid) {//deserialization get Jarray array Jarray ja        = (Jarray) jsonconvert.deserializeobject (jsondata); if (ja.        Count <= 0) {return 0; } recordfieldcollection Recordfield = store. Reader[0].        fields; String ParentField = Recordfield[0]. Name;  ID of the parent table record string fields = parentfield;//Field List      String values = "";        Insert Value List string sqlstr = "";        String selsql = ""; for (int i = 0; i < ja. Count; i++)//Line loop {values = "'" + parentid.tostring () + "'"; The parent table ID takes the argument, not the for in json (int j = 1; j < Recordfield.count; J + +)//column loop {if (i = = 0)//execute the outermost loop only once {fields + = "," + recordfield[j].                Name; }//date:2014.7.22 By:wyl if (Ja[i][recordfield[j].                    Name] = = null) {string str = "";                Values + = "," + "'" + str + "'"; } else {values + = "," + "'" + ja[i][recordfield[j]. Name].                ToString () + "'"; }} Selsql + = "Select" + Values + "union ALL"; Construct the SELECT statement} sqlstr = string.         Format (@ "begin delete from {0:g} where {1:g}={2:g};                           Insert INTO {0:g} ({3:g}) {4:g};        End; ", TableName, ParentField, ParentID, Fields, selsql.substring (0, selsql.length-10));    Return Sunway.DBUtility.DbHelperSQL.ExecuteNonQuery (CommandType.Text, sqlstr, NULL); }///<summary>//Save Gridpanel Data///</summary>//<param name= "Jsondata" >store JSON data < /param>//<param name= "store" >gridpanel store</param>//<param name= "TableName" > Saved table name </ param> public static int Savegriddata (string jsondata, Store, string tableName) {//with Newtonsoft.json        Get jarray array Jarray ja = (jarray) jsonconvert.deserializeobject (jsondata); Recordfieldcollection Recordfield = store. Reader[0].        fields; String fields = Recordfield[0]. name;//Field List String values = "";        Insert Value List string sqlstr = "";        String selsql = "";        String sqlStr1 = "";        String[] Sqlarray; for (int i = 0; I < ja. Count; i++) {values = "'" + ja[i][recordfield[0]. Name].            ToString () + "'";                    for (int j = 1; j < Recordfield.count; J + +) {if (i = = 0) { Fields + = "," + recordfield[j].                Name; } values + = "," + "'" + ja[i][recordfield[j]. Name].            ToString () + "'";        } Selsql + = "Select" + Values + "union ALL"; } sqlstr = string.        Format ("Delete from {0:g}", TableName); if (Selsql! = "")//grid no record (may have been completely deleted, or not filled) {sqlStr1 = string.        Format (@ "insert INTO {0:g} ({1:g}) {2:g}", TableName, Fields, selsql.substring (0, selsql.length-10)); } if (Selsql = = "") {return Sunway.DBUtility.DbHelperSQL.ExecuteNonQuery (CommandType.Text, Sqlst        R, NULL);         } else {Sqlarray = new string[] {selsql, selsql};        Return Tranhelp.exectran (Sqlarray); }    }    /// &Lt;summary>///Save Gridpanel Sub-table (with trigger)///</summary>//<param name= "Jsondata" > Sub-table store JSON data </p aram>//<param name= "Store" > Sub-table Store (Ext.Net.Store) </param>//<param name= "tableName" > child table name &L t;/param>//<param name= "ParentID" > Parent table Records id</param>//<param name= "Istrigger" > with trigger </par am> public static int Savegriddata (string jsondata, Ext.Net.Store Store, string tableName, int parentid, BOOL Istrig        GER) {int num = 0; if (Istrigger = = true) {//deserialization gets jarray array Jarray ja = (jarray) jsonconvert.deserializeobject (j            Sondata); Recordfieldcollection Recordfield = store. Reader[0].            fields; String ParentField = Recordfield[0]. Name; ID of the parent table record string fields = parentfield;//Field List String values = "";            Insert Value List string sqlstr = "";            String inssql = ""; for (int i = 0; i < ja. Count;        i++)//Line loop    {values = "'" + parentid.tostring () + "'";                    for (int j = 1, J < Recordfield.count; J + +)//column loop {if (i = = 0)//Only one outermost loop is executed {fields + = "," + recordfield[j].                    Name; } values + = "," + "'" + ja[i][recordfield[j]. Name].                ToString () + "'"; } Inssql + = string. Format ("INSERT INTO {0:g} ({1:g}) ' Values ({2:g});", tableName, fields, values); Constructs an INSERT statement} SQLSTR = string.                                    Format (@ "begin delete from {0:g} where {1:g}={2:g};            {3:g} end; ", TableName, ParentField, ParentID, Inssql);            try {num = Sunway.DBUtility.DbHelperSQL.ExecuteNonQuery (commandtype.text, sqlstr, NULL);            } catch (Exception) {num =-1; }        }       return num; }///<summary>//Save grid Line///</summary>//<param name= "Jsondata" ></param>// <param name= "Store" ></param>//<param name= "TableName" ></param>//<param name= "Parent ID "></param>///<returns></returns> public static int Savegridrow (string jsondata, Ext.Net.Sto Re store, String tableName, String parentid) {//Deserialize get Jarray array jarray ja = (jarray) Jsonconvert.deseriali        Zeobject (Jsondata); Recordfieldcollection Recordfield = store. Reader[0].        fields; String ParentField = Recordfield[0]. Name; ID of the parent table record string fields = "";//Field List String values = "";        Insert Value List string sqlstr = "";        String inssql = ""; for (int j = 1; j < Recordfield.count; J + +)//Column loop {fields + = Recordfield[j].            Name+ ","; Values + = "'" +ja[0][recordfield[j]. Name].        ToString () + "',"; } Inssql + = string. ForMat ("INSERT INTO {0:g} ({1:g}) ' Values ({2:g});", TableName, fields. Substring (0,fields. Length-1), values. Substring (0,values. LENGTH-1)); Constructs an INSERT statement if (ParentID! = "") {sqlstr = string. Format (@ "begin delete from {0:g} where {1:g}={2:g};{                    3:G} end; ", TableName, ParentField, ParentID, Inssql);        } else {sqlstr = Inssql;    } return Sunway.DBUtility.DbHelperSQL.ExecuteNonQuery (CommandType.Text, sqlstr, NULL); } #endregion #region Import Excel///<summary>//import Excel to Gridpanel, apply COM component////</summary>/ /<param name= "Filenameurl" > Physical path </param>//<param name= "Sheetindex" >sheet name index </param>/ <returns></returns> public static void Impexecletogrid (String filenameurl, Gridpanel grd) {i NT Sheetindex = index of 1;//sheet name Microsoft.Office.Interop.Excel.WorkbooK WB = NULL;        Microsoft.Office.Interop.Excel.Worksheet ws = NULL;        ArrayList Columnarr = new ArrayList ();//column Field table DataSet MyDs = new DataSet ();        DataTable xlstable = MyDs.Tables.Add ("show");        Object missing = System.Reflection.Missing.Value;        Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application (); if (Excel! = null) {Excel.            Visible = false; Excel.            UserControl = true; Open the Excel file in read-only format WB = Excel. Workbooks.Open (Filenameurl, Missing, true, missing, missing, missing, missing, missing, missing, true, missing            , missing, missing, missing, missing); Get the first workbook of the WS = (Microsoft.Office.Interop.Excel.Worksheet) WB.            Worksheets.get_item (Sheetindex); Gets the total number of record rows (including the title column) int rowsint = ws. UsedRange.Cells.Rows.Count; Gets the number of rows int columnsint = ws. usedrange.cells.columns.count;//Get column number DataRow Dr;            String colname = ""; for (int i = 1; i < Columnsint; i++) {colname = ((Microsoft.Office.Interop.Excel.Range) ws. Cells[1, I]).                Text.tostring (); if (GRD. Columnmodel.columns[i]. Header = = colname) {xlsTable.Columns.Add (GRD. Columnmodel.columns[i].                    Dataindex, typeof (String)); Columnarr.add (GRD. Columnmodel.columns[i].                DATAINDEX);                }} for (int i = 2; I <= rowsint; i++) {Dr = Xlstable.newrow (); for (int j = 1; j < Columnsint; J + +) {Dr[columnarr[j-1]. ToString ()] = ((Microsoft.Office.Interop.Excel.Range) ws. Cells[i, J]).                Text.tostring ();            } xlsTable.Rows.Add (DR); }} Excel.        Quit ();        Excel = null;        Dispose (WS, WB); Gc.        Collect (); Grd. Store[0].        DataSource = xlstable; Grd. STORE[0].    DataBind (); } #endregion//<summary>///Set column can be modified///</summary>//<param name= "cm" >columnmodel&l t;/param>//<param name= "iseditable" ></param> public static void setcolumnseditable (Columnmodel cm, BOOL iseditable) {for (int i = 0; i < cm. Columns.count; i++) {cm.        Seteditable (i, iseditable); }} public static void Savestore (Store st, string ID) {}}

Ext.net Gridpanelhelper