/// <summary>
/// Adiciona um registro no banco de dados
/// </summary>
/// <returns>true se adicionar</returns>
public virtual void Add()
{
using (var trans = TransManager.GetTrans())
{
var eventArgs = new TableAdapterEventArgs();
if (OnBeforeAdd != null)
{
OnBeforeAdd(this, eventArgs);
}
var inserdCommand = GetInsertCommand();
var lastInsertedId = Connection.SqlServerExecuteAndGetLastInsertId(inserdCommand.Item1, inserdCommand.Item2);
SetIdentityValue(lastInsertedId);
eventArgs.Keys = KeyFields;
eventArgs = new TableAdapterEventArgs();
if (OnAfterAdd != null)
{
OnAfterAdd(this, eventArgs);
}
trans.Complete();
}
}
/// <summary>
/// Criar a tabela no banco de dados
/// </summary>
/// <returns>true se criar</returns>
public virtual void Create()
{
try
{
using (var trans = TransManager.GetTrans())
{
var eventArgs = new TableAdapterEventArgs();
if (OnBeforeCreate != null)
{
OnBeforeCreate(this, eventArgs);
}
var command = GetCreateCommand();
Connection.SqlServerExecute(command.Item1, command.Item2);
eventArgs = new TableAdapterEventArgs();
if (OnAfterCreate != null)
{
OnAfterCreate(this, eventArgs);
}
trans.Complete();
}
}
catch (Exception ex)
{
var message = string.Format("Erro ao criar a tabela [{0}..{1}]", DBName, TableName);
throw new Exception(message, ex);
}
}
/// <summary>
/// Atualiza o registro no banco de dados
/// </summary>
/// <returns>True se atualizar</returns>
public virtual void Update()
{
using (var trans = TransManager.GetTrans())
{
var eventArgs = new TableAdapterEventArgs();
if (OnBeforeUpdate != null)
{
OnBeforeUpdate(this, eventArgs);
}
var command = GetUpdateCommand();
Connection.SqlServerExecute(command.Item1, command.Item2);
eventArgs = new TableAdapterEventArgs();
if (OnAfterUpdate != null)
{
eventArgs.Keys = KeyFields;
OnAfterUpdate(this, eventArgs);
}
trans.Complete();
}
}
/// <summary>
/// Executa a atualização do código da SP
/// </summary>
/// <param name="pText">Procedire</param>
/// <param name="pCompanyDb">Nome do Banco de dados da empresa</param>
private static void Exec(string pText, string pCompanyDb)
{
pCompanyDb.CheckForArgumentNull("pCompanyDb");
var conn = Connection.Instance;
var pCurrentDb = conn.ConnectionParameter.Database;
try
{
using (var trans = TransManager.GetTrans())
{
conn.ConnectionParameter.Database = pCompanyDb;
pText = pText.Replace("CREATE proc", "ALTER proc");
conn.SqlServerExecute(pText);
trans.Complete();
}
}
finally
{
conn.ConnectionParameter.Database = pCurrentDb;
}
}
/// <summary>
/// Executa um comando no banco de dados e retorna o resultado.
/// </summary>
/// <param name="pCommand">Um comando sql que retorna resultado</param>
/// <param name="parameters">Parametros da Query</param>
/// <returns>Um DataTable com o resultado da query</returns>
public DataTable SqlServerQuery(string pCommand, IEnumerable <TableAdapterField> parameters = null)
{
using (var trans = TransManager.GetTrans())
{ var dbConn = trans.Connection;
ChangeDatabaseIfNeeded(dbConn);
using (var command = dbConn.CreateCommand())
{
command.Transaction = trans.Transaction;
command.CommandText = pCommand;
command.CommandTimeout = 0;
SetParameters(command, parameters);
using (var dataReader = command.ExecuteReader())
{
var dataTable = new DataTable();
dataTable.Load(dataReader);
foreach (DataColumn dataColun in dataTable.Columns)
{
dataColun.ReadOnly = false;
}
trans.Complete();
return(dataTable);
}
} }
}
/// <summary>
/// Tenta uma conexão com o banco de dados
/// </summary>
/// <returns>True de conectar, false se não conectar</returns>
public bool Connect()
{
using (var trans = TransManager.GetTrans())
{
var dbConn = trans.Connection;
var isOpened = dbConn.State == ConnectionState.Open;
trans.Complete();
return(isOpened);
}
}
/// <summary>
/// Executa um comando SQl na conexão atual
/// </summary>
/// <param name="pCommand">Um sting com o comando SQl</param>
/// <param name="parameters">Parametros</param>
/// <returns>True se for executado com sucesso</returns>
public void SqlServerExecute(string pCommand, IEnumerable <TableAdapterField> parameters = null)
{
using (var trans = TransManager.GetTrans())
{
var dbConn = trans.Connection;
ChangeDatabaseIfNeeded(dbConn);
ExecuteCommnad(pCommand, parameters, dbConn, trans);
trans.Complete();
}
}
/// <summary>
/// Executa um comando scalar no banco de dados e retorna o resultado.
/// </summary>
/// <typeparam name="TT">O tipo do resultado</typeparam>
/// <param name="pCommand">Um comando sql que retorna resultado</param>
/// <param name="parameters"></param>
/// <returns>o valor da consulta</returns>
public TT SqlExecuteScalar <TT>(string pCommand, IEnumerable <TableAdapterField> parameters = null)
{
using (var trans = TransManager.GetTrans())
{
var dbConn = trans.Connection;
ChangeDatabaseIfNeeded(dbConn);
var value = ExecuteScalar <TT>(pCommand, parameters, dbConn, trans);
trans.Complete();
return(value);
}
}
/// <summary>
/// Cria o banco de dados
/// </summary>
public void Create()
{
if (OnBeforeCreate != null)
{
OnBeforeCreate(this, new DBEventArgs());
}
if (!Exists())
{
var query = new StringBuilder();
query.AppendFormat("CREATE DATABASE {0} \n", DataBaseName);
query.AppendFormat("COLLATE {0} \n;", DefaultCollate);
query.AppendFormat("ALTER DATABASE [{0}] SET RECOVERY SIMPLE WITH NO_WAIT;", DataBaseName);
query.AppendFormat("ALTER DATABASE [{0}] SET READ_COMMITTED_SNAPSHOT ON;", DataBaseName);
Connection.Instance.ConnectionParameter.Database = StrDbMater;
using (var trans = TransManager.GetTrans(withTransaction: false))
{
Connection.Instance.SqlServerExecute(query.ToString());
trans.Complete();
}
}
Connection.Instance.ConnectionParameter.Database = DataBaseName;
using (var trans = TransManager.GetTrans())
{
LoadObjects();
CreateTables(Tables);
CreateProcedures(StoredProcedures);
AddVersionToDb(Assembly.GetName().Version, new AppVersion {
DBName = DataBaseName
});
UpdateInitialValues();
if (OnAfterCreate != null)
{
OnAfterCreate(this, new DBEventArgs());
}
trans.Complete();
}
}
/// <summary>
/// Executa um command e retorna o último valor adicionado
/// </summary>
/// <param name="command">Comando</param>
/// <param name="parameters">Parametros do Comando</param>
/// <returns>Ultimo Id Inserido</returns>
public long SqlServerExecuteAndGetLastInsertId(string command, List <TableAdapterField> parameters = null)
{
using (var trans = TransManager.GetTrans())
{
var dbConn = trans.Connection;
ChangeDatabaseIfNeeded(dbConn);
var commnadAndLastInsertId = string.Format("{0};{1}", command, GetGetLastInsertId);
var valueResult = ExecuteScalar <long>(commnadAndLastInsertId, parameters, dbConn, trans);
trans.Complete();
return(valueResult);
}
}
/// <summary>
/// Get new manager
/// </summary>
/// <returns></returns>
public static TransManager GetTrans(string conectionName = "default", bool withTransaction = true)
{
lock (LockObject)
{
TransManager mgr;
if (!Managers.TryGetValue(conectionName, out mgr))
{
mgr = new TransManager(conectionName, withTransaction);
Managers.Add(conectionName, mgr);
}
mgr.AddRef();
return(mgr);
}
}
/// <summary>
/// Salva a coleção
/// </summary>
/// <typeparam name="TT">O tipo do TableAdapter</typeparam>
/// <param name="tableAdaptes">Uma lista de Coleções</param>
/// <returns>true se salvar</returns>
public void SaveLines <TT>(IEnumerable <TT> tableAdaptes) where TT : TableAdapter
{
if (tableAdaptes == null)
{
throw new ArgumentNullException("tableAdaptes");
}
using (var trans = TransManager.GetTrans())
{
foreach (var tableAdpter in tableAdaptes)
{
tableAdpter.SetConnection(Connection);
if (string.IsNullOrEmpty(tableAdpter.DBName))
{
tableAdpter.DBName = DBName;
}
switch (tableAdpter.StateRecord)
{
case eState.eAdd:
tableAdpter.Add();
break;
case eState.eUpdate:
tableAdpter.Update();
break;
case eState.eRemove:
tableAdpter.Remove();
break;
}
}
trans.Complete();
}
}
/// <summary>
/// Pega o registro pelas chaves
/// </summary>
/// <returns>True se encontrar</returns>
public virtual bool GetByKey()
{
using (var trans = TransManager.GetTrans())
{
var eventArgs = new TableAdapterEventArgs();
if (OnBeforeSelect != null)
{
OnBeforeSelect(this, eventArgs);
}
var command = GetSelectCommand();
var data = Connection.SqlServerQuery(command.Item1, command.Item2);
var foundRecord = data.Rows.Count > 0;
if (foundRecord)
{
eventArgs = new TableAdapterEventArgs
{
Keys = KeyFields
};
FillFields(data.Rows[0], true);
StateRecord = eState.eDatabase;
if (OnAfterSelect != null)
{
OnAfterSelect(this, eventArgs);
}
}
trans.Complete();
return(foundRecord);
}
}
private static TT ExecuteScalar <TT>(string commnad, IEnumerable <TableAdapterField> parameters, IDbConnection dbConn, TransManager trans)
{
using (var command = dbConn.CreateCommand())
{
command.Transaction = trans.Transaction;
command.CommandText = commnad;
command.CommandTimeout = 0;
SetParameters(command, parameters);
return(command.ExecuteScalar().To <TT>());
}
}
private static void ExecuteCommnad(string pCommand, IEnumerable <TableAdapterField> parameters, IDbConnection dbConn, TransManager trans)
{
using (var command = dbConn.CreateCommand())
{
command.Transaction = trans.Transaction;
command.CommandText = pCommand;
command.CommandTimeout = 0;
SetParameters(command, parameters);
command.ExecuteNonQuery();
}
}
