public void ShouldExecuteOnTransactionForMultipleSets() { var sut = new SqlServerConnection("Data Source=.;Initial Catalog=DbSessionTests;Integrated Security=True;"); sut.ExecuteBatchOnTransaction("INSERT INTO TestTable VALUES(@Id, @Value)", new [] { new ParameterSet { new Parameter <int>("Id", 77), new Parameter <int>("Value", 77) }, new ParameterSet { new Parameter <int>("Id", 78), new Parameter <int>("Value", 78) } }); sut.Commit(); using (var connection = new SqlConnection("Data Source=.;Initial Catalog=DbSessionTests;Integrated Security=True;")) { var command = connection.CreateCommand(); command.CommandText = "SELECT TestValue FROM TestTable WHERE Id = 77"; connection.Open(); var result = int.Parse(command.ExecuteScalar().ToString()); Assert.That(result, Is.EqualTo(77)); command.CommandText = "SELECT TestValue FROM TestTable WHERE Id = 78"; result = int.Parse(command.ExecuteScalar().ToString()); Assert.That(result, Is.EqualTo(78)); } }
public Usuario Create(Usuario entity, long codigoCampo) { connection = new SqlServerConnection(); DbTransaction transaction = connection.BeginTransaction(); try { var clave = Encrypt.GetMD5(entity.pass); var parametros = new Dictionary <string, object> { { "@usuario", entity.usuario }, { "@nombre", entity.nombre }, { "@apellido", entity.apellido }, { "@pass", clave }, { "@idRol", entity.idRol }, { "@codigoCampo", codigoCampo } }; entity.idUsuario = connection.Execute("spRegistrarUsuario", parametros, System.Data.CommandType.StoredProcedure, transaction); if (entity.idUsuario == 0) { throw new ArgumentException("Create Usuario Error"); } else if (entity.idUsuario == -1) { throw new ArgumentException("El usuario ya existe para este campo"); } var param = new Dictionary <string, object> { { "@idUsuario", entity.idUsuario }, { "@codigoCampo", codigoCampo } }; var insert = connection.Execute("spRegistrarUsuarioEnCampo", param, System.Data.CommandType.StoredProcedure, transaction); if (insert == 0) { throw new ArgumentException("Create Usuario por Campo Error"); } connection.Commit(transaction); return(entity); } catch (Exception ex) { connection.Rollback(transaction); throw; } finally { connection.Close(); connection = null; transaction = null; } }
/// <summary> /// Archive the message and its parts and optionally archives the message and part properties. /// </summary> /// <param name="expiryMinutes">The amount of time (in minutes) before the archived message expires and will be automatically deleted from the archive.</param> /// <param name="includeProperties">A flag indicating if properties should be archived along with the message.</param> /// <param name="tag">An ArchiveTag to be associated with the archived message.</param> public Task Archive(int expiryMinutes, bool includeProperties, ArchiveTag tag) { return(Task.Factory.StartNew(() => { try { if (tag.ArchiveType.Active) { using (SqlServerConnection connection = new SqlServerConnection("MessageArchive")) { connection.RefreshConfiguration(); connection.Open(); connection.BeginTransaction(); try { ArchiveMessage(expiryMinutes, connection); if (includeProperties) { ArchiveMessageProperties(connection); } ArchiveParts(connection); if (includeProperties) { ArchivePartProperties(connection); } connection.Commit(); } catch (Exception ex) { if (connection.IsOpen()) { connection.Rollback(); } throw ex; } } } } catch (Exception ex) { Logger.WriteError(string.Format("Error archiving a message to database. The message will be writen to archive directory for later archival. \r\n Details: {0}", ex.ToString()), 128); HandleFailedArchiveMessage(); } })); }
public void ShouldExecuteProcedureOnTransaction() { var sut = new SqlServerConnection("Data Source=.;Initial Catalog=DbSessionTests;Integrated Security=True;"); sut.ExecuteOnTransaction( "dbo.TestProc", new ParameterSet { new Parameter <int>("ParOne", 100), new Parameter <int>("ParTwo", 101) }, CommandType.StoredProcedure); sut.Commit(); using (var connection = new SqlConnection("Data Source=.;Initial Catalog=DbSessionTests;Integrated Security=True;")) { var command = connection.CreateCommand(); command.CommandText = "SELECT TestValue FROM TestTable WHERE Id = 100"; connection.Open(); var result = int.Parse(command.ExecuteScalar().ToString()); Assert.That(result, Is.EqualTo(101)); } }
public Evento Update(long id, Evento entity, List <long> lista) { connection = new SqlServerConnection(); DbTransaction transaction = connection.BeginTransaction(); try { if (lista == null) { var param = new Dictionary <string, object> { { "@idEvento", id } }; connection.Execute("spDeleteDetalleXBovino", param, System.Data.CommandType.StoredProcedure, transaction); var delete = connection.Execute("spEliminarEvento", param, System.Data.CommandType.StoredProcedure, transaction); if (delete == 0) { throw new ArgumentException("Delete Evento Error"); } } else { var parametrosEvento = new Dictionary <string, object> { { "@idEvento", id }, { "@idTipoEvento", entity.idTipoEvento }, { "@fechaHora", entity.fechaHora }, { "@cantidad", entity.cantidad } }; switch (entity.idTipoEvento) { case 1: parametrosEvento.Add("@idVacuna", entity.idVacuna); break; case 2: parametrosEvento.Add("@idAntibiotico", entity.idAntibiotico); break; case 3: parametrosEvento.Add("@idCampoDestino", entity.idCampoDestino); parametrosEvento.Add("@idRodeoDestino", entity.idRodeoDestino); break; case 4: parametrosEvento.Add("@idAlimento", entity.idAlimento); break; } var update = connection.Execute("spModificarEvento", parametrosEvento, System.Data.CommandType.StoredProcedure, transaction); if (update == 0) { throw new ArgumentException("Update evento error"); } var parametrosDetalle = new Dictionary <string, object> { { "@idEvento", id } }; connection.Execute("spDeleteDetalleXBovino", parametrosDetalle, System.Data.CommandType.StoredProcedure, transaction); var insertDetalle = 0; var fechaHora = DateTime.Now.ToString("yyyyMMddHHmmss"); parametrosDetalle.Add("@idBovino", 0); parametrosDetalle.Add("@fechaHora", fechaHora); parametrosDetalle.Add("@idTipoEvento", entity.idTipoEvento); switch (entity.idTipoEvento) { case 1: parametrosDetalle.Add("@idVacuna", entity.idVacuna); break; case 3: parametrosDetalle.Add("@idRodeoDestino", entity.idRodeoDestino); break; case 4: parametrosDetalle.Add("@idAlimento", entity.idAlimento); parametrosDetalle.Add("@cantAlimento", entity.cantidad); break; } for (int i = 0; i < lista.Count; i++) { parametrosDetalle["@idBovino"] = lista.ElementAt(i); insertDetalle = connection.Execute("spRegistrarEventosXBovino", parametrosDetalle, System.Data.CommandType.StoredProcedure, transaction); if (insertDetalle == 0) { throw new ArgumentException("Update EventosXBovino Error"); } } //if (entity.idTipoEvento == 3) //{ // var insert = 0; // var parametros = new Dictionary<string, object> // { // {"@idRodeo", entity.idRodeoDestino }, // {"@idBovino", 0 } // }; // for (int i = 0; i < lista.Count; i++) // { // parametros["@idBovino"] = lista.ElementAt(i); // insert = connection.Execute("spActualizarRodeoBovino", parametros, System.Data.CommandType.StoredProcedure, transaction); // if (insert == 0) // throw new ArgumentException("Update rodeo bovino Error"); // } //} } connection.Commit(transaction); return(entity); } catch (Exception ex) { connection.Rollback(transaction); throw; } finally { connection.Close(); connection = null; transaction = null; } }
public Evento Insert(Evento evento, List <long> lista) { connection = new SqlServerConnection(); DbTransaction transaction = connection.BeginTransaction(); try { var parametrosEvento = new Dictionary <string, object> { { "@cant", evento.cantidad }, { "@idTipoEvento", evento.idTipoEvento }, { "@fechaHora", evento.fechaHora } }; switch (evento.idTipoEvento) { case 1: //vacunacion parametrosEvento.Add("@idVacuna", evento.idVacuna); break; case 2: //antibiotico parametrosEvento.Add("@idAntibiotico", evento.idAntibiotico); break; case 3: //manejo parametrosEvento.Add("@codigoCampo", evento.idCampoDestino); parametrosEvento.Add("@idRodeoDestino", evento.idRodeoDestino); break; case 4: //alimenticio parametrosEvento.Add("@idAlimento", evento.idAlimento); break; } evento.idEvento = connection.Execute("spRegistrarEvento", parametrosEvento, System.Data.CommandType.StoredProcedure, transaction); if (evento.idEvento == 0) { throw new ArgumentException("Create Evento Error"); } var parametrosDetalle = new Dictionary <string, object>() { { "@idEvento", evento.idEvento }, { "@idTipoEvento", evento.idTipoEvento }, { "@fechaHora", evento.fechaHora } }; switch (evento.idTipoEvento) { case 1: //vacunacion parametrosDetalle.Add("@idVacuna", evento.idVacuna); break; case 3: //manejo parametrosDetalle.Add("@idRodeoDestino", evento.idRodeoDestino); break; case 4: //alimenticio parametrosDetalle.Add("@idAlimento", evento.idAlimento); break; } var insert = 0; parametrosDetalle.Add("@idBovino", 0); for (int i = 0; i < lista.Count; i++) { parametrosDetalle["@idBovino"] = lista.ElementAt(i); insert = connection.Execute("spRegistrarEventosXBovino", parametrosDetalle, System.Data.CommandType.StoredProcedure, transaction); if (insert == 0) { throw new ArgumentException("Create EventosXBovino Error"); } } //if (evento.idTipoEvento == 3) //{ // insert = 0; // var parametros = new Dictionary<string, object> // { // {"@idRodeo", evento.idRodeoDestino }, // {"@idBovino", 0 } // }; // for (int i = 0; i < lista.Count; i++) // { // parametros["@idBovino"] = lista.ElementAt(i); // insert = connection.Execute("spActualizarRodeoBovino", parametros, System.Data.CommandType.StoredProcedure, transaction); // if (insert == 0) // throw new ArgumentException("Update rodeo bovino Error"); // } //} connection.Commit(transaction); return(evento); } catch (Exception ex) { connection.Rollback(transaction); throw; } finally { connection.Close(); connection = null; transaction = null; } }