예제 #1
0
        /// <summary>用于取整个数据源的查询语法</summary>
        /// <param name="where">指主表的条件</param>
        /// <returns></returns>
        //public string GetSQL(WhereObject where)
        //{
        //    StringBuilder sql = new StringBuilder();
        //    if (this.DataSource==null) return string.Empty;
        //    StringBuilder fields = null;

        //    //LibFormPage form =SDPCRL.COM.ModelManager.ModelManager.GetFormSource(this._id);
        //    //var datasourse = SDPCRL.COM.ModelManager.ModelManager.GetDataSource(form.DSID);
        //    if (this.DataSource != null)
        //    {
        //        foreach (LibDefineTable deftb in this.DataSource.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 WhereObject Where(string format, params object[] values)
        {
            WhereObject wobj = new WhereObject();

            wobj.WhereFormat = format;
            wobj.Values      = values;
            return(wobj);
        }
예제 #2
0
 public LibTableObj Where(string format, params object[] values)
 {
     if (this._whereObject == null)
     {
         this._whereObject = new WhereObject();
     }
     this._whereObject.WhereFormat = format;
     this._whereObject.Values      = values;
     return(this);
 }
예제 #3
0
        /// <summary>
        /// 取分页查询的sql语法
        /// </summary>
        /// <param name="tableNm"></param>
        /// <param name="fields"></param>
        /// <param name="where"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="IsJoinRelateTable"></param>
        /// <param name="IsJoinFromSourceField"></param>
        /// <returns></returns>
        public string GetSQLByPage(string tableNm, string[] fields, WhereObject where, int pageindex, int pagesize, bool IsJoinRelateTable = true, bool IsJoinFromSourceField = true)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append(ResFactory.ResManager.SQLSelect);
            if (fields != null)
            {
                foreach (string field in fields)
                {
                    if (builder.Length != ResFactory.ResManager.SQLSelect.Length)
                    {
                        builder.Append(SysConstManage.Comma);
                    }
                    builder.AppendFormat(" {0}", field);
                }
            }
            if (this.DataSource == null)
            {
                if (builder.Length == ResFactory.ResManager.SQLSelect.Length)
                {
                    builder.AppendFormat(" {0}", SysConstManage.Asterisk);
                }

                //builder.AppendFormat("{0}ROW_NUMBER()OVER(order by A.BillNo) as rownumber");
                builder.AppendFormat(" {0}", ResFactory.ResManager.SQLFrom);
                builder.AppendFormat(" {0}", tableNm);
            }
            else
            {
                //LibDataSource ds = null;
                //if (this._mark)
                //{
                //    ds = ModelManager.GetDataSource(this._id);
                //}
                //else
                //{
                //    LibFormPage form = ModelManager.GetFormSource(this._id);
                //    ds = ModelManager.GetDataSource(form.DSID);
                //}
                //if (ds != null)
                //{
                DoGetSQL(builder, tableNm, this.DataSource, where, true, IsJoinRelateTable, IsJoinFromSourceField);
                //}
            }
            if (!string.IsNullOrEmpty(where.WhereFormat))
            {
                return(string.Format("EXEC sp_executesql N'select *from({0} where {1}) as temp where rownumber>={3} and rownumber<={4}',{2}", builder.ToString(), where.WhereFormat, where.ValueTostring, (pageindex - 1) * pagesize + 1, pageindex * pagesize));
            }
            return(string.Format("EXEC sp_executesql N'select *from({0}) as temp where rownumber>={1} and rownumber<={2}'", builder.ToString(), (pageindex - 1) * pagesize + 1, pageindex * pagesize));
        }
예제 #4
0
        //public LibTableObj GetTableObj(string tableNm)
        //{
        //    LibTableObj tableObj = null;
        //    foreach (LibDefineTable deftb in _ds.DefTables)
        //    {
        //        if (deftb.TableStruct == null) continue;
        //        foreach (LibDataTableStruct tb in deftb.TableStruct)
        //        {
        //            if (tb.Name.ToUpper() != tableNm.ToUpper()) continue;
        //            tableObj = new LibTableObj(new CreateTableSchemaHelp().DoCreateTableShema(tb));
        //            break;
        //        }
        //    }
        //    return tableObj;
        //}

        /// <summary>
        /// 取查询的sql语法
        /// </summary>
        /// <param name="tableNm"></param>
        /// <param name="fields"></param>
        /// <param name="where"></param>
        /// <param name="IsJoinRelateTable"></param>
        /// <param name="IsJoinFromSourceField"></param>
        /// <returns></returns>
        public string GetSQL(string tableNm, string[] fields, WhereObject where, bool IsJoinRelateTable = true, bool IsJoinFromSourceField = true)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append(ResFactory.ResManager.SQLSelect);
            if (fields != null)
            {
                foreach (string field in fields)
                {
                    if (builder.Length != ResFactory.ResManager.SQLSelect.Length)
                    {
                        builder.Append(SysConstManage.Comma);
                    }
                    builder.AppendFormat(" {0}", field);
                }
            }
            if (this.DataSource == null)
            {
                if (builder.Length == ResFactory.ResManager.SQLSelect.Length)
                {
                    builder.AppendFormat(" {0}", SysConstManage.Asterisk);
                }
                builder.AppendFormat(" {0}", ResFactory.ResManager.SQLFrom);
                builder.AppendFormat(" {0}", tableNm);
            }
            else
            {
                //LibDataSource ds = null;
                //if (this._mark)
                //{
                //    ds = ModelManager.GetDataSource(this._id);
                //}
                //else
                //{
                //    LibFormPage form = ModelManager.GetFormSource(this._id);
                //    ds = ModelManager.GetDataSource(form.DSID);
                //}
                //if (this._ds != null)
                //{
                DoGetSQL(builder, tableNm, this.DataSource, where, false, IsJoinRelateTable, IsJoinFromSourceField);
                //}
            }
            if (!string.IsNullOrEmpty(where.WhereFormat))
            {
                return(string.Format("EXEC sp_executesql N'{0} where {1}',{2}", builder.ToString(), where.WhereFormat, where.ValueTostring));
            }
            return(string.Format("EXEC sp_executesql N'{0}'", builder.ToString()));
        }
예제 #5
0
        private void DoJoinOnCondition(string condition, WhereObject where)
        {
            string[]      array      = condition.ToUpper().Split('=');
            string[]      separator  = { "(", ")", "AND", "OR" };
            string        itemstr    = string.Empty;
            StringBuilder condformat = new StringBuilder();

            object[] valus = { };
            //string pattern = "^[0-9]*$";
            //Regex regex = new Regex(pattern);
            if (array.Length > 0)
            {
                condformat.Append(array[0]);
                int   index = -1;
                int   n     = 0;
                Int32 val   = -1;
                for (int i = 1; i < array.Length; i++)
                {
                    foreach (string sep in separator)
                    {
                        index = array[i].IndexOf(sep);
                        if (index == -1)
                        {
                            continue;
                        }
                        break;
                    }
                    val = -1;
                    if (index != -1)
                    {
                        itemstr = array[i].Substring(0, index).Trim();
                        if (itemstr.Contains("'") || LibSysUtils.IsNumberic(itemstr, out val))
                        {
                            condformat.AppendFormat("={0} ", array[i].Replace(itemstr, "{" + n + "} "));
                            n++;
                            Array.Resize(ref valus, valus.Length + 1);
                            if (val == -1)
                            {
                                valus[valus.Length - 1] = itemstr.Trim();
                            }
                            else
                            {
                                valus[valus.Length - 1] = val;
                            }
                            //valus[valus.Length - 1] =(val == -1 ? itemstr.Trim() : val);
                        }
                        else
                        {
                            condformat.AppendFormat("={0} ", array[i]);
                        }
                    }
                    else
                    {
                        if (array[i].Contains("'") || LibSysUtils.IsNumberic(array[i], out val))
                        {
                            condformat.AppendFormat("={0} ", array[i].Replace(array[i], "{" + n + "} "));
                            n++;
                            Array.Resize(ref valus, valus.Length + 1);
                            if (val == -1)
                            {
                                valus[valus.Length - 1] = val;
                            }
                            else
                            {
                                valus[valus.Length - 1] = array[i].Replace(SysConstManage.SingleQuotes, ' ').Trim();
                            }
                        }
                        else
                        {
                            condformat.AppendFormat("={0}", array[i]);
                        }
                    }
                }
                where.AppendWhereFormat(ResFactory.ResManager.SQLAnd, string.Format("({0})", condformat), valus);
            }
        }
예제 #6
0
        private void DoGetSQL(StringBuilder builder, string tableNm, LibDataSource ds, WhereObject where, bool page = false, bool IsJoinRelateTable = true, bool IsJoinFromSourceField = true)
        {
            List <LibDataTableStruct> list      = new List <LibDataTableStruct>();
            StringBuilder             joinstr   = new StringBuilder();
            StringBuilder             joinfield = null;
            StringBuilder             allfields = new StringBuilder();

            //Dictionary<string, int> tablealiasmdic = new Dictionary<string, int>();
            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);
                foreach (LibField f in relatefields)
                {
                    foreach (LibFromSourceField fromfield in f.SourceField)
                    {
                        string tbnm = this[tableNm].GetTBAliasmn(fromfield.FromDataSource, fromfield.FromTableIndex, f.Name);
                        //string tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(fromfield.FromTableIndex));
                        //if (tablealiasmdic.ContainsKey(tbnm))
                        //{
                        //    tbnm = string.Format("{0}{1}", tbnm, tablealiasmdic[tbnm]++);
                        //}
                        //else
                        //{
                        //    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))
                        {
                            DoJoinOnCondition(fromfield.JoinOnCondition, where);
                        }
                        #endregion
                        joinstr.Append(joinfield.ToString());
                        joinstr.AppendLine();
                        if (IsJoinFromSourceField)
                        {
                            #region 取来源表所在数据源的 与来源表关联的表。
                            LibDataSource fromSouceDS = SDPCRL.COM.ModelManager.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)
                                    {
                                        tbindexs2.Add(jointb.TableIndex);
                                        string tbnm2 = this[tableNm].GetTBAliasmn(fromfield.FromDataSource, jointb.TableIndex, f.Name);
                                        //string tbnm2 = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(jointb.TableIndex));
                                        //if (tablealiasmdic.ContainsKey(tbnm2))
                                        //{
                                        //    tbnm2 = string.Format("{0}{1}", tbnm2, tablealiasmdic[tbnm2]++);
                                        //}
                                        //else
                                        //{
                                        //    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)
                        {
                            if (fromfield.RelateFieldNm != null)
                            {
                                foreach (LibRelateField relatef in fromfield.RelateFieldNm)
                                {
                                    if (!IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex)
                                    {
                                        continue;
                                    }
                                    tbnm = this[tableNm].GetTBAliasmn(fromfield.FromDataSource, relatef.FromTableIndex, f.Name);
                                    //tbnm = string.Format("{0}{1}", tbaliasnm, LibSysUtils.ToCharByTableIndex(relatef.FromTableIndex));
                                    //if (IsJoinFromSourceField && relatef.FromTableIndex != fromfield.FromTableIndex)
                                    //{
                                    //    tbnm = tablealiasmdic[tbnm] == 0 ? tbnm : string.Format("{0}{1}", tbnm, tablealiasmdic[tbnm]);
                                    //}
                                    if (!string.IsNullOrEmpty(relatef.AliasName))
                                    {
                                        allfields.AppendFormat("{0}{1}.{2} as {3}",
                                                               SysConstManage.Comma,
                                                               tbnm,
                                                               relatef.FieldNm,
                                                               relatef.AliasName);
                                    }
                                    else
                                    {
                                        allfields.AppendFormat("{0}{1}.{2}",
                                                               SysConstManage.Comma,
                                                               tbnm,
                                                               relatef.FieldNm);
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                }
                #endregion
                if (builder.Length == ResFactory.ResManager.SQLSelect.Length)
                {
                    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 ,COUNT(1)OVER() as {1} ", orderstr.ToString(), SysConstManage.sdp_total_row);
                }
                builder.AppendFormat(" {0}", ResFactory.ResManager.SQLFrom);
                builder.AppendFormat(" {0} {1}", tableNm, tbaliasnm);
                builder.Append(joinstr.ToString());
            }
        }