public void IniciarSQL(DataTable dataAccess, string tabela)
{
try
{
SqlCommand command = new SqlCommand();
// conexaoSQL.ConnectionString = @"Data Source=192.168.1.4;Initial Catalog=Habil;User=sa;Password=ti@sup0rt3";
command.Connection = con;
con.Open();
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = tabela;
foreach (var column in dataAccess.Columns)
{
sqlBulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
}
sqlBulkCopy.WriteToServer(dataAccess);
}
con.Close();
}
catch (Exception ex)
{
LogErrosDAO log = new LogErrosDAO();
log.Salvar_Log("ReportHabilDAO IniciarSQL", ex.Message);
}
}
public void IniciarAccess(string tabela)
{
try
{
// Conectar com habil
// string conectarAcess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destFile.Replace(" ", "") + ";Jet OLEDB:Database Password = amoragape;Persist Security Info=True";
string conectarAcess = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + destFile.Replace(" ", "") + ";Jet OLEDB:Database Password = amoragape;Persist Security Info=True";
string localizar = "select * from " + tabela;
OleDbConnection conexaoAcess = new OleDbConnection(conectarAcess);
OleDbCommand Acess = new OleDbCommand(localizar, conexaoAcess);
DataTable dtaccess = null;
conexaoAcess.Open();
Acess.CommandText = localizar;
dtaccess = new DataTable();
dtaccess.Load(Acess.ExecuteReader());
conexaoAcess.Close();
IniciarSQL(dtaccess, tabela);
}
catch (Exception ex)
{
LogErrosDAO log = new LogErrosDAO();
log.Salvar_Log("ReportHabilDAO IniciarAccess", ex.Message);
}
}
public void Carregar_Lista(List <string> lista)
{
try
{
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = "select * from Lista_Tabelas";
con.Open();
SqlDataReader Lista = command.ExecuteReader();
int progs = 0;
while (Lista.Read())
{
lista.Add(Lista[1].ToString());
progs++;
}
con.Close();
}
catch (Exception ex)
{
LogErrosDAO log = new LogErrosDAO();
log.Salvar_Log("ReportHabilDAO Carregar_Lista", ex.Message);
}
}
} //Fazer verificação para remover arquivos antigos.
public void Copiar_Arquivo()
{
try
{
/*destFile = @"D:\Habil_to_SQL\habil.mdb";
*
* // Verifica se existe um arquivo Se existir apaga para recriar
* if (System.IO.File.Exists(destFile))
* {
* System.IO.File.Delete(destFile);
* }
*
* System.Diagnostics.Process.Start(@"D:\Habil_to_SQL\Copiar.bat");
*
*/
string fileName = "habil.mdb";
string diretorio = Convert.ToString(System.DateTime.Today.Date);
string sourcePath = @"D:\Habil Empresarial"; //@"D:\Habil Empresarial\Habil Empresarial\Dados";
string targetPath = @"D:\Temp_Habil\Habil_TO_SQL"; // + diretorio.Replace("/", "-").Replace("00:00:00", "");
// string targetPath = @"D:\Habil_to_SQL\" + diretorio;
string targetPathFile = targetPath + @"\" + fileName;
string sourceFile = System.IO.Path.Combine(sourcePath, fileName);
destFile = System.IO.Path.Combine(targetPath, fileName);
//Criar Diretorio se nao existir
if (!System.IO.Directory.Exists(targetPath))
{
System.IO.Directory.CreateDirectory(targetPath);
}
// Verifica se existe um arquivo Se existir apaga para recriar
if (System.IO.File.Exists(targetPathFile))
{
System.IO.File.Delete(targetPathFile);
}
//Copiar Arquivo
System.IO.File.Copy(sourceFile, destFile, true);
}
catch (Exception ex)
{
LogErrosDAO log = new LogErrosDAO();
log.Salvar_Log("ReportHabilDAO Copiar_Arquivo", ex.Message);
}
}
public void ResetarTabelas()
{
try
{
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = "truncate table ANEXOS " +
"truncate table Caixa_Configuracao " +
"truncate table Caixa_Contas " +
"truncate table Caixa_Lancamentos " +
"truncate table Caixa_Lancamentos_Detalhes " +
"truncate table CAIXAS " +
"truncate table Centros_Custos " +
"truncate table Cliente " +
"truncate table Codigo_Bancario_Padrao " +
"truncate table Codigos " +
"truncate table Contas_Bancarias " +
"truncate table Contas_Bancarias_Lancamentos " +
"truncate table Contas_Pagar " +
"truncate table Contas_Receber " +
"truncate table DETALHES_CONTACAIXA " +
"truncate table DetalhesLctos " +
"truncate table Empresa " +
"truncate table Fonte_Sql " +
"truncate table FORMAS_PGTO " +
"truncate table Fornecedor " +
"truncate table IMAGENS " +
"truncate table RapidAction " +
"truncate table Usuarios";
con.Open();
command.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
LogErrosDAO log = new LogErrosDAO();
log.Salvar_Log("ReportHabilDAO ResetarTabelas", ex.Message);
}
} //Fazer verificação para remover arquivos antigos.
public void SalvaUltimaAtualizacao()
{
try
{
SqlCommand command = new SqlCommand();
// con.ConnectionString = @"Data Source=192.168.1.4;Initial Catalog=Habil;User=sa;Password=ti@sup0rt3";
command.Connection = con;
command.CommandText = "INSERT INTO ULTIMA_ATUALIZACAO (DATA) VALUES (GETDATE())";
con.Open();
command.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
LogErrosDAO log = new LogErrosDAO();
log.Salvar_Log("ReportHabilDAO SalvaUltimaAtualizacao", ex.Message);
}
}
public string ConsultaUltimaAtualizacao()
{
string data = "";
try
{
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = "select top 1 format(data, 'dd/MM/yyyy HH:mm:ss') from ULTIMA_ATUALIZACAO order by data desc";
con.Open();
data = (string)command.ExecuteScalar();
con.Close();
}
catch (Exception ex)
{
LogErrosDAO log = new LogErrosDAO();
log.Salvar_Log("ReportHabilDAO ConsultarUltimaAtualização", ex.Message);
}
return(data);
}