protected override Sql GetSql <T>(string tableName, string primaryKeyName, bool autoincrement, T poco) { var dictionary = poco.AsDictionary(); var ignored = poco.GetIgnoredColumns(); List <string> columns; if (autoincrement) { columns = dictionary.Keys.Where(x => x.ToUnderscoreLowerCase() != primaryKeyName) .Where(x => !ignored.Contains(x)) .Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList(); } else { columns = dictionary.Keys .Where(x => !ignored.Contains(x)) .Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList(); } var sql = new Sql(); if (!string.IsNullOrWhiteSpace(primaryKeyName)) { string output = "DECLARE @OUTPUT TABLE(id national character varying(1000));"; sql.Append(output); } sql.Append($"INSERT INTO {tableName} ({string.Join(",", columns)})"); sql.Append(!string.IsNullOrWhiteSpace(primaryKeyName) ? $"OUTPUT CAST(INSERTED.\"{primaryKeyName}\" AS national character varying(1000)) INTO @OUTPUT" : ""); sql.Append($"SELECT {string.Join(",", Enumerable.Range(0, columns.Count).Select(x => "@" + x))};"); if (!string.IsNullOrWhiteSpace(primaryKeyName)) { sql.Append("SELECT * FROM @OUTPUT;"); } List <object> values; if (autoincrement) { values = dictionary.Where(x => x.Key.ToUnderscoreLowerCase() != primaryKeyName) .Where(x => !ignored.Contains(x.Key)) .Select(x => x.Value).ToList(); } else { values = dictionary.Values .Where(x => !ignored.Contains(x)) .Select(x => x).ToList(); } sql.AppendParameters(values); return(sql); }
protected virtual Sql GetSql(string tableName, string primaryKeyName, object primaryKeyValue) { var sql = new Sql($"DELETE FROM {tableName}"); sql.Append($"WHERE \"{primaryKeyName}\"=@0;", primaryKeyValue); var values = new List <object> { primaryKeyValue }; sql.AppendParameters(values); return(sql); }
protected virtual Sql GetSql <T>(string tableName, string primaryKeyName, bool autoincrement, T poco) { var dictionary = poco.AsDictionary(); var ignored = poco.GetIgnoredColumns(); List <string> columns; if (autoincrement) { columns = dictionary.Keys.Where(x => x.ToUnderscoreLowerCase() != primaryKeyName) .Where(x => !ignored.Contains(x)) .Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList(); } else { columns = dictionary.Keys .Where(x => !ignored.Contains(x)) .Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList(); } var sql = new Sql($"INSERT INTO {tableName} ({string.Join(",", columns)})"); sql.Append($"SELECT {string.Join(",", Enumerable.Range(0, columns.Count).Select(x => "@" + x))}"); sql.Append(!string.IsNullOrWhiteSpace(primaryKeyName) ? $"RETURNING \"{primaryKeyName}\";" : ";"); List <object> values; if (autoincrement) { values = dictionary.Where(x => x.Key.ToUnderscoreLowerCase() != primaryKeyName) .Where(x => !ignored.Contains(x.Key)) .Select(x => x.Value).ToList(); } else { values = dictionary.Values .Where(x => !ignored.Contains(x)) .Select(x => x).ToList(); } sql.AppendParameters(values); return(sql); }
protected virtual Sql GetSql <T>(T poco, string tableName, string primaryKeyName, object primaryKeyValue, bool isIdentity) { var dictionary = poco.AsDictionary(); var ignored = poco.GetIgnoredColumns(); var columns = dictionary.Keys .Where(x => !ignored.Contains(x)) .Select(key => $"\"{key.ToUnderscoreLowerCase()}\"").ToList(); var sql = new Sql($"UPDATE {tableName}"); sql.Append("SET"); int columnCount = columns.Count; for (int i = 0; i < columnCount; i++) { string column = columns[i]; string token = $"\t{column}=@0"; if (i != columnCount - 1) { token += ","; } sql.Append(token); } sql.Append($"WHERE \"{primaryKeyName}\"=@0;"); var values = dictionary .Where(x => !ignored.Contains(x.Key)) .Select(x => x.Value).ToList(); values.Add(primaryKeyValue); sql.AppendParameters(values); return(sql); }