internal static SQLDMLStatement GetDDLStatementGenericEntityStorageCreation(IDatabaseTenant Tenant, object Entity) { var statement = new SQLDMLStatement { PreparedStatement = BuildTableCreateForEntitySql(Entity), StatemtType = DMLStatemtType.Create }; return(statement); }
public bool TableExists(string EntityType) { var sql = "select count(*) From sysobjects where type='U' and name = @name;"; List <ISQLDMLStatementVariable> vars = new List <ISQLDMLStatementVariable> { new SQLDMLStatementVariable { Name = "@name", Value = EntityType } }; SQLDMLStatement dml = new SQLDMLStatement { PreparedStatement = sql, StatemtType = DMLStatemtType.Select, Variables = vars }; var results = (DataTable)(((IExecuteDML)this)).Execute(dml); var count = Convert.ToInt32(results.Rows[0].ItemArray[0]); return(count > 0); }
public bool DoesEntityWithSamePrimaryKeyExist(object Entity) { EntityDescriber ed = new EntityDescriber(Entity); if (ed.PrimaryKeys().Count > 0) { var sql = DMLStatementFactory.BuildExistenceCheckSql(Entity, true); List <ISQLDMLStatementVariable> vars = DMLStatementFactory.GetUniqueKeyNameValuePairs(Entity, true, false); SQLDMLStatement dml = new SQLDMLStatement { PreparedStatement = sql, StatemtType = DMLStatemtType.Select, Variables = vars }; var results = (DataTable)((IExecuteDML)_dbaccess).Execute(dml); var count = Convert.ToInt32(results.Rows[0].ItemArray[0]); return(count > 0); } else { return(false); } }
private SQLDMLStatement _GetDMLScript(IDatabaseTenant Tenant, string DMLStatementPrefix, object[] ParameterNames, object[] ParameterValues, int expectedParamCount, DMLStatemtType DMLStatemtType, List <ISQLDMLStatementVariable> uniqueKeys) { if (expectedParamCount != ParameterValues.GetUpperBound(0) + 1) { throw new Exception("Number of parameters supplied for statement differs from expected"); } if (DMLStatemtType == DMLStatemtType.SelectAll && false) { ParameterNames = AppendValueToBeginningOfArray(new object[0], "@Tenant"); ParameterValues = AppendValueToBeginningOfArray(new object[0], Tenant.Code); } else { ParameterNames = AppendValueToBeginningOfArray(ParameterNames, "@Tenant"); ParameterValues = AppendValueToBeginningOfArray(ParameterValues, Tenant.Code); } if (ParameterNames.GetUpperBound(0) != ParameterValues.GetUpperBound(0)) { throw new Exception("CODE LOGIC ERROR: Param names and value count mismatch"); } string paramlist = ""; string paramlist2 = ""; string dmlSuffix = ""; int j = 0; foreach (var param in ParameterValues) { if (ParameterNames[j].ToString().Contains("@@")) { if (ParameterValues[j].ToString().Contains("'")) { throw new Exception("injection attempt"); } else { DMLStatementPrefix = DMLStatementPrefix.Replace(ParameterNames[j].ToString(), ParameterValues[j].ToString()); } } else if (DMLStatementPrefix.Contains(ParameterNames[j].ToString())) { } else if (DMLStatemtType == DMLStatemtType.Insert) { paramlist += ParameterNames[j] + ","; } else if (DMLStatemtType == DMLStatemtType.Delete) { bool considerUniqueKeys = true; considerUniqueKeys = uniqueKeys != null; if (considerUniqueKeys) { if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0) { paramlist += (paramlist.Length > 0 ? " AND " : " "); paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j]; } } else { paramlist += (j > 0 ? " AND " : " "); paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j]; } } else if (DMLStatemtType == DMLStatemtType.Select || DMLStatemtType == DMLStatemtType.SelectAll) { bool considerUniqueKeys = true; considerUniqueKeys = uniqueKeys != null; if (considerUniqueKeys) { if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0) { paramlist += (j > 0 ? " AND " : " "); paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j]; } } else { paramlist += (j > 0 ? " AND " : " "); paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j]; } } else if (DMLStatemtType == DMLStatemtType.Update) { bool isUniqueKey = false; bool considerUniqueKeys = (uniqueKeys != null); if (considerUniqueKeys) { if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0) { isUniqueKey = true; } } if (isUniqueKey && considerUniqueKeys) { paramlist += (j > 0 ? " AND " : " "); paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j]; } else if (!isUniqueKey && considerUniqueKeys) { paramlist2 += (paramlist2.Length > 0 ? " , " : " "); paramlist2 += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j].ToString(); } else if (ParameterNames[j].ToString().Substring(0, 1) == "$") { paramlist2 += (paramlist2.Length > 0 ? " , " : " "); paramlist2 += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j].ToString().Replace("$", "@"); } else { paramlist += (j > 0 ? " AND " : " "); paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j]; } } j++; } if ((paramlist.Length > 0) && (paramlist.EndsWith(","))) { paramlist = paramlist.Substring(0, paramlist.Length - 1); } if (paramlist.Length > 0) { dmlSuffix += (DMLStatemtType == DMLStatemtType.Insert ? $" values ({paramlist})" : ""); dmlSuffix += (DMLStatemtType == DMLStatemtType.Delete ? $" where {paramlist}" : ""); dmlSuffix += (DMLStatemtType == DMLStatemtType.Select ? $" where {paramlist}" : ""); dmlSuffix += (DMLStatemtType == DMLStatemtType.SelectAll ? $" where {paramlist}" : ""); dmlSuffix += (DMLStatemtType == DMLStatemtType.Update ? paramlist2 + $" where {paramlist}" : ""); } List <ISQLDMLStatementVariable> sqlVars = new List <ISQLDMLStatementVariable>(); int i = 0; foreach (var val in ParameterValues) { string paramNam = ""; paramNam = ParameterNames[i].ToString().Replace("$", "@"); if (DMLStatementPrefix.Contains(paramNam) || dmlSuffix.Contains(paramNam)) { sqlVars.Add(new SQLDMLStatementVariable { Name = paramNam, Value = val }); } i++; } SQLDMLStatement dmlStatement = new SQLDMLStatement { PreparedStatement = DMLStatementPrefix + dmlSuffix, Variables = sqlVars, StatemtType = DMLStatemtType }; //if (expectedParamCount != dmlStatement.ParameterCount - 1) // throw new Exception("CODE LOGIC ERROR : Param Count Mismatch"); return(dmlStatement); }