private List <TreeNode> drillMembersComm(List <string> memberPath) { if (levels == null || levels.Count < 1) { throw new NoDefineLevelException(); } StringBuilder sbSql = new StringBuilder(); int memberPathCount; StringBuilder sbWhere = getDrillWhere(memberPath, out memberPathCount); if (memberPathCount > levels.Count - 1) { throw new OlapLevelOverException(); } string drillField = levels[memberPathCount].column; sbSql.Append(" Select "); sbSql.Append(drillField); sbSql.Append(" From "); sbSql.Append(table.name); if (sbWhere.Length > 0) { sbSql.Append(" Where "); sbSql.Append(sbWhere); } sbSql.Append(" Group By "); sbSql.Append(drillField); DatabaseAdmin dba = DatabaseAdmin.getInstance(_table.connection); DataTable tb = dba.executeTable(sbSql.ToString()); List <TreeNode> nodes = new List <TreeNode>(); foreach (DataRow row in tb.Rows) { TreeNode node = new TreeNode(); node.text = row[drillField].ToString(); node.attr.Add("memberField", drillField); if (memberPathCount + 1 < levels.Count) { List <string> subMembers = new List <string>(); if (memberPath != null && memberPath.Count > 0) { subMembers = new List <string>(memberPath); } subMembers.Add(node.text); node.children = drillMembers(subMembers); } nodes.Add(node); } return(nodes); }
private List <ListDataRow> getSubTableRows(Dictionary <string, string> pks, SubTableSchema subSchema) { DatabaseAdmin dba = DatabaseAdmin.getInstance(); DataSource subDs = new DataSource(subSchema.Name); DataSourceSchema dss = subDs.getSchema(); if (dss.SelectCommand.CommandType != CommandType.TableDirect) { throw new XException(string.Format(Lang.SubTableSelCommandTypeOnlyIsTable, subSchema.Name)); } StringBuilder sb = new StringBuilder("select * from "); sb.Append(dss.SelectCommand.CommandText); sb.Append(" "); sb.Append(" where "); Hashtable ps = new Hashtable(); for (int i = 0; i < subSchema.Fks.Count; i++) { string fk = subSchema.Fks[i]; string pk = _schema.PrimaryKeys[i]; sb.Append(fk); sb.Append("=@"); sb.Append(pk); sb.Append(" and "); ps.Add("@" + pk, pks[pk].ToString()); } sb.Remove(sb.Length - 5, 5); DbCommand cmd = dba.getSqlStringCommand(sb.ToString()); foreach (string key in ps.Keys) { dba.addInParameter(cmd, key, DbType.String, ps[key]); } DataTable tb = dba.executeTable(cmd); List <ListDataRow> rows = new List <ListDataRow>(); foreach (DataRow row in tb.Rows) { rows.Add(DataSourceComm.readRow(tb, dss, row)); } return(rows); }
public Dictionary <string, PermissionObject> getRolePermissionDict(string roleId, string objType) { DatabaseAdmin dba = SecuritySettings.getDBA(); DbCommand cmd = dba.getSqlStringCommand(SecurityDataScripts.GetPermissionObjectsSql); dba.addInParameter(cmd, "@roleId", DbType.String, roleId); dba.addInParameter(cmd, "@objType", DbType.String, objType); DataTable tb = dba.executeTable(cmd); Dictionary <string, PermissionObject> ret = new Dictionary <string, PermissionObject>(); foreach (DataRow r in tb.Rows) { int p = 0; PermissionObject permObj = new PermissionObject(); permObj.ObjectId = r["object_id"].ToString(); permObj.ObjectType = r["object_type"].ToString(); int.TryParse(r["permission"].ToString(), out p); permObj.Permission = (PermissionTypes)p; ret.Add(permObj.ObjectId, permObj); } return(ret); }
public ListData drillTable(string table, string fk, List <string> parentMembers) { if (string.IsNullOrEmpty(table)) { table = this.table.name; } if (string.IsNullOrEmpty(fk)) { fk = this.primaryKey; } EasyUiGridData grid = new EasyUiGridData(); StringBuilder sql = new StringBuilder(); //纬度表 sql.Append("Select "); sql.Append(this.primaryKey); sql.Append(" Into #t "); sql.Append(" From "); sql.Append(this.table.name); int memberPathCount; StringBuilder sbWhere = getDrillWhere(parentMembers, out memberPathCount); if (sbWhere.Length > 0) { sql.Append(" Where "); sql.Append(sbWhere); } sql.Append(" Group By "); sql.Append(primaryKey); sql.Append(";"); //事实表 sql.Append(" Select "); sql.Append(table); sql.Append(".* From "); sql.Append(table); sql.Append(","); sql.Append("#t"); sql.Append(" Where "); sql.Append(table); sql.Append("."); sql.Append(fk); sql.Append("=#t."); sql.Append(primaryKey); ListData ret = new ListData(); DatabaseAdmin dba = DatabaseAdmin.getInstance(this.table.connection); DataTable tb = dba.executeTable(sql.ToString()); ret.total = tb.Rows.Count; ret.rows = new List <ListDataRow>(); foreach (DataRow dRow in tb.Rows) { ListDataRow row = new ListDataRow(); foreach (DataColumn col in tb.Columns) { row.Add(col.ColumnName, dRow[col].ToString()); } ret.rows.Add(row); } return(ret); }