Hynex Mendoza Toledo Hynex Mendoza Toledo - 2 months ago 11
C# Question

Transaction accept null, and don't rollback

I'll try to be so specific.

I'm reading a CSV file into

DataGridView
, the user can see the information if the user validates the information, he sends to an SQL Server database.



I'm using a stored procedure transaction with parameters to send the information.

Called from Visual Studio:

using (SqlConnection conn = new SqlConnection(CadenaConexionBD.cs)) {
conn.Open();

for (int i = 0; i < dgvTrabajadores.Rows.Count - 1; i++){
using (SqlCommand cmd = new SqlCommand("SistemaFAIFAP.cargaTransTrabajadores", conn)){
cmd.CommandType = CommandType.StoredProcedure;
guid = Guid.NewGuid();
string stringGuid = guid.ToString();

cmd.Parameters.AddWithValue("@id", stringGuid);
cmd.Parameters.AddWithValue("@ap", dgvTrabajadores.Rows[i].Cells["APELLIDOPATERNO"].Value.ToString());
cmd.Parameters.AddWithValue("@am", dgvTrabajadores.Rows[i].Cells["APELLIDOMATERNO"].Value.ToString());
cmd.Parameters.AddWithValue("@n", dgvTrabajadores.Rows[i].Cells["NOMBRE"].Value.ToString());
cmd.Parameters.AddWithValue("@fn", DateTime.Parse(dgvTrabajadores.Rows[i].Cells["FECHANACIMIENTO"].Value.ToString()));
cmd.Parameters.AddWithValue("@pn", dgvTrabajadores.Rows[i].Cells["PAISNACIMIENTO"].Value.ToString());
cmd.Parameters.AddWithValue("@en", dgvTrabajadores.Rows[i].Cells["ENTIDADNACIMIENTO"].Value.ToString());
cmd.Parameters.AddWithValue("@nn", dgvTrabajadores.Rows[i].Cells["NombreNacionalidad"].Value.ToString());
cmd.Parameters.AddWithValue("@nss", dgvTrabajadores.Rows[i].Cells["NSS"].Value.ToString());
cmd.Parameters.AddWithValue("@rfc", dgvTrabajadores.Rows[i].Cells["RFC"].Value.ToString());
cmd.Parameters.AddWithValue("@hom", dgvTrabajadores.Rows[i].Cells["HOMONIMIA"].Value.ToString());
cmd.Parameters.AddWithValue("@curp", dgvTrabajadores.Rows[i].Cells["CURP"].Value.ToString());
cmd.Parameters.AddWithValue("@ec", dgvTrabajadores.Rows[i].Cells["ESTADOCIVIL"].Value.ToString());
cmd.Parameters.AddWithValue("@sex", dgvTrabajadores.Rows[i].Cells["SEXO"].Value.ToString());
cmd.Parameters.AddWithValue("@ce", dgvTrabajadores.Rows[i].Cells["CORREOELECTRONICO"].Value.ToString());
cmd.Parameters.AddWithValue("@cat", dgvTrabajadores.Rows[i].Cells["CATEGORIA"].Value.ToString());
cmd.Parameters.AddWithValue("@dc", dgvTrabajadores.Rows[i].Cells["DESCRIPCIONCATEGORIA"].Value.ToString());
cmd.Parameters.AddWithValue("@calle", dgvTrabajadores.Rows[i].Cells["CALLE"].Value.ToString());
cmd.Parameters.AddWithValue("@num", dgvTrabajadores.Rows[i].Cells["NUMEROEXTERIOR"].Value.ToString());
cmd.Parameters.AddWithValue("@col", dgvTrabajadores.Rows[i].Cells["COLONIA"].Value.ToString());
cmd.Parameters.AddWithValue("@mun", dgvTrabajadores.Rows[i].Cells["MUNICIPIO"].Value.ToString());
cmd.Parameters.AddWithValue("@ent", dgvTrabajadores.Rows[i].Cells["ENTIDAD"].Value.ToString());
cmd.Parameters.AddWithValue("@tel", dgvTrabajadores.Rows[i].Cells["TELEFONO"].Value.ToString());
cmd.Parameters.AddWithValue("@dep", dgvTrabajadores.Rows[i].Cells["DEPENDENCIA"].Value.ToString());
cmd.Parameters.AddWithValue("@subdep", dgvTrabajadores.Rows[i].Cells["SUBDEPENDENCIA"].Value.ToString());
cmd.Parameters.AddWithValue("@dir", dgvTrabajadores.Rows[i].Cells["DIRECCION"].Value.ToString());
cmd.Parameters.AddWithValue("@depa", dgvTrabajadores.Rows[i].Cells["DEPARTAMENTO"].Value.ToString());
cmd.Parameters.AddWithValue("@ofi", dgvTrabajadores.Rows[i].Cells["OFICINA"].Value.ToString());
cmd.Parameters.AddWithValue("@npl", dgvTrabajadores.Rows[i].Cells["NUMEROPLAZA"].Value.ToString());
cmd.Parameters.AddWithValue("@cvep", dgvTrabajadores.Rows[i].Cells["CLAVEPROGRAMATICA"].Value.ToString());
cmd.Parameters.AddWithValue("@fig", DateTime.Parse(dgvTrabajadores.Rows[i].Cells["FECHAINGRESOGOBIERNO"].Value.ToString()));
cmd.Parameters.AddWithValue("@nc", dgvTrabajadores.Rows[i].Cells["NUMEROCONTROL"].Value.ToString());
cmd.Parameters.AddWithValue("@cvee", dgvTrabajadores.Rows[i].Cells["CLAVEEMPLEADO"].Value.ToString());
cmd.Parameters.AddWithValue("@ior", dgvTrabajadores.Rows[i].Cells["IDOFICINARECAUDADORA"].Value.ToString());
cmd.Parameters.AddWithValue("@fum", DateTime.Parse(dgvTrabajadores.Rows[i].Cells["FECULTMOV"].Value.ToString()));

filasAfectadas = cmd.ExecuteNonQuery();
}
}
if (filasAfectadas <= 0){
MessageBox.Show("Los datos no se cargaron de manera correcta. \nContacte al Administrador del Sistema.", "Error al cargar la base de datos", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
}else{
MessageBox.Show("Se cargó correctamente la información", "Datos agregados", MessageBoxButtons.OK, MessageBoxIcon.Information);
validacion = 1;
conn.Close();
}
}


My store in the database:

SECOND EDITION TO A DevCod SOLUTION

ALTER PROCEDURE [SistemaFAIFAP].cargaTransTrabajadores
@id uniqueidentifier, @ap NVARCHAR (64), @am NVARCHAR (64), @n NVARCHAR (64) = NULL,
@fn DATE, @pn NVARCHAR (50), @en NVARCHAR (50), @nn NVARCHAR (50),
@nss NVARCHAR (11), @rfc NVARCHAR (10), @hom NVARCHAR (3), @curp NVARCHAR (18),
@ec NVARCHAR (50), @sex NVARCHAR (50), @ce NVARCHAR (50), @cat NVARCHAR (6),
@dc NVARCHAR (64), @calle NVARCHAR (255), @num NVARCHAR (10), @col NVARCHAR (60),
@mun NVARCHAR (50), @ent NVARCHAR (50), @tel NVARCHAR (10), @dep NVARCHAR (3),
@subdep NVARCHAR (2), @dir NVARCHAR (3), @depa NVARCHAR (3), @ofi NVARCHAR (4),
@npl NVARCHAR (4), @cvep NVARCHAR (6), @fig DATE, @nc NVARCHAR (64), @cvee NVARCHAR (64),
@ior FLOAT, @fum
DATE AS
BEGIN TRY
BEGIN TRANSACTION
IF(ISNULL(@n,'')='' OR @n = NULL OR @n = '')
BEGIN
RAISERROR ('@n is null or empty. This is not allowed. Insert will be rollbacked',-- Message text to return to the UI.
16, -- Severity.
1 -- State
)
END
INSERT INTO sistemaFAIFAP.TrabajadorBuffer
VALUES(
@id, @ap, @am, @n, @fn,
@pn, @en, @nn, @nss, @rfc,
@hom, @curp, @ec, @sex, @ce,
@cat, @dc, @calle, @num, @col,
@mun, @ent, @tel, @dep, @subdep,
@dir, @depa, @ofi, @npl, @cvep,
@fig, @nc, @cvee, @ior, @fum
)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
END CATCH


As you can see in the pic, I put a
null
field as test.

I have my table defined as
Not Null
in field
Nombre
, but it doesn't show an error message, and the information is inserted in the table.

asd

How can I prevent sending empty records?

(It's my first time using transaction) Why doesn't my stored procedure show an error message and proceeds as if the field
Nombre
has a value?

Answer
  1. First of all, dgvTrabajadores.Rows.Count Should not contain data that cannot be saved. Add UI validation to the Grid using Javascript, .net validators. Prompt the User, "Column cannot be null".
  2. Place one try catch just for the "for loop". In catch block, consolidate all error message in string format and display it at the end.

Ex;

string ErrorMsg = "";
for (int i = 0; i < dgvTrabajadores.Rows.Count - 1; i++){
try
{
  cmd.SavingOneRowAtOneTime()
}
catch(Exception ex)
{
  //Example, change it as you need.
  ErrorMsg += (ErrorMsg == "") ?  "Insert failed for Rows: " + i.ToString() : "," + i.ToString();
}
}
//At the end
if(ErrorMsg != "")
 MessageBox.Show(ErrorMsg);
}

Please mark this as answer, if it is an acceptable answer for you. Thanks!