public async Task <ExeRslt> Update(int id, IssuModel Issu) { string lxQry = "UPDATE [Issu] " + " SET " + " Tit = @Tit," + " Txt = @Txt," + " St = @St," + " StmMdf = GetDate() " + " WHERE IssuId = @IssuId"; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { using (SqlCommand cmd = new SqlCommand(lxQry, cnx)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Tit", Issu.Tit); cmd.Parameters.AddWithValue("@Txt", Issu.Txt); cmd.Parameters.AddWithValue("@St", Issu.St); cmd.Parameters.AddWithValue("@IssuId", id); await cnx.OpenAsync(); await cmd.ExecuteNonQueryAsync(); return(new ExeRslt(Issu.IssuId)); } } } catch (Exception ex) { ex.Data.Add("Qry", lxQry); ex.Data.Add("Method", Ext.GetCaller()); return(new ExeRslt(-1, ex)); } }
public async Task <ExeRslt> Delete(int id) { SqlTransaction trns = null; string lxQry = "DELETE [Lbl] WHERE LblId = @LblId"; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { await cnx.OpenAsync(); trns = cnx.BeginTransaction(); using (SqlCommand cmd = new SqlCommand(lxQry, cnx, trns)) { cmd.Parameters.AddWithValue("@LblId", id); int rows = await cmd.ExecuteNonQueryAsync(); trns.Commit(); return(new ExeRslt(rows)); } } } catch (Exception ex) { try { trns.Rollback(); } catch (Exception exr) { ex.Data.Add("Rollback", exr.Message); } ex.Data.Add("Qry", lxQry); ex.Data.Add("Method", Ext.GetCaller()); return(new ExeRslt(-1, ex)); } }
public async Task <LstRslt <LblModel> > GetAllLbl() { DataTable lxDT = new DataTable(); string lxQry = "SELECT LblId, Lbl, BkClr, Clr " + " FROM [Lbl]"; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { using (SqlCommand cmd = new SqlCommand(lxQry, cnx)) { SqlDataAdapter lxDA = new SqlDataAdapter(cmd); lxDA.Fill(lxDT); lxDT.TableName = "Lbl"; } } if (lxDT.Rows.Count > 0) { List <LblModel> lxLbls = lxDT.ToList <LblModel>(); var lxRslt = new LstRslt <LblModel>(lxLbls); return(await Task.FromResult(lxRslt)); } else { return(new LstRslt <LblModel>(new List <LblModel>())); } } catch (Exception ex) { ex.Data.Add("Qry", lxQry); ex.Data.Add("Method", Ext.GetCaller()); return(new LstRslt <LblModel>(new List <LblModel>(), ex)); } }
public async Task <SnglRslt <IssuModel> > Get(int id) { DataSet lxDS = new DataSet(); string lxQryI = "SELECT IssuId, Tit, Txt, St, StmCre, StmMdf " + " FROM [Issu] I" + " WHERE IssuId = @IssuId"; string lxQryLxI = "SELECT LxI.IssuId, LxI.LblId, L.Lbl, L.BkClr, L.Clr " + " FROM [LblxIssu] LxI " + " LEFT JOIN [Lbl] L " + " ON L.LblId = LxI.LblId " + "WHERE IssuId = @IssuId"; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { using (SqlCommand cmd = new SqlCommand(lxQryI, cnx)) { cmd.Parameters.AddWithValue("@IssuId", id); SqlDataAdapter lxDA = new SqlDataAdapter(cmd); lxDA.Fill(lxDS, "Issu"); } using (SqlCommand cmd = new SqlCommand(lxQryLxI, cnx)) { cmd.Parameters.AddWithValue("@IssuId", id); SqlDataAdapter lxDA = new SqlDataAdapter(cmd); lxDA.Fill(lxDS, "LblxIssu"); } DataColumn lxColIssuIdM = lxDS.Tables["Issu"].Columns["IssuId"]; DataColumn lxColIssuIdD = lxDS.Tables["LblxIssu"].Columns["IssuId"]; DataRelation lxI_LxI = new DataRelation("Issu_LblxIssu", lxColIssuIdM, lxColIssuIdD); lxDS.Relations.Add(lxI_LxI); } var lxRslt = new SnglRslt <IssuModel>(); if (lxDS.Tables["Issu"].Rows.Count > 0) { IssuModel lxIssu = lxDS.Tables["Issu"].Rows[0].ToRow <IssuModel>(); FillLbl(ref lxIssu, lxDS.Tables["LblxIssu"]); lxRslt = new SnglRslt <IssuModel>(lxIssu); } return(await Task.FromResult(lxRslt)); } catch (Exception ex) { ex.Data.Add("QryI", lxQryI); ex.Data.Add("QryLxI", lxQryLxI); ex.Data.Add("Method", Ext.GetCaller()); return(new SnglRslt <IssuModel>(new IssuModel(), ex)); } }
public async Task <LstRslt <IssuModel> > GetAll() { DataSet lxDS = new DataSet(); string lxQryI = "SELECT IssuId, Tit, Txt, St, StmCre, StmMdf " + " FROM [Issu] I" + " ORDER BY StmMdf DESC"; string lxQryLxI = "SELECT LxI.IssuId, LxI.LblId, L.Lbl, L.BkClr, L.Clr " + " FROM [LblxIssu] LxI " + " LEFT JOIN [Lbl] L " + " ON L.LblId = LxI.LblId"; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { using (SqlCommand cmd = new SqlCommand(lxQryI, cnx)) { SqlDataAdapter lxDA = new SqlDataAdapter(cmd); lxDA.Fill(lxDS, "Issu"); } using (SqlCommand cmd = new SqlCommand(lxQryLxI, cnx)) { SqlDataAdapter lxDA = new SqlDataAdapter(cmd); lxDA.Fill(lxDS, "LblxIssu"); } DataColumn lxColIssuIdM = lxDS.Tables["Issu"].Columns["IssuId"]; DataColumn lxColIssuIdD = lxDS.Tables["LblxIssu"].Columns["IssuId"]; DataRelation lxI_LxI = new DataRelation("Issu_LblxIssu", lxColIssuIdM, lxColIssuIdD); lxDS.Relations.Add(lxI_LxI); } var lxRslt = new LstRslt <IssuModel>(); if (lxDS.Tables["Issu"].Rows.Count > 0) { List <IssuModel> lxIssus = lxDS.Tables["Issu"].ToList <IssuModel>(); FillLbls(ref lxIssus, lxDS); lxRslt = new LstRslt <IssuModel>(lxIssus); } return(await Task.FromResult(lxRslt)); } catch (Exception ex) { ex.Data.Add("QryI", lxQryI); ex.Data.Add("QryLxI", lxQryLxI); ex.Data.Add("Method", Ext.GetCaller()); return(new LstRslt <IssuModel>(new List <IssuModel>(), ex)); } }
public async Task <ExeRslt> Create(LblModel Lbl) { SqlTransaction trns = null; string lxQry = "INSERT INTO [Lbl] " + "(Lbl, BkClr, Clr) " + "OUTPUT Inserted.LblId " + "VALUES " + "(@Lbl, @BkClr, @Clr) "; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { await cnx.OpenAsync(); trns = cnx.BeginTransaction(); using (SqlCommand cmd = new SqlCommand(lxQry, cnx, trns)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Lbl", Lbl.Lbl); cmd.Parameters.AddWithValue("@BkClr", Lbl.BkClr); cmd.Parameters.AddWithValue("@Clr", Lbl.Clr); var id = await cmd.ExecuteScalarAsync(); Lbl.LblId = Convert.ToInt32(id); trns.Commit(); return(new ExeRslt(Lbl.LblId)); } } } catch (Exception ex) { try { trns.Rollback(); } catch (Exception exr) { ex.Data.Add("Rollback", exr.Message); } ex.Data.Add("Qry", lxQry); ex.Data.Add("Method", Ext.GetCaller()); return(new ExeRslt(-1, ex)); } }
public async Task <ExeRslt> Update(int id, LblModel Lbl) { SqlTransaction trns = null; string lxQry = "UPDATE [Lbl] " + " SET " + " Lbl = @Lbl," + " BkClr = @BkClr, " + " Clr = @Clr " + " WHERE LblId = @LblId"; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { await cnx.OpenAsync(); trns = cnx.BeginTransaction(); using (SqlCommand cmd = new SqlCommand(lxQry, cnx, trns)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Lbl", Lbl.Lbl); cmd.Parameters.AddWithValue("@BkClr", Lbl.BkClr); cmd.Parameters.AddWithValue("@Clr", Lbl.Clr); cmd.Parameters.AddWithValue("@LblId", id); await cmd.ExecuteNonQueryAsync(); trns.Commit(); return(new ExeRslt(Lbl.LblId)); } } } catch (Exception ex) { try { trns.Rollback(); } catch (Exception exr) { ex.Data.Add("Rollback", exr.Message); } ex.Data.Add("Qry", lxQry); ex.Data.Add("Method", Ext.GetCaller()); return(new ExeRslt(-1, ex)); } }
public async Task <ExeRslt> Delete(int id) { string lxQry = "DELETE [Issu] WHERE IssuId = @Id"; //TODO Borrar Labels asignados try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { using (SqlCommand cmd = new SqlCommand(lxQry, cnx)) { cmd.Parameters.AddWithValue("@IsuuId", id); cnx.Open(); int rows = await cmd.ExecuteNonQueryAsync(); return(new ExeRslt(rows)); } } } catch (Exception ex) { ex.Data.Add("Qry", lxQry); ex.Data.Add("Method", Ext.GetCaller()); return(new ExeRslt(-1, ex)); } }
public async Task <ExeRslt> Create(IssuModel Issu) { SqlTransaction trns = null; string lxQry = "INSERT INTO [Issu] " + "(Tit, Txt, St, StmCre, StmMdf) " + "OUTPUT Inserted.IssuId " + "VALUES " + "(@Tit, @Txt, @St, GetDate(), GetDate()) "; string lxQryLbl = "INSERT INTO [LblxIssu] " + "(IssuId, LblId) " + "VALUES " + "(@IssuId, @LblId)"; try { using (SqlConnection cnx = new SqlConnection(GetCnxStr())) { await cnx.OpenAsync(); trns = cnx.BeginTransaction(); using (SqlCommand cmd = new SqlCommand(lxQry, cnx, trns)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Tit", Issu.Tit); cmd.Parameters.AddWithValue("@Txt", Issu.Txt); cmd.Parameters.AddWithValue("@St", Issu.St); var id = await cmd.ExecuteScalarAsync(); Issu.IssuId = Convert.ToInt32(id); foreach (var lxLbl in Issu.LstLbl) { using (SqlCommand cmdL = new SqlCommand(lxQryLbl, cnx, trns)) { cmdL.CommandType = CommandType.Text; cmdL.Parameters.AddWithValue("@IssuId", Issu.IssuId); cmdL.Parameters.AddWithValue("@LblId", lxLbl.LblId); await cmdL.ExecuteScalarAsync(); } } trns.Commit(); return(new ExeRslt(Issu.IssuId)); } } } catch (Exception ex) { try { trns.Rollback(); } catch (Exception exr) { ex.Data.Add("Rollback", exr.Message); } ex.Data.Add("Qry", lxQry); ex.Data.Add("Method", Ext.GetCaller()); return(new ExeRslt(-1, ex)); } }