private async Task SetSelectViewBag(DatabaseGDR databaseGDR)
{
//Busca bancos de dados
List <Report> reportsSelect = await reportRepository.GetList(EnableEnum.Enabled);
List <Report> reportSelected = databaseGDR.ReportDatabaseGDR.Select(x => x.Report).ToList();
reportsSelect = reportsSelect.Except(reportSelected, new ReportIdComparer()).ToList();
ViewBag.ReportId = reportsSelect.Select(x => new SelectListItem
{
Text = x.Name,
Value = x.Id.ToString()
}).ToList();
ViewBag.ReportIdSelected = reportSelected.Select(x => new SelectListItem
{
Text = x.Name,
Value = x.Id.ToString()
}).ToList();
ViewBag.EnableSelect = databaseGDRRepository.GetSelectList(databaseGDR.Enable);
ViewBag.DatabaseTypeId = new SelectList(await databaseTypeRepository.GetList(EnableEnum.Enabled), "Id", "Name", databaseGDR.DatabaseTypeId);
}
public async Task <IActionResult> Create([Bind("Name,Description,DatabaseName,DatabaseServer,DatabaseUser,DatabasePassword,DatabaseTypeId,Port,ConnectionString")] DatabaseGDR databaseGDR, int[] ReportIdSelected)
{
if (ModelState.IsValid)
{
foreach (var reportId in ReportIdSelected)
{
ReportDatabaseGDR reportDatabaseGDR = new ReportDatabaseGDR();
reportDatabaseGDR.ReportID = reportId;
databaseGDR.ReportDatabaseGDR.Add(reportDatabaseGDR);
}
databaseGDR.User = User.Identity.Name;
databaseGDR.ChangeDate = DateTime.Parse(DateTime.Now.ToString());
if (await databaseGDRRepository.Create(databaseGDR))
{
return(RedirectToAction(nameof(Index)));
}
}
ViewBag.EnableSelect = databaseGDRRepository.GetSelectList(databaseGDR.Enable);
ViewBag.DatabaseTypeId = new SelectList(await databaseTypeRepository.GetList(EnableEnum.Enabled), "Id", "Name");
return(View(databaseGDR));
}
public async Task <IActionResult> Edit(int id, [Bind("Id,Name,Description,DatabaseName,DatabaseServer,DatabaseUser,DatabasePassword,DatabaseTypeId,Port,ConnectionString,Enable")] DatabaseGDR databaseGDR, int[] ReportIdSelected)
{
if (id != databaseGDR.Id)
{
return(NotFound());
}
if (ModelState.IsValid)
{
databaseGDR.User = User.Identity.Name;
databaseGDR.ChangeDate = DateTime.Parse(DateTime.Now.ToString());
var result = await databaseGDRRepository.Update(databaseGDR, ReportIdSelected);
if (result == null)
{
return(RedirectToAction(nameof(Index)));
}
return(RedirectToAction(nameof(Index)));
}
await SetSelectViewBag(databaseGDR);
return(View(databaseGDR));
}
private async Task <List <ReportResult> > GetDatabaseResult(DatabaseGDR databaseGDR, Report report, bool header, string filtro)
{
String connectionString;
dynamic connection;
dynamic cmd;
string databaseUser = Cipher.Decrypt(databaseGDR.DatabaseUser, databaseGDR.ChangeDate.ToString());
string databasePassword = Cipher.Decrypt(databaseGDR.DatabasePassword, databaseGDR.ChangeDate.ToString());
switch (databaseGDR.DatabaseTypeId)
{
//MySQL/MariaDB
case 1:
connection = new MySqlConnection();
cmd = new MySqlCommand();
connectionString = $"server={databaseGDR.DatabaseServer};uid={databaseUser};Pwd={databasePassword};Database={databaseGDR.DatabaseName};Port={databaseGDR.Port}";
return(await MultiDatabaseConnection(report.SQL, connection, cmd, connectionString, header, filtro));
//Oracle
case 2:
connection = new OracleConnection();
cmd = new OracleCommand();
connectionString = $"User Id={databaseUser};Password={databasePassword};Data Source={databaseGDR.DatabaseServer}:{databaseGDR.Port}/{databaseGDR.DatabaseName}";
return(await MultiDatabaseConnection(report.SQL, connection, cmd, connectionString, header, filtro));
//PostgreSQL
case 3:
connection = new NpgsqlConnection();
cmd = new NpgsqlCommand();
connectionString = $"Host={databaseGDR.DatabaseServer};Username={databaseUser};Password={databasePassword};Database={databaseGDR.DatabaseName};Port={databaseGDR.Port}";
return(await MultiDatabaseConnection(report.SQL, connection, cmd, connectionString, header, filtro));
//SQL Server
case 4:
connection = new SqlConnection();
cmd = new SqlCommand();
connectionString = $"Server={databaseGDR.DatabaseServer},{databaseGDR.Port};User Id={databaseUser};Password={databasePassword};Initial Catalog={databaseGDR.DatabaseName}";
return(await MultiDatabaseConnection(report.SQL, connection, cmd, connectionString, header, filtro));
//Firebird
case 5:
connection = new FbConnection();
cmd = new FbCommand();
connectionString = $"User={databaseUser};Password={databasePassword};Database={databaseGDR.DatabaseName};DataSource={databaseGDR.DatabaseServer};Port={databaseGDR.Port}";
return(await MultiDatabaseConnection(report.SQL, connection, cmd, connectionString, header, filtro));
//OpenEdge
//Este driver usa o campo descrição para complementar a string odbc
case 6:
connection = new OdbcConnection();
cmd = new OdbcCommand();
connectionString = databaseGDR.ConnectionString;
connectionString = connectionString.Replace("Nome do servidor", databaseGDR.DatabaseServer);
connectionString = connectionString.Replace("Porta", databaseGDR.Port.ToString());
connectionString = connectionString.Replace("Nome do banco", databaseGDR.DatabaseName);
connectionString = connectionString.Replace("Nome do usuário", databaseUser);
connectionString = connectionString.Replace("Senha", databasePassword);
return(await MultiDatabaseConnection(report.SQL, connection, cmd, connectionString, header, filtro));
}
return(null);
}
