public static string GenerateInsert(SqlInfo info) { var columnsWithoutSelectedPk = info.TableColumns.Where(c => info.SkipPrimaryKey.All(pk => pk != c)).ToList(); if (info.TenantRelated && !columnsWithoutSelectedPk.Contains(_tenantId)) { columnsWithoutSelectedPk.Add(_tenantId); } if (info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName)) { var columnsJonedWithoutSelectedPk = info.JoinTableColumns.Where(c => info.SkipPrimaryKey.All(pk => pk != c)).ToList(); if (info.TenantRelated && !columnsJonedWithoutSelectedPk.Contains(_tenantId)) { columnsJonedWithoutSelectedPk.Add(_tenantId); } var outputKey = info.ReturnPrimarayKey && string.IsNullOrEmpty(info.PrimaryKeyNames.First()) ? "" : "OUTPUT INSERTED." + info.PrimaryKeyNames.First(); //TODO FIX TO MANY KEYS // use pk from inherite model var values = columnsJonedWithoutSelectedPk.Select(c => c == info.JoinVersionKeyName ? info.VersionKeyName : c == info.JoinPrimaryKeyNames.First() ? info.PrimaryKeyNames.First() : c);//TODO FIX TO MANY KEYS var joinClassValues = Values(values); // return inset into table and join table return "INSERT INTO " + info.JoinTableName + "(" + Fields(columnsJonedWithoutSelectedPk, info.JoinTableName) + ") VALUES(" + joinClassValues + ")\r\n " + "INSERT INTO " + info.TableName + "(" + Fields(columnsWithoutSelectedPk, info.TableName) + ") " + outputKey + " VALUES(" + Values(columnsWithoutSelectedPk) + ") "; } // return select from table return Insert(columnsWithoutSelectedPk, info.TableName, info.ReturnPrimarayKey ? info.PrimaryKeyNames.First() : null) + " "; //TODO FIX TO MANY KEYS }
public static string GenerateFields(SqlInfo info) { return Fields(info.TableColumns, info.TableName); }
private static List<string> GetFullJoinedColumnList(SqlInfo info) { var list = info.JoinTableColumns.ToList(); if (info.TenantRelated && !list.Contains(_tenantId)) { list.Add(_tenantId); } return list; }
public static string GenerateAndVersionsWithAlias(string selectedFilter, SqlInfo info, string condition = "=") { var tableAlias = info.JoinVersionTableName != null ? _joinVersionTableAlias1 : _versionTableAlias1; return And(new[] { selectedFilter }, tableAlias, condition); }
public static string GenerateWhereVersions(IEnumerable<string> selectedFilters, SqlInfo info) { return Where(selectedFilters, info.VersionTableName) + AndTenantRelated(info.VersionTableName, info.TenantRelated) + " "; }
public static string GenerateWhereVersionsWithAlias(IEnumerable<string> selectedFilters, SqlInfo info) { return Where(selectedFilters, _versionTableAlias1) + AndTenantRelated(_versionTableAlias1, info.TenantRelated) + " "; }
public static string GenerateValues(SqlInfo info) { return Values(info.TableColumns); }
public static string GenerateRemove(SqlInfo info) { if (info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName)) return Delete(info.TableName) + " WHERE " + Field(info.TableName, info.PrimaryKeyNames.First()) + " IN (SELECT ItemId FROM " + _tempTable + ");" +//TODO FIX TO MANY KEYS Delete(info.JoinTableName) + " WHERE " + Field(info.JoinTableName, info.JoinPrimaryKeyNames.First()) + " IN (SELECT ItemId FROM " + _tempTable + ");" + " ";//TODO FIX TO MANY KEYS return Delete(info.TableName) + " "; }
public static string GenerateUpdate(SqlInfo info) { var columns = info.TableColumns.Where(c => info.SkipPrimaryKey.All(pk => pk != c)).ToList(); return Update(info.TableName) + " " + Set(columns, info.TableName) + " " + From(info.TableName) + " "; }
public static string GenerateUpdateJoin(SqlInfo info) { // use pk from inherite model var values = info.JoinTableColumns.Select(c => new KeyValuePair<string,string>(c,c == info.JoinVersionKeyName ? info.VersionKeyName : c == info.JoinPrimaryKeyNames.First() ? info.PrimaryKeyNames.First() : c));//TODO FIX TO MANY KEYS return Update(info.JoinTableName) + " " + Set(values, info.JoinTableName) + " " + From(info.JoinTableName) + " "; }
public static string GenerateSelectByToVersionTable(SqlInfo info) { if (info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName)) { // return select from table and join table return Select(info.TableColumns, info.VersionTableName) + "," + Fields(info.JoinTableColumns, info.JoinVersionTableName) + " " + From(info.VersionTableName) + " " + InnerJoin(info.VersionTableName, info.PrimaryKeyNames.First(), info.JoinVersionTableName, info.JoinPrimaryKeyNames.First()) + " ";//TODO FIX TO MANY KEYS } // return select from table return Select(info.TableColumns, info.VersionTableName) + " " + From(info.VersionTableName) + " "; }
public static string GenerateSelectByKeyAndSliceDateToVersionTable(SqlInfo info) { var versionTableAlias = "versionTable"; var joined = info.JoinTableColumns != null && !string.IsNullOrEmpty(info.JoinTableName); if (joined) { var selectMaxModifiedJoin = @"(SELECT " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ", MAX(" + Field(_joinVersionTableAlias1, _sliceDateColumnName) + ") as " + _sliceDateColumnName + " " + From(info.VersionTableName + " " + _versionTableAlias1) + " " + InnerJoin(info.JoinTableName + " " + _joinVersionTableAlias1, Field(_versionTableAlias1, info.VersionKeyName) + " = " + Field(_joinVersionTableAlias1, info.JoinVersionKeyName)) + " " + "{filter} " + " GROUP BY " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ") " + versionTableAlias; // join all columns from main table var comparePrimaryKey = string.Join(" AND ", info.PrimaryKeyNames.Select(pk => Field(versionTableAlias, pk) + " = " + Field(info.JoinVersionTableName, info.JoinPrimaryKeyNames.First()))); var compareSliceDate = Field(versionTableAlias, _sliceDateColumnName) + " = " + Field(info.JoinVersionTableName, _sliceDateColumnName); var join1 = InnerJoin(info.JoinVersionTableName, comparePrimaryKey + " AND " + compareSliceDate); // join all columns from joinned table var compareVersionId = Field(info.JoinVersionTableName, info.JoinVersionKeyName) + " = " + Field(info.VersionTableName, info.VersionKeyName); var join2 = InnerJoin(info.VersionTableName, compareVersionId); return Select(info.TableColumns, info.VersionTableName) + " FROM " + selectMaxModifiedJoin + " " + join1 + " " + join2; } else { var selectMaxModified = @"(SELECT " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ", MAX(" + Field(_versionTableAlias1, _sliceDateColumnName) + ") as " + _sliceDateColumnName + " " + From(info.VersionTableName, _versionTableAlias1) + " " + " {filter} " + " GROUP BY " + string.Join(",", info.PrimaryKeyNames.Select(pk => Field(_versionTableAlias1, pk))) + ") " + versionTableAlias; // join all columns from main table var comparePrimaryKey = string.Join(" AND ", info.PrimaryKeyNames.Select(pk => Field(versionTableAlias, pk) + " = " + Field(info.VersionTableName, pk))); var compareSliceDate = Field(versionTableAlias, _sliceDateColumnName) + " = " + Field(info.VersionTableName, _sliceDateColumnName); var join1 = InnerJoin(info.VersionTableName, comparePrimaryKey + " AND " + compareSliceDate); return Select(info.TableColumns, info.VersionTableName) + " FROM " + selectMaxModified + " " + join1; } }
public static string GenerateSelectAll(SqlInfo info) { return GenerateSelectBy(info, null) + " " + WhereTenantRelated(info.TableName, info.TenantRelated) + " "; }
public static string GenerateInsertToTemp(SqlInfo info) { return "DECLARE " + _tempTable + " TABLE (ItemId uniqueidentifier);" + "INSERT INTO "+ _tempTable + " " + "SELECT " + Field(info.TableName, info.PrimaryKeyNames.First()) + " FROM " + info.TableName + " ";//TODO FIX TO MANY KEYS }
public static string GenerateWhereJoinPkVersion(SqlInfo info) { return Where(new[] { info.JoinPrimaryKeyNames.First() }, info.JoinVersionTableName) + AndTenantRelated(info.JoinVersionTableName, info.TenantRelated) + " "; //TODO FIX TO MANY KEYS }
public static string GenerateInsertToVersionTable(SqlInfo info) { var columns = GetFullColumnList(info); var classProperties = Fields(columns, info.TableName); var classValues = Values(columns); if (info.JoinTableName != null) { var joinFields = GetFullJoinedColumnList(info); var joinClassProperties = Fields(joinFields, info.JoinTableName); // use versionId & primary key from inherite model var values = joinFields.Select(c => c == info.JoinVersionKeyName ? info.VersionKeyName : c == info.JoinPrimaryKeyNames.First() ? info.PrimaryKeyNames.First() : c).ToList(); //TODO FIX TO MANY KEYS var joinClassValues = Values(values); return "INSERT INTO " + info.JoinVersionTableName + "(" + joinClassProperties + ")\r\n" + "VALUES (" + joinClassValues + ")\r\n" + "INSERT INTO " + info.VersionTableName + "(" + classProperties + ")\r\n" + "VALUES (" + classValues + ")"; } if(!info.IsManyToMany) { return "INSERT INTO " + info.VersionTableName + "(" + classProperties + ")\r\n" + "VALUES (" + classValues + ")"; } return "INSERT INTO " + info.VersionTableName + "(" + classProperties + ")\r\n" + "VALUES (" + classValues + ")"; }
public static string GenerateOrderBySliceDate(SqlInfo info) { return OrderBy("Modified", info.JoinTableName, true); }