public SqlCommandString GenerateSqlCommandString(string sqlBody, string sqlWherePlaceHolderInBody) { SqlCommandString r = new SqlCommandString(); // SSC.DataStructure.Pair <string, List <string> > r_Internal = GenerateSqlWhere_Internal(1, 1); // r.Params = r_Internal.Data2; // r.CommandString = sqlBody + "\n"; // if (!string.IsNullOrEmpty(r_Internal.Data1)) { if (string.IsNullOrEmpty(sqlWherePlaceHolderInBody)) { r.CommandString += string.Format("WHERE\n{0}", r_Internal.Data1); } else { r.CommandString = r.CommandString.Replace(sqlWherePlaceHolderInBody, string.Format("WHERE\n{0}", r_Internal.Data1)); } } else if (!string.IsNullOrEmpty(sqlWherePlaceHolderInBody)) { r.CommandString = r.CommandString.Replace(sqlWherePlaceHolderInBody, ""); } // return(r); }
internal SSC.DataStructure.Pair <string, List <string> > GenerateSqlWhere_Internal(int level, int iLevelIndex) { SSC.DataStructure.Pair <string, List <string> > r = new DataStructure.Pair <string, List <string> >(); List <string> declareLines = new List <string>(); string sql = ""; int iCondition = 1; Queue <DataStructure.Pair <DataCondition, DataConditionCombinationTypes> > toProcessConditions = new Queue <DataStructure.Pair <DataCondition, DataConditionCombinationTypes> >(from x1 in _Conditions select new DataStructure.Pair <DataCondition, DataConditionCombinationTypes>(x1.Key, x1.Value)); while (toProcessConditions.Count > 0) { DataStructure.Pair <DataCondition, DataConditionCombinationTypes> item1 = toProcessConditions.Dequeue(); // DataConditionSqlGenerationEventArgs e = new DataConditionSqlGenerationEventArgs(); e.ConditionUniqueId = string.Format("{0}_{1}_{2}", level, iLevelIndex, iCondition); e.Condition = item1.Data1; if (DataConditionSqlGeneration != null) { DataConditionSqlGeneration(this, e); } // if (e.Mode == DataConditionSqlGenerationMode.Ignore) { continue; } // if (e.Mode == DataConditionSqlGenerationMode.OtherConditions) { _ConditionHolders.Add(e.OtherConditions, DataConditionCombinationTypes.AND); continue; } // if (!string.IsNullOrEmpty(sql)) { if (item1.Data2 == DataConditionCombinationTypes.AND) { sql += "\nAND\n"; } else { sql += "\nOR\n"; } } // sql += "("; // string sql_Condition = ""; if (item1.Data1.IsNot) { sql_Condition = "NOT ("; } else { sql_Condition = "("; } // if (e.Mode == DataConditionSqlGenerationMode.Auto || e.Mode == DataConditionSqlGenerationMode.AutoWithCustomSqlPlaceHolder) { string paramName = string.Format("@{3}p_{0}_{1}_{2}", level, iLevelIndex, iCondition, _ParametersPrefix); string paramType = ""; string paramValue = ""; bool paramHasValue = false; switch (e.Condition.DataType) { case DataConditionDataTypes.Text: paramHasValue = true; paramValue = e.Condition.ValueString; paramType = "nvarchar(1000)"; break; case DataConditionDataTypes.NumberInteger: paramHasValue = true; paramType = "int"; paramValue = (e.Condition.ValueInt == null) ? "NULL" : e.Condition.ValueInt.ToString(); break; case DataConditionDataTypes.NumberDouble: paramHasValue = true; paramType = "float"; paramValue = (e.Condition.ValueDouble == null) ? "NULL" : e.Condition.ValueDouble.ToString(); break; case DataConditionDataTypes.SingleSelection: paramHasValue = true; paramType = "int"; paramValue = (e.Condition.ValueInt == null) ? "NULL" : e.Condition.ValueInt.ToString(); break; case DataConditionDataTypes.MultiSelection: paramHasValue = (e.Condition.ValueListOfInt != null && e.Condition.ValueListOfInt.Count > 0); paramValue = (paramHasValue) ? "(" + e.Condition.ValueListOfInt.ToSeperated() + ")" : ""; break; case DataConditionDataTypes.Date: case DataConditionDataTypes.Time: case DataConditionDataTypes.DateTime: paramHasValue = true; paramType = "datetime"; paramValue = (e.Condition.ValueDateTime == null) ? "NULL" : string.Format("'{0}'", e.Condition.ValueDateTime.Value.ToString("yyyy-MM-ddTHH:mm:ss.fff")); break; case DataConditionDataTypes.Boolean: paramHasValue = true; paramType = "bit"; paramValue = (e.Condition.ValueInt == null) ? "NULL" : e.Condition.ValueInt.ToString(); break; case DataConditionDataTypes.Custom: break; default: break; } // if (paramHasValue) { string sql_AfterTarget = ""; string sql_BeforeTarget = ""; // switch (e.Condition.CompareType) { case DataCompareTypes.Equal: if (e.Condition.DataType == DataConditionDataTypes.Text) { paramValue = string.Format("N'{0}'", paramValue); } if (paramValue == "NULL") { sql_AfterTarget += " IS NULL"; } else { sql_AfterTarget += " = " + paramName; } break; case DataCompareTypes.GreaterThan: sql_AfterTarget += " > " + paramName; break; case DataCompareTypes.LowerThan: sql_AfterTarget += " < " + paramName; break; case DataCompareTypes.GreaterOrEqual: sql_AfterTarget += " >= " + paramName; break; case DataCompareTypes.LowerOrEqual: sql_AfterTarget += " <= " + paramName; break; case DataCompareTypes.Contains: if (e.Condition.DataType == DataConditionDataTypes.Text) { paramValue = string.Format("N'%{0}%'", paramValue); sql_AfterTarget += " LIKE " + paramName; } else if (e.Condition.DataType == DataConditionDataTypes.MultiSelection) { sql_AfterTarget += " IN " + paramValue; } break; case DataCompareTypes.StartsWith: if (e.Condition.DataType == DataConditionDataTypes.Text) { paramValue = string.Format("N'{0}%'", paramValue); sql_AfterTarget += " LIKE " + paramName; } break; case DataCompareTypes.EndsWith: if (e.Condition.DataType == DataConditionDataTypes.Text) { paramValue = string.Format("N'%{0}'", paramValue); sql_AfterTarget += " LIKE " + paramName; } break; case DataCompareTypes.Among: if (e.Condition.DataType == DataConditionDataTypes.MultiSelection) { sql_AfterTarget += " IN " + paramValue; } break; case DataCompareTypes.BitwiseAnd: if (e.Condition.DataType == DataConditionDataTypes.NumberInteger) { sql_BeforeTarget = "("; sql_AfterTarget += string.Format(" & {0}) = {0}", paramName); } break; default: break; } if (!string.IsNullOrEmpty(paramType)) { declareLines.Add(string.Format("{0} {1}={2}", paramName, paramType, paramValue)); } if (!string.IsNullOrEmpty(sql_AfterTarget)) { string sql_Target; if (e.Mode == DataConditionSqlGenerationMode.Auto) { sql_Target = e.Condition.ColumnName; } else { sql_Target = e.CustomSQL; } // sql_Condition += sql_BeforeTarget + sql_Target + sql_AfterTarget + ")"; if (paramValue != "NULL" && item1.Data1.IsNot) { sql_Condition = string.Format("(({0}) OR ({1} IS NULL))", sql_Condition, sql_Target); } else { sql_Condition = string.Format("({0})", sql_Condition); } // sql += sql_Condition; } } } else if (e.Mode == DataConditionSqlGenerationMode.Custom) { sql_Condition += e.CustomSQL + ")"; sql_Condition = string.Format("({0})", sql_Condition); sql += sql_Condition; } // foreach (string fe1 in e.CustomDeclare) { if (!declareLines.Contains(fe1)) { declareLines.Add(fe1); } } // foreach (var feCustomAdditionalCondition in e.CustomAdditionalConditions) { if (feCustomAdditionalCondition.Value == DataConditionCombinationTypes.AND) { sql += "\nAND\n"; } else { sql += "\nOR\n"; } sql += feCustomAdditionalCondition.Key; } // sql += ")"; // iCondition++; } // int iChildLevelIndex = 1; foreach (DataConditionHolder feDCH in _ConditionHolders.Keys) { EventHandler <DataConditionSqlGenerationEventArgs> feDCH_Event = new EventHandler <DataConditionSqlGenerationEventArgs>(feDCH_DataConditionSqlGeneration); feDCH.DataConditionSqlGeneration += feDCH_Event; if (!string.IsNullOrEmpty(sql)) { if (_ConditionHolders[feDCH] == DataConditionCombinationTypes.AND) { sql += "\nAND\n"; } else { sql += "\nOR\n"; } } // if (feDCH.IsNot) { sql += "NOT ("; } else { sql += "("; } // SSC.DataStructure.Pair <string, List <string> > feDCH_R = feDCH.GenerateSqlWhere_Internal(level + 1, iChildLevelIndex); sql += feDCH_R.Data1; declareLines.AddRange(from x1 in feDCH_R.Data2 where !declareLines.Contains(x1) select x1); sql += ")"; feDCH.DataConditionSqlGeneration -= feDCH_Event; // iChildLevelIndex++; } // r.Data1 = sql; r.Data2 = declareLines; // return(r); }