/// <summary>
/// Verifica se a tabela está sem registros e se o id está zerado
/// </summary>
public Boolean TableIsEmpty()
{
DBQuery dbQuery = new DBQuery(sqlConnection);
dbQuery.Query = "use " + tableData.DatabaseName;
dbQuery.Execute(false);
// Verifica se a tabela está sem registros
dbQuery.Query = "SELECT TOP 1 * FROM " + tableData.TableName;
dbQuery.Execute(true);
List <Object> rowList = dbQuery.ExtractFromResultset(typeof(Object));
if (rowList.Count > 0)
{
return(false);
}
// Verifica se o id está zerado
dbQuery.Query = "SELECT IDENT_CURRENT('" + tableData.TableName + "')";
dbQuery.Execute(true);
int?identity = dbQuery.ExtractFromResultset();
if ((identity != null) && (identity.Value > 1))
{
return(false);
}
return(true);
}
/// <summary>
/// Exporta as tabelas existentes no database para arquivos XML, os dados são gravados no XML em
/// formato pronto para INSERT (sem informação de tipo porém no formato esperado pelo BD)
/// </summary>
public Boolean DBExport(String databaseName)
{
if (!OpenConnection())
{
return(false);
}
currentDatabase = databaseName;
decimalSeparatorIsComma = FieldParser.IsCommaDecimalSeparator();
DBQuery dbQuery = new DBQuery(sqlConnection);
dbQuery.Query = "use " + currentDatabase;
dbQuery.Execute(false);
dbQuery.Query = "SELECT name, id FROM sysObjects WHERE xtype = 'U'";
dbQuery.Execute(true);
List <Object> tableList = dbQuery.ExtractFromResultset(typeof(DBObject));
if (tableList.Count < 1)
{
CloseConnection();
return(false);
}
foreach (DBObject table in tableList)
{
// Verifica a quantidade de registros da tabela
dbQuery.Query = "SELECT COUNT(1) FROM " + table.name;
dbQuery.Execute(true);
int?rowCount = dbQuery.ExtractFromResultset();
if (rowCount > 1000000)
{
continue; // Pula tabelas com mais de 1 milhão de registros ( backup manual )
}
dbQuery.Query = "SELECT name FROM sysColumns WHERE id = " + table.id;
dbQuery.Execute(true);
List <Object> fieldList = dbQuery.ExtractFromResultset(new String[] { "name" });
String[] fieldNames = new String[fieldList.Count];
for (int index = 0; index < fieldList.Count; index++)
{
fieldNames[index] = (String)((Object[])fieldList[index])[0];
}
dbQuery.Query = "SELECT * FROM " + table.name;
dbQuery.Execute(true);
List <Object> rowList = dbQuery.ExtractFromResultset(fieldNames);
Boolean tableExported = ExportTable(table.name, fieldNames, rowList);
if (!tableExported)
{
CloseConnection();
return(false);
}
}
CloseConnection();
return(true);
}
public Boolean Create()
{
if (!OpenConnection())
{
return(false);
}
DBQuery dbQuery;
try
{
dbQuery = new DBQuery(sqlConnection);
// dbQuery.Query = "use AppCommon";
// dbQuery.Execute(false);
}
catch (Exception exc)
{
lastError = "Não foi possível setar o database. " + exc.Message;
return(false);
}
try
{
// Cria um login de administrador na tabela "tb_administratorLogin"
dbQuery.Query = "INSERT INTO tb_administratorLogin VALUES ('admin', '1E588BE3A984524C7F2C278686F44E72')";
dbQuery.Execute(false);
// Insere os aplicativos na tabela "tb_application"
dbQuery.Query = "INSERT INTO tb_application VALUES ('Print Accounting', 0) SELECT SCOPE_IDENTITY() id";
dbQuery.Execute(true);
int?accountingAppId = dbQuery.ExtractFromResultset();
dbQuery.Query = "INSERT INTO tb_application VALUES ('Remote Device Management', 1)";
dbQuery.Execute(false);
dbQuery.Query = "INSERT INTO tb_application VALUES ('Enterprise Content Management', 0)";
dbQuery.Execute(false);
dbQuery.Query = "INSERT INTO tb_application VALUES ('Variable Data Printing', 1)";
dbQuery.Execute(false);
// Insere os parâmetros de aplicativo na tabela "tb_applicationParam"
dbQuery.Query = "INSERT INTO tb_applicationParam VALUES ('interval', '599000', " + accountingAppId.Value + ", 'reportMailing')";
dbQuery.Execute(false);
dbQuery.Query = "INSERT INTO tb_applicationParam VALUES ('url', 'http://www.datacopy.com.br/Datacount', " + accountingAppId.Value + ", 'webAccounting')";
dbQuery.Execute(false);
}
catch (Exception exc)
{
lastError = "Não foi possível inserir dados na tabelas. " + exc.Message;
return(false);
}
CloseConnection();
return(true);
}
/// <summary>
/// Busca as foreign keys do database
/// </summary>
public static List <Object> GetForeignKeys(SqlConnection sqlConnection, String databaseName)
{
List <Object> foreignKeys = null;
DBQuery dbQuery = new DBQuery(sqlConnection);
dbQuery.Query = "use " + databaseName;
dbQuery.Execute(false);
dbQuery.Query = "SELECT OBJECT_NAME(PARENT_OBJECT_ID) SrcTable," + Environment.NewLine +
" SRC.NAME SrcField," + Environment.NewLine +
" OBJECT_NAME(REFERENCED_OBJECT_ID) RefTable," + Environment.NewLine +
" REF.NAME RefField " + Environment.NewLine +
"FROM SYS.FOREIGN_KEY_COLUMNS FKC " + Environment.NewLine +
" JOIN SYS.COLUMNS SRC " + Environment.NewLine +
" ON FKC.PARENT_OBJECT_ID = SRC.OBJECT_ID " + Environment.NewLine +
" AND FKC.PARENT_COLUMN_ID = SRC.COLUMN_ID " + Environment.NewLine +
" JOIN SYS.COLUMNS REF " + Environment.NewLine +
" ON FKC.REFERENCED_OBJECT_ID = REF.OBJECT_ID " + Environment.NewLine +
" AND FKC.REFERENCED_COLUMN_ID = REF.COLUMN_ID";
dbQuery.Execute(true);
foreignKeys = dbQuery.ExtractFromResultset(new String[] { "SrcTable", "SrcField", "RefTable", "RefField" });
return(foreignKeys);
}
private int GetReferencedRecordFixedId(String oldId)
{
// Busca o registro referenciado no xml(dependência)
String dependency = currentTableReference;
XmlNode referencedRecord = GetReferencedRecord(dependency, oldId);
if (referencedRecord == null)
{
return(0);
}
// Monta a query de seleção do registro
NameValueCollection query = new NameValueCollection();
String queryText = "";
foreach (XmlAttribute attrib in referencedRecord.Attributes)
{
// Se não for chave primária nem estrangeira adiciona na query
if ((attrib.Name != "id") && (!IsForeignKey(dependency, attrib.Name)))
{
query.Add(attrib.Name, attrib.Value);
if (!String.IsNullOrEmpty(queryText))
{
queryText += " AND ";
}
String condition = attrib.Name + "=" + attrib.Value;
if (attrib.Value == "null")
{
condition = attrib.Name + " is NULL";
}
queryText += condition;
}
}
// Busca no banco o registro equivalente
String statement = "SELECT id FROM " + dependency + " WHERE {0}";
DBQuery dbQuery = new DBQuery(sqlConnection);
dbQuery.Query = "use " + tableDependencies[dependency].DatabaseName;
dbQuery.Execute(false);
dbQuery.Query = String.Format(statement, queryText);
dbQuery.Execute(true);
// Busca o indice relativo ( quando existem varios registros que atendem a query )
int relativeIndex = GetRelativeIndex(dependency, oldId, query);
if (relativeIndex == 0)
{
int?referencedRecordId = dbQuery.ExtractFromResultset();
if (referencedRecordId == null)
{
return(0);
}
return(referencedRecordId.Value);
}
// A existência de vários registros correspondentes é um indício de que podem existir
// dados corrompidos, então avisa o usuário e utiliza o indice relativo.
String importWarning = "A tabela " + tableData.TableName + " apresentou problemas durante a importação. Faça uma verificação.";
if (listener != null)
{
listener.NotifyObject(importWarning);
}
List <Object> resultset = dbQuery.ExtractFromResultset(new String[] { "id" });
return((int)((Object[])resultset[relativeIndex - 1])[0]);
}
private Boolean ImportFile(String filename, List <int> tenantsToImport, Boolean preserveRecordId)
{
String tableName = "tb_" + Path.GetFileNameWithoutExtension(filename);
progressLog += tableName + Environment.NewLine;
XmlNode rowCollection;
ImportData importData = new ImportData(filename);
currentDatabase = importData.DatabaseName;
rowCollection = importData.MainNode;
if ((currentDatabase == null) || (rowCollection == null))
{
lastError = importData.GetLastError();
return(false);
}
// Se o XML não possui registros considera como se tivesse importado e retorna
if (rowCollection.FirstChild == null)
{
return(true);
}
DBQuery dbQuery = new DBQuery(sqlConnection);
// Evita alterar o banco de dados pois o Microsoft Azure não tem suporte a essa operação
// dbQuery.Query = "use " + currentDatabase;
// dbQuery.Execute(false);
// Verifica se a tabela já está populada, se estiver aborta
ImportController controller = new ImportController(sqlConnection, importData, this);
if (!controller.TableIsEmpty())
{
lastError = "A importação foi cancelada pois a tabela " + tableName + " já possui dados.";
return(false);
}
try // tenta fazer a inserção dos dados na tabela
{
controller.MountDependencies(filesToImport, databaseForeignKeys);
int?lastInsertedId = 0;
while (controller.MoveNextRecord())
{
int?owner = controller.GetRecordOwner();
if ((owner == null) || (tenantsToImport.Contains(owner.Value)))
{
// Conserta o identity para que seja mantida a numeração original
if (preserveRecordId)
{
controller.FixIdentity(lastInsertedId);
}
dbQuery.Query = controller.MountInsertStatement();
dbQuery.Execute(true);
lastInsertedId = dbQuery.ExtractFromResultset();
}
progressMeter.IncreaseProgress(1);
}
}
catch (Exception exc)
{
lastError = exc.Message + Environment.NewLine + progressLog;
return(false);
}
return(true);
}