/// <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 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> /// 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> /// 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> /// 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> /// 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); } }