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));
}
}
