public static DataTable GetTable(string tableSchema, string tableName, string columnNames, string columnValues) { if (string.IsNullOrWhiteSpace(columnNames)) { return(null); } if (string.IsNullOrWhiteSpace(columnValues)) { return(null); } var columns = columnNames.Split(','); var values = columnValues.Split(','); if (!columns.Length.Equals(values.Length)) { return(null); } var counter = 0; var sql = "SELECT * FROM @TableSchema.@TableName WHERE "; foreach (var column in columns) { if (!counter.Equals(0)) { sql += " AND "; } sql += Sanitizer.SanitizeIdentifierName(column.Trim()) + " = @" + Sanitizer.SanitizeIdentifierName(column.Trim()); counter++; } sql += ";"; using (var command = new NpgsqlCommand()) { sql = sql.Replace("@TableSchema", Sanitizer.SanitizeIdentifierName(tableSchema)); sql = sql.Replace("@TableName", Sanitizer.SanitizeIdentifierName(tableName)); command.CommandText = sql; counter = 0; foreach (var column in columns) { command.Parameters.AddWithValue("@" + Sanitizer.SanitizeIdentifierName(column.Trim()), values[counter]); counter++; } return(DbOperations.GetDataTable(command)); } }
public static DataTable GetTable(string tableSchema, string tableName) { var sql = "SELECT * FROM @TableSchema.@TableName;"; using (var command = new NpgsqlCommand()) { sql = sql.Replace("@TableSchema", Sanitizer.SanitizeIdentifierName(tableSchema)); sql = sql.Replace("@TableName", Sanitizer.SanitizeIdentifierName(tableName)); command.CommandText = sql; return(DbOperations.GetDataTable(command)); } }
public static DataTable GetView(string tableSchema, string tableName, string orderBy, int limit, int offset) { var sql = "SELECT * FROM @TableSchema.@TableName ORDER BY @OrderBy LIMIT @Limit OFFSET @Offset;"; using (var command = new NpgsqlCommand()) { //We are 100% sure that the following parameters do not come from user input. //Having said that, it is nice to sanitize the objects before sending it to the database server. sql = sql.Replace("@TableSchema", Sanitizer.SanitizeIdentifierName(tableSchema)); sql = sql.Replace("@TableName", Sanitizer.SanitizeIdentifierName(tableName)); sql = sql.Replace("@OrderBy", Sanitizer.SanitizeIdentifierName(orderBy)); sql = sql.Replace("@Limit", Conversion.TryCastString(limit)); sql = sql.Replace("@Offset", Conversion.TryCastString(offset)); command.CommandText = sql; return(DbOperations.GetDataTable(command)); } }
public static DataTable GetTable(string schema, string tableName, string exclusion) { string sql; if (!string.IsNullOrWhiteSpace(exclusion)) { var exclusions = exclusion.Split(','); var paramNames = exclusions.Select((s, i) => "@Paramter" + i.ToString(Thread.CurrentThread.CurrentCulture).Trim()).ToArray(); var inClause = string.Join(",", paramNames); sql = string.Format(Thread.CurrentThread.CurrentCulture, @"select * from scrud.mixerp_table_view where table_schema=@Schema AND table_name=@TableName AND column_name NOT IN({0}) ORDER BY ordinal_position ASC;", inClause); using (var command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@Schema", schema); command.Parameters.AddWithValue("@TableName", tableName); for (var i = 0; i < paramNames.Length; i++) { command.Parameters.AddWithValue(paramNames[i], exclusions[i].Trim()); } return(DbOperations.GetDataTable(command)); } } sql = "select * from scrud.mixerp_table_view where table_schema=@Schema AND table_name=@TableName ORDER BY ordinal_position ASC;"; using (var command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@Schema", schema); command.Parameters.AddWithValue("@TableName", tableName); return(DbOperations.GetDataTable(command)); } }
public static DataTable GetTable(string tableSchema, string tableName, string columnNames, string columnValuesLike, int limit) { if (columnNames == null) { columnNames = string.Empty; } if (columnValuesLike == null) { columnValuesLike = string.Empty; } var columns = columnNames.Split(','); var values = columnValuesLike.Split(','); if (!columns.Length.Equals(values.Length)) { return(null); } var counter = 0; var sql = "SELECT * FROM @TableSchema.@TableName "; foreach (var column in columns) { if (!string.IsNullOrWhiteSpace(column)) { if (counter.Equals(0)) { sql += " WHERE "; } else { sql += " AND "; } sql += " lower(" + Sanitizer.SanitizeIdentifierName(column.Trim()) + "::text) LIKE @" + Sanitizer.SanitizeIdentifierName(column.Trim()); counter++; } } sql += " LIMIT @Limit;"; using (var command = new NpgsqlCommand()) { sql = sql.Replace("@TableSchema", Sanitizer.SanitizeIdentifierName(tableSchema)); sql = sql.Replace("@TableName", Sanitizer.SanitizeIdentifierName(tableName)); command.CommandText = sql; counter = 0; foreach (var column in columns) { if (!string.IsNullOrWhiteSpace(column)) { command.Parameters.AddWithValue(Sanitizer.SanitizeIdentifierName(column.Trim()), "%" + values[counter].ToLower(Thread.CurrentThread.CurrentCulture) + "%"); counter++; } } command.Parameters.AddWithValue("@Limit", limit); return(DbOperations.GetDataTable(command)); } }