Beispiel #1
0
        private static SQLContext GetConditionedComprehensiveJoinedSelect_Inner(int methodId, string funcName, ref TableAndFields[] tblAndFldses, TableConnectionCollection tblCnnts, TableBiJoinedLogic tblBiJndLgc, bool andOrOr)
        {
            SQLContext ret = null;
            bool res1 = GetFromCache(methodId, ref ret);
            bool res2 = GetFromCacheTaF(methodId, ref tblAndFldses);
            if (res1 && res2)
                return ret;

            ret.Sentence = "SELECT {0} FROM {1} WHERE {2} ";
            //List<string> tables = new List<string>();
            List<string> fields = new List<string>();
            List<string> conds = new List<string>();
            //List<string> cnnts = new List<string>();

            int i = 0;
            int j = 1;
            foreach (TableAndFields tblAndFlds in tblAndFldses)
            {
                string alias = "t" + j.ToString();
                tblAndFlds.alias = alias;

                //if (tblAndFlds.subDBCalalog != null)
                //    tables.Add(tblAndFlds.subDBCalalog + ".." + tblAndFlds.Table.Name + " " + alias); //Sub Tables
                //else
                //    tables.Add(tblAndFlds.Table.Name + " " + alias);

                //object default_obj = Activator.CreateInstance(tblAndFlds.Table);
                object default_obj = CreateCtor(tblAndFlds.Table)();
                //FieldInfo[] fis = tblAndFlds.Table.GetFields(BindingFlags.Instance | BindingFlags.Public);
                IDictionary<string, DBFieldAttribute> dbAttrList;
                FieldInfo[] cfis;
                FieldInfo[] fis = getCacheFieldInfos(tblAndFlds.Table, BindingFlags.Instance | BindingFlags.Public, out dbAttrList, out cfis);
                foreach (FieldInfo fi in fis)
                {
                    //string fieldName = TransField(tblAndFlds.Table, fi.Name);
                    string fieldName = TransField(cfis, fi.Name);
                    //object[] objs = fi.GetCustomAttributes(typeof(DBFieldAttribute), false);
                    //DBFieldAttribute dbfa = (DBFieldAttribute)objs[0];
                    DBFieldAttribute dbfa = dbAttrList[fi.Name];

                    object default_val = fi.GetValue(default_obj);

                    #region . Conditions .

                    if (tblAndFlds.equalcond != null)
                    {
                        object equalval = fi.GetValue(tblAndFlds.equalcond);
                        if (equalval != null && !equalval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + "=@" + DecAlias(alias, ClearRectBrace(fieldName)));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, fieldName), sptr);
                        }
                    }

                    if (tblAndFlds.betweencond != null)
                    {
                        object betweenval = fi.GetValue(tblAndFlds.betweencond);
                        if (betweenval != null && !betweenval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + " BETWEEN @" + DecAlias(alias, DecBeg(ClearRectBrace(fieldName))) + " AND @" + DecAlias(alias, DecEnd(ClearRectBrace(fieldName))));
                            SqlParameter sptr_beg = new SqlParameter("@" + DecAlias(alias, DecBeg(ClearRectBrace(fieldName))), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, DecBeg(fieldName)), sptr_beg);
                            SqlParameter sptr_end = new SqlParameter("@" + DecAlias(alias, DecEnd(ClearRectBrace(fieldName))), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, DecEnd(fieldName)), sptr_end);
                        }
                    }

                    if (tblAndFlds.likecond != null)
                    {
                        object likeval = fi.GetValue(tblAndFlds.likecond);
                        if (likeval != null && !likeval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + " LIKE @" + DecAlias(alias, ClearRectBrace(fieldName)));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, fieldName), sptr);
                        }
                    }

                    if (tblAndFlds.greatercond != null)
                    {
                        object greaterval = fi.GetValue(tblAndFlds.greatercond);
                        if (greaterval != null && !greaterval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + ">@" + DecAlias(alias, DecG(ClearRectBrace(fieldName))));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecG(ClearRectBrace(fieldName))), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, DecG(fieldName)), sptr);
                        }
                    }

                    if (tblAndFlds.smallercond != null)
                    {
                        object smallerval = fi.GetValue(tblAndFlds.smallercond);
                        if (smallerval != null && !smallerval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + "<@" + DecAlias(alias, DecS(ClearRectBrace(fieldName))));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecS(ClearRectBrace(fieldName))), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, DecS(fieldName)), sptr);
                        }
                    }

                    if (tblAndFlds.greaterOrEqualcond != null)
                    {
                        object greaterOrEqualval = fi.GetValue(tblAndFlds.greaterOrEqualcond);
                        if (greaterOrEqualval != null && !greaterOrEqualval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + ">=@" + DecAlias(alias, DecGE(ClearRectBrace(fieldName))));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecGE(ClearRectBrace(fieldName))), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, DecGE(fieldName)), sptr);
                        }
                    }

                    if (tblAndFlds.smallerOrEqualcond != null)
                    {
                        object smallerOrEqualval = fi.GetValue(tblAndFlds.smallerOrEqualcond);
                        if (smallerOrEqualval != null && !smallerOrEqualval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + "<=@" + DecAlias(alias, DecSE(ClearRectBrace(fieldName))));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, DecSE(ClearRectBrace(fieldName))), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, DecSE(fieldName)), sptr);
                        }
                    }

                    if (tblAndFlds.inSetcond != null)
                    {
                        object inSetval = fi.GetValue(tblAndFlds.inSetcond);
                        if (inSetval != null && !inSetval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + " IN (" + DecAlias(alias, DecInSet(fieldName)) + ")");
                            //SqlParameter sptr = new SqlParameter("@" + DecSE(fieldName), dbfa.fieldType);
                            //ret.Params.Add(DecSE(fieldName), sptr);
                        }
                    }

                    if (tblAndFlds.notNullcond != null)
                    {
                        object notNullVal = fi.GetValue(tblAndFlds.notNullcond);
                        if (notNullVal != null && !notNullVal.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + " IS NOT NULL ");
                        }
                    }

                    if (tblAndFlds.nullcond != null)
                    {
                        object nullval = fi.GetValue(tblAndFlds.nullcond);
                        if (nullval != null && !nullval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + " IS NULL ");
                        }
                    }

                    if (tblAndFlds.notEqualcond != null)
                    {
                        object notEqualval = fi.GetValue(tblAndFlds.notEqualcond);
                        if (notEqualval != null && !notEqualval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + "!=@" + DecAlias(alias, ClearRectBrace(fieldName)));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, fieldName), sptr);
                        }
                    }

                    if (tblAndFlds.notLikecond != null)
                    {
                        object notLikeval = fi.GetValue(tblAndFlds.notLikecond);
                        if (notLikeval != null && !notLikeval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + " NOT LIKE @" + DecAlias(alias, ClearRectBrace(fieldName)));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, fieldName), sptr);
                        }
                    }

                    if (tblAndFlds.notInSetcond != null)
                    {
                        object notInSetval = fi.GetValue(tblAndFlds.notInSetcond);
                        if (notInSetval != null && !notInSetval.Equals(default_val))
                        {
                            conds.Add(DecAliasInner(alias, fieldName) + " NOT IN (" + DecAlias(alias, DecInSet(fieldName)) + ")");
                        }
                    }

                    if (tblAndFlds.nullOrEqual != null)
                    {
                        object nullOrEqVal = fi.GetValue(tblAndFlds.nullOrEqual);
                        if (nullOrEqVal != null && !nullOrEqVal.Equals(default_val))
                        {
                            conds.Add(string.Format("({0} IS NULL OR {0}={1})", DecAliasInner(alias, fieldName), "@" + DecAlias(alias, ClearRectBrace(fieldName))));
                            SqlParameter sptr = new SqlParameter("@" + DecAlias(alias, ClearRectBrace(fieldName)), dbfa.fieldType);
                            ret.Params.Add(DecAlias(alias, fieldName), sptr);
                        }
                    }

                    #endregion

                    //string key = DecAlias(tblAndFlds.Table.Name, fieldName);
                    string key = DecAlias(tblAndFlds.GetHashCode().ToString(), fieldName);
                    if (tblCnnts.Regist.ContainsKey(key))
                    {
                        IDictionary<string, TableConnectionItem> point = tblCnnts.Regist[key];
                        foreach (TableConnectionItem tcti in point.Values)
                        {
                            if (!tcti.tbl1_Alias && tblAndFlds == tcti.Tb1)
                            {
                                tcti.alias1 = alias;
                                tcti.tbl1_Alias = true;
                            }
                            if (!tcti.tbl2_Alias && tblAndFlds == tcti.Tb2)
                            {
                                tcti.alias2 = alias;
                                tcti.tbl2_Alias = true;
                            }
                        }
                    }

                    if (/*tblAndFlds.ToGetFieldNames == null ||*/ tblAndFlds.ToGetFieldNames != null && (tblAndFlds.ToGetFieldNames.Count < 1 || tblAndFlds.ToGetFieldNames.Contains(fieldName)))
                    {
                        ret.Indexes.Add(DecAlias(alias, fieldName), i);
                        fields.Add(DecAliasInner(alias, fieldName));
                        i++;
                    }
                }
                j++;
            }

            string fStr = string.Join(",", fields.ToArray());
            if (funcName != null)
            {
                if (funcName.ToUpper().Equals("DISTINCT") || funcName.ToUpper().StartsWith("TOP "))
                {
                    fStr = string.Format("{0} {1} ", funcName, fStr);
                }
                else
                {
                    fStr = string.Format("{0}({1})", funcName, fStr);
                    ret.Indexes.Clear();
                    ret.Indexes.Add(funcName, 0);
                }
            }

            object lastObj = null;
            string toStr = string.Empty;
            IEnumerator enmrt = tblBiJndLgc.Enum;
            while (enmrt.MoveNext())
            {
                object obj = enmrt.Current;
                if (obj is TableAndFields)
                {
                    toStr += obj.ToString();
                }
                else if (obj is TableConnectionItem)
                {
                    if (lastObj is TableConnectionItem)
                    {
                        if (((TableConnectionItem)obj).AndOrOr)
                            toStr += _andStr;
                        else
                            toStr += _orStr;
                    }
                    else if (lastObj is TableAndFields)
                    {
                        toStr += _onStr;
                    }

                    toStr += obj.ToString();
                }
                else if (obj is string)
                {
                    toStr += obj.ToString();
                }
                lastObj = obj;
            }
            string cStr = (conds.Count > 0 ? string.Join(andOrOr ? " AND " : " OR ", conds.ToArray()) : " 1=1 ");

            ret.Sentence = string.Format(ret.Sentence, fStr, toStr, cStr);
            return ret;
        }
Beispiel #2
0
 public static SQLContext GetConditionedComprehensiveJoinedSelectExtOr(int methodId, string funcName, ref TableAndFields[] tblAndFldses, TableConnectionCollection tblCnnts, TableBiJoinedLogic tblBiJndLgc)
 {
     return GetConditionedComprehensiveJoinedSelect_Inner(methodId, funcName, ref tblAndFldses, tblCnnts, tblBiJndLgc, false);
 }