public IEnumerable <string> BuscarBancos(string server, string user, string senha)
{
var conexao = BaseDAO.CriarConexao(new AppSettings
{
ConnectionProvider = "sqlserver",
ConnectionString = $"Data Source={server};User ID={user};Password={senha};Initial Catalog=master;Persist Security Info=True"
});
var databases = conexao.Query <string>("SELECT name FROM master.dbo.sysdatabases WHERE name not in ('master', 'tempdb', 'model','msdb') ORDER BY name");
return(databases);
}
public IActionResult BuscarColunas(string server, string user, string senha, string database, string tabela, bool sinonimo)
{
var conexao = BaseDAO.CriarConexao(new AppSettings
{
ConnectionProvider = "sqlserver",
ConnectionString = $"Data Source={server};User ID={user};Password={senha};Initial Catalog={database};Persist Security Info=True"
});
string sql;
if (sinonimo)
{
sql = $"SELECT TOP(0) * INTO #tmpColumns FROM {tabela};" +
"SELECT tempdb.sys.columns.name, tempdb.sys.columns.max_length, tempdb.sys.columns.precision, tempdb.sys.columns.scale, tempdb.sys.columns.is_nullable, tempdb.sys.columns.is_identity, sys.types.name as type " +
"FROM tempdb.sys.columns " +
"INNER JOIN sys.types ON tempdb.sys.columns.user_type_id = sys.types.user_type_id " +
"WHERE object_id = OBJECT_ID('tempdb.dbo.#tmpColumns');" +
"DROP TABLE #tmpColumns;";
}
else
{
sql = "SELECT sys.columns.name, sys.columns.max_length, sys.columns.precision, sys.columns.scale, sys.columns.is_nullable, sys.columns.is_identity, sys.types.name as type " +
"FROM sys.columns " +
"INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id " +
$"WHERE OBJECT_NAME(object_id) = '{tabela}';";
}
var colunas = conexao.Query(sql).ToList();
var listaColunas = new List <dynamic>();
foreach (var column in colunas)
{
listaColunas.Add(new
{
column.name,
column.type,
column.is_nullable,
column.is_identity
});
}
return(Ok(listaColunas));
}
