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));
}
}
private void FillLbl(ref IssuModel Issu, DataTable DT)
{
DataRow[] lxRows = DT.Select();
Issu.LstLbl = new List <LblModel>();
foreach (var lxRow in lxRows)
{
var lxLbl = lxRow.ToRow <LblModel>();
Issu.LstLbl.Add(lxLbl);
}
}
public async void OnGet(int id)
{
var lxRslt = await IssuBO.Get(id);
if (lxRslt.EsVld == false)
{
TempData["ExErr"] = lxRslt.Err;
}
Issu = lxRslt.Sngl;
}
public async Task <ActionResult <IssuModel> > Put(int id, [FromBody] IssuModel issu)
{
var lxRslt = await IssuBO.Update(id, issu);
if (lxRslt.Err != null)
{
return(BadRequest(lxRslt));
}
return(CreatedAtAction(nameof(Get), new { id = issu.IssuId }, issu));
}
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 Task <ExeRslt> Update(int id, IssuModel Issu)
{
return(IssuDM.Update(id, Issu));
}
public Task <ExeRslt> Create(IssuModel Issu)
{
return(IssuDM.Create(Issu));
}
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));
}
}