/// <summary>用于取整个数据源的查询语法</summary> /// <param name="where">指主表的条件</param> /// <returns></returns> public string GetSQL(WhereObject where) { StringBuilder sql = new StringBuilder(); if (string.IsNullOrEmpty(this._id)) { return(string.Empty); } StringBuilder fields = null; LibFormPage form = ModelManager.GetFormSource(this._id); var datasourse = ModelManager.GetDataSource(form.DSID); if (datasourse != null) { foreach (LibDefineTable deftb in datasourse.DefTables) { if (deftb.TableStruct == null) { continue; } foreach (LibDataTableStruct tbstruct in deftb.TableStruct) { if (!tbstruct.Ignore) { continue; } fields = new StringBuilder(); char tbaliasnm = LibSysUtils.ToCharByTableIndex(tbstruct.TableIndex); foreach (LibField f in tbstruct.Fields) { if (fields.Length > 0) { fields.Append(SysConstManage.Comma); } fields.AppendFormat("{0}.{1}", tbaliasnm, string.IsNullOrEmpty(f.AliasName) ? f.Name : f.AliasName); } if (fields.Length > 0) { if (string.IsNullOrEmpty(where.WhereFormat)) { sql.AppendFormat("select {0} from {1} {2}", fields.ToString(), tbstruct.Name, tbaliasnm); } else { sql.AppendFormat("select {0} from {1} {2} where {3}", fields.ToString(), tbstruct.Name, tbaliasnm, where.WhereFormat); } sql.AppendLine(); } } } } return(sql.ToString()); }
public DataTable[] InternalFillData(List <string> whereformat, object[] valus) { StringBuilder sql = new StringBuilder(); DataTable[] dts = { }; StringBuilder where = new StringBuilder(); //DataTable mdt = null; //Dictionary<int, int> dic = new Dictionary<int, int>(); //SDPCRL.DAL.COM.SQLBuilder sQLBuilder = new SDPCRL.DAL.COM.SQLBuilder(); foreach (var libdt in this.LibTables) { foreach (LibTableObj dtobj in libdt.Tables) { TableExtendedProperties tbextprop = this.JsonToObj <TableExtendedProperties>(dtobj.DataTable.ExtendedProperties[SysConstManage.ExtProp].ToString()); if (!tbextprop.Ignore) { continue; } Array.Resize(ref dts, dts.Length + 1); dts[dts.Length - 1] = new DataTable(dtobj.TableName); if (tbextprop.TableIndex == 0 || tbextprop.TableIndex != tbextprop.RelateTableIndex) { where.Clear(); foreach (string item in whereformat) { if (where.Length > 0) { where.Append(" And "); } where.AppendFormat("{0}.{1}", LibSysUtils.ToCharByTableIndex(tbextprop.TableIndex), item); } //sql.Append(this.SQLBuilder.GetSQL(dtobj.TableName, null, new WhereObject { WhereFormat=where.ToString (),Values=valus },false,false)); sql.Append(this.DSContext.GetSQL(dtobj.TableName, null, new WhereObject { WhereFormat = where.ToString(), Values = valus }, false, false)); sql.AppendLine(); } } } this.DataAccess.GetDatatTables(sql.ToString(), ref dts); return(dts); }
private void DoGetSQL(StringBuilder builder, string tableNm, LibDataSource ds, WhereObject where, string[] sumaryfields, bool page = false, bool IsJoinRelateTable = true, bool IsJoinFromSourceField = true, bool IsRpt = false) { List <LibDataTableStruct> list = new List <LibDataTableStruct>(); StringBuilder joinstr = new StringBuilder(); StringBuilder joinfield = null; StringBuilder allfields = new StringBuilder(); Dictionary <string, string> tablealiasmdic = new Dictionary <string, string>(); foreach (LibDefineTable item in ds.DefTables) { if (item.TableStruct == null) { continue; } list.AddRange(item.TableStruct.ToArray()); } var tb = list.FirstOrDefault(i => i.Name == tableNm && i.Ignore); char tbaliasnm = LibSysUtils.ToCharByTableIndex(tb.TableIndex); #region 组织要查询表的字段 if (builder.Length == ResFactory.ResManager.SQLSelect.Length) { foreach (LibField f in tb.Fields) { if (!f.IsActive) { continue; } if (allfields.Length > 0) { allfields.Append(SysConstManage.Comma); } allfields.AppendFormat(" {0}.{1}", tbaliasnm, f.Name); } #region 日志列 if (allfields.Length > 0) { allfields.Append(SysConstManage.Comma); } allfields.AppendFormat(" {0}.{1}", tbaliasnm, SysConstManage.Sdp_LogId); #endregion } #endregion if (tb != null) { if (IsJoinRelateTable) { var relatetbs = list.Where(i => i.JoinTableIndex == tb.TableIndex && i.TableIndex != tb.TableIndex && i.Ignore).ToList(); List <int> tbindexs = null; //char tbaliasnm2 = tbaliasnm; while (relatetbs != null && relatetbs.Count > 0) { tbindexs = new List <int>(); foreach (var jointb in relatetbs) { tbindexs.Add(jointb.TableIndex); #region 组织joinstr语句 joinstr.AppendFormat(" {0} {1} {2} {3} ", ResFactory.ResManager.SQLLeftJoin, jointb.Name, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), //(char)(jointb.TableIndex+65), ResFactory.ResManager.SQLOn); joinfield = new StringBuilder(); foreach (var relatfield in jointb.JoinFields) { if (joinfield.Length > 0) { joinfield.Append(ResFactory.ResManager.SQLAnd);; } LibField libfd = jointb.Fields.FindFirst("Name", relatfield); //if (string.IsNullOrEmpty(libfd.RelatePrimarykey)) joinfield.AppendFormat(" {0}.{1}={2}.{3} ", LibSysUtils.ToCharByTableIndex(jointb.JoinTableIndex), string.IsNullOrEmpty(libfd.RelatePrimarykey) ? relatfield : libfd.RelatePrimarykey, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), relatfield); //else } joinstr.Append(joinfield.ToString()); joinstr.AppendLine(); #endregion #region 取关联表的字段 if (builder.Length == ResFactory.ResManager.SQLSelect.Length) { foreach (LibField f2 in jointb.Fields) { if (!f2.IsActive || jointb.JoinFields.Contains(f2.Name)) { continue; } if (allfields.ToString().ToUpper().Contains(string.Format(".{0}", f2.Name.ToUpper())) && string.IsNullOrEmpty(f2.AliasName)) { allfields.AppendFormat("{0}{1}.{2} as {3}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, string.Format("{0}{2}{1}", LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, SysConstManage.Underline)); } else { if (!string.IsNullOrEmpty(f2.AliasName)) { allfields.AppendFormat("{0}{1}.{2} as {3}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name, f2.AliasName); } else { allfields.AppendFormat("{0}{1}.{2}", SysConstManage.Comma, LibSysUtils.ToCharByTableIndex(jointb.TableIndex), f2.Name); } } } } #endregion } relatetbs = list.Where(i => tbindexs.Contains(i.JoinTableIndex) && i.TableIndex != i.JoinTableIndex && i.Ignore).ToList(); //tbaliasnm2= } } #region 字段上的来源表。 var relatefields = tb.Fields.ToArray().Where(i => i.SourceField != null); List <string> vals = new List <string>(); foreach (LibField f in relatefields) { foreach (LibFromSourceField fromfield in f.SourceField) { string key = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, fromfield.FromTableIndex); string tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(fromfield.FromTableIndex)); if (!tablealiasmdic.ContainsKey(key)) { //vals.Add(tbnm); int cout = vals.Where(i => i == tbnm).Count(); if (cout > 0) { tbnm = string.Format("{0}{1}", tbnm, cout); //tablealiasmdic.Add(key, string.Format("{0}", tbnm)); } vals.Add(tbnm); tablealiasmdic.Add(key, tbnm); //tablealiasmdic.Add(key,string.Format( tbnm); //tablealiasmdic[tbnm]++; //tbnm = string.Format("{0}{1}", tbnm, tablealiasmdic[tbnm]+1); } else { ////int cout = vals.Where(i => i == tbnm).Count(); tbnm = tablealiasmdic[key]; //tablealiasmdic.Add(tbnm, 0); } #region 组织joinstr语句 joinstr.AppendFormat(" {0} {1} {2} {3} ", ResFactory.ResManager.SQLLeftJoin, fromfield.FromStructTableNm, tbnm, //(char)(jointb.TableIndex+65), ResFactory.ResManager.SQLOn); joinfield = new StringBuilder(); if (joinfield.Length > 0) { joinfield.Append(ResFactory.ResManager.SQLAnd); } joinfield.AppendFormat(" {0}.{1}={2}.{3} ", tbaliasnm, f.Name, tbnm, fromfield.FromFieldNm); #region 处理JoinOnCondition if (!string.IsNullOrEmpty(fromfield.JoinOnCondition)) { if (where == null) { where = new WhereObject(); } DoJoinOnCondition(fromfield.JoinOnCondition, where); } #endregion joinstr.Append(joinfield.ToString()); joinstr.AppendLine(); if (IsJoinFromSourceField) { #region 取来源表所在数据源的 与来源表关联的表。 LibDataSource fromSouceDS = ModelManager.GetDataSource(fromfield.FromDataSource); if (fromSouceDS != null) { List <LibDataTableStruct> list2 = new List <LibDataTableStruct>(); foreach (LibDefineTable def in fromSouceDS.DefTables) { if (def.TableStruct == null) { continue; } list2.AddRange(def.TableStruct.ToArray()); } var relatetbs = list2.Where(i => i.JoinTableIndex == fromfield.FromTableIndex && i.TableIndex != fromfield.FromTableIndex && i.Ignore).ToList(); List <int> tbindexs2 = null; while (relatetbs != null && relatetbs.Count > 0) { tbindexs2 = new List <int>(); foreach (var jointb in relatetbs) { string key2 = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, jointb.TableIndex); tbindexs2.Add(jointb.TableIndex); string tbnm2 = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.TableIndex)); if (!tablealiasmdic.ContainsKey(key2)) { //vals.Add(tbnm2); int cout = vals.Where(i => i == tbnm2).Count(); if (cout > 0) { tbnm2 = string.Format("{0}{1}", tbnm2, cout); //tablealiasmdic.Add(key, string.Format("{0}", tbnm)); } tablealiasmdic.Add(key, tbnm2); //tablealiasmdic.Add(key2, tbnm2); //tablealiasmdic[tbnm2]++; //tbnm2 = string.Format("{0}{1}", tbnm2, tablealiasmdic[tbnm2]+1); } else { tbnm2 = tablealiasmdic[key2]; //tablealiasmdic.Add(tbnm2, 0); } joinstr.AppendFormat(" {0} {1} {2} {3} ", ResFactory.ResManager.SQLLeftJoin, jointb.Name, tbnm2, //(char)(jointb.TableIndex+65), ResFactory.ResManager.SQLOn); joinfield = new StringBuilder(); foreach (var relatfield in jointb.JoinFields) { if (joinfield.Length > 0) { joinfield.Append(ResFactory.ResManager.SQLAnd);; } LibField libfd = jointb.Fields.FindFirst("Name", relatfield); joinfield.AppendFormat(" {0}.{1}={2}.{3} ", string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.JoinTableIndex)), string.IsNullOrEmpty(libfd.RelatePrimarykey) ? relatfield : libfd.RelatePrimarykey, string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.TableIndex)), relatfield); } joinstr.Append(joinfield.ToString()); joinstr.AppendLine(); } relatetbs = list2.Where(i => tbindexs2.Contains(i.JoinTableIndex) && i.TableIndex != i.JoinTableIndex && i.Ignore).ToList(); //tbaliasnm2= } } #endregion } if (builder.Length == ResFactory.ResManager.SQLSelect.Length || IsRpt) { if (fromfield.RelateFieldNm != null) { foreach (LibRelateField relatef in fromfield.RelateFieldNm) { if (!IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex) { continue; } key = string.Format("{0}{1}{2}", f.Name, fromfield.FromDataSource, relatef.FromTableIndex); //tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(relatef.FromTableIndex)); //if (IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex) //{ tbnm = tablealiasmdic[key]; //} if (allfields.Length > 0) { allfields.Append(SysConstManage.Comma); } if (!string.IsNullOrEmpty(relatef.AliasName)) { allfields.AppendFormat("{0}.{1} as {2}", //SysConstManage.Comma, tbnm, relatef.FieldNm, relatef.AliasName); } else { allfields.AppendFormat("{0}.{1}", //SysConstManage.Comma, tbnm, relatef.FieldNm); } } } } #endregion } } #endregion if (builder.Length == ResFactory.ResManager.SQLSelect.Length || IsRpt) { if (builder.Length != ResFactory.ResManager.SQLSelect.Length) { builder.Append(SysConstManage.Comma); } builder.Append(allfields.ToString()); } if (page) //分页 { StringBuilder orderstr = new StringBuilder(); foreach (string key in tb.PrimaryKey) { if (orderstr.Length > 0) { orderstr.Append(SysConstManage.Comma); } orderstr.AppendFormat("{0}.{1}", LibSysUtils.ToCharByTableIndex(tb.TableIndex), key); } builder.AppendFormat(",ROW_NUMBER()OVER(order by {0}) as rownumber ", orderstr.ToString()); } //if (sumaryfields != null) //汇总字段 //{ // foreach (string field in sumaryfields) // { // builder.AppendFormat(",SUM({0})OVER() as {1}{2}", field,SysConstManage.sdp_summaryprefix,field .Substring(field.IndexOf(".")+1)); // } //} builder.AppendFormat(" {0}", ResFactory.ResManager.SQLFrom); builder.AppendFormat(" {0} {1}", tableNm, tbaliasnm); builder.Append(joinstr.ToString()); } }
//public #region 私有函数 private void GetColumns() { _cols = new Dictionary <string, LibFieldObj>(); List <string> vals = new List <string>(); ColExtendedProperties colExtended = null; LibFieldObj fobj = null; string ckey = string.Empty; TableExtendedProperties tableExtended = DataTable.ExtendedProperties[SysConstManage.ExtProp] as TableExtendedProperties; if (_tbaliasmdic == null) { _tbaliasmdic = new Dictionary <string, string>(); } foreach (DataColumn c in DataTable.Columns) { fobj = new LibFieldObj(); colExtended = c.ExtendedProperties[SysConstManage.ExtProp] as ColExtendedProperties; if (colExtended != null) { if (colExtended.IsRelate) { if (!string.IsNullOrEmpty(colExtended.AliasName)) { ckey = colExtended.AliasName; //fobj.FieldNm = colExtended.AliasName; } else if (!string.IsNullOrEmpty(colExtended.ObjectNm)) { ckey = colExtended.ObjectNm; //fobj.FieldNm = colExtended.ObjectNm; } else { ckey = c.ColumnName; //fobj.FieldNm = c.ColumnName; } string key = string.Format("{0}{1}{2}", colExtended.FromDSID, colExtended.FromTableIndex, colExtended.SourceFieldNm); if (!_tbaliasmdic.ContainsKey(key)) { string v = string.Format("{0}{1}", LibSysUtils.ToCharByTableIndex(tableExtended.TableIndex), LibSysUtils.ToCharByTableIndex(colExtended.FromTableIndex)); //int t = vals.Where(i => i == v).Count(); int cout = vals.Where(i => i == v).Count(); if (cout == 0) { _tbaliasmdic.Add(key, string.Format("{0}", v)); } else { _tbaliasmdic.Add(key, string.Format("{0}{1}", v, cout)); } vals.Add(v); } fobj.TBAliasNm = _tbaliasmdic[key]; fobj.FieldNm = colExtended.FieldNm; } else { if (tableExtended != null) { fobj.TBAliasNm = LibSysUtils.ToCharByTableIndex(tableExtended.TableIndex).ToString(); } ckey = string.IsNullOrEmpty(colExtended.ObjectNm) ? c.ColumnName : colExtended.ObjectNm; fobj.FieldNm = c.ColumnName; } fobj.colNm = c.ColumnName; _cols.Add(ckey, fobj); } else { fobj.FieldNm = c.ColumnName; fobj.colNm = c.ColumnName; _cols.Add(fobj.FieldNm, fobj); } } Columns = new ColumnObj(_cols); }