public IList <string> ListColumnsInView(string schemaName, string viewName)
{
using OrmCookbookContext context = CreateDbContext();
using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
const string sql =
@"SELECT c.name AS ColumnName
FROM sys.columns c
INNER JOIN sys.views v ON c.object_id = v.object_id
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE s.name = @SchemaName AND v.name = @ViewName";
commnd.CommandText = sql;
commnd.Parameters.Add(new SqlParameter {
ParameterName = "SchemaName", DbType = DbType.String, Value = schemaName
});
commnd.Parameters.Add(new SqlParameter {
ParameterName = "ViewName", DbType = DbType.String, Value = viewName
});
commnd.Connection !.Open();
DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
var columnNames = new List <string>();
while (reader.Read())
{
int columnNameOrdinal = reader.GetOrdinal("ColumnName");
string columnName = reader.GetString(columnNameOrdinal);
columnNames.Add(columnName);
}
return(columnNames);
}
public DataTable GetAll(string schema, string tableName)
{
using OrmCookbookContext context = CreateDbContext();
using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
string sql = $"SELECT * FROM [{schema}].[{tableName}];";
commnd.CommandText = sql;
commnd.Connection !.Open();
using DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
var result = new DataTable();
result.Load(reader);
return(result);
}
public IList <string> ListTables()
{
using OrmCookbookContext context = CreateDbContext();
using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
const string sql =
@"SELECT s.name + '.' + t.name AS TableName
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id";
commnd.CommandText = sql;
commnd.Connection !.Open();
DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
var tableNames = new List <string>();
while (reader.Read())
{
int tableNameOrdinal = reader.GetOrdinal("TableName");
string tableName = reader.GetString(tableNameOrdinal);
tableNames.Add(tableName);
}
return(tableNames);
}
