public DataTable GetCircles(ParametrsForView p)
{
using (DataSet ds = new DataSet())
{
using (var cmd = new SqlCommand(@"
select f.id,c.Id as CircleId, Name, Radius from Figure f
join Circle c on
f.Id_Circle= c.Id
where Name like '%'+ @PartOfName+'%'
ORDER BY id
OFFSET @offset ROWS
FETCH NEXT @amount ROWS ONLY "
, _connection))
{
cmd.Parameters.Add("@PartOfName", SqlDbType.VarChar);
cmd.Parameters["@PartOfName"].Value = p.PartOfName;
cmd.Parameters.Add("@amount", SqlDbType.Int);
cmd.Parameters["@amount"].Value = p.Amount;
cmd.Parameters.Add("@offset", SqlDbType.Int);
cmd.Parameters["@offset"].Value = p.OffSet;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
//throw new Exception();
da.Fill(ds);
return(ds.Tables[0]);
}
}
}
}
public DataTable GetSquares(ParametrsForView p)
{
using (DataSet ds = new DataSet())
{
using (var cmd = new SqlCommand(@"
select f.id,s.Id as SquareId, Name, Side from Figure f
join Square s on
f.Id_Square = s.Id
ORDER BY id
OFFSET @offset ROWS
FETCH NEXT @amount ROWS ONLY "
, _connection))
{
cmd.Parameters.Add("@amount", SqlDbType.Int);
cmd.Parameters["@amount"].Value = p.Amount;
cmd.Parameters.Add("@offset", SqlDbType.Int);
cmd.Parameters["@offset"].Value = p.OffSet;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
return(ds.Tables[0]);
}
}
}
}
public int GetCirclesCount(ParametrsForView p)
{
_connection.Open();
using (var cmd = new SqlCommand(@"
select count( f.id) from Figure f
join Circle c on
f.Id_Circle= c.Id
where Name like '%'+ @PartOfName+'%'"
, _connection))
{
cmd.Parameters.Add("@PartOfName", SqlDbType.VarChar);
cmd.Parameters["@PartOfName"].Value = p.PartOfName;
return((int)cmd.ExecuteScalar());
}
}
public int GetSquaresCount(ParametrsForView p)
{
_connection.Open();
using (var cmd = new SqlCommand(@"
select count( f.id) from Figure f
join Square c on
f.Id_Square= c.Id"
, _connection))
{
// cmd.Parameters.Add("@amount", SqlDbType.Int);
// cmd.Parameters["@amount"].Value = p.Amount;
// cmd.Parameters.Add("@offset", SqlDbType.Int);
// cmd.Parameters["@offset"].Value = p.OffSet;
//throw new Exception();
return((int)cmd.ExecuteScalar());
}
}
public ActionResult Circles(ParametrsForView parametrs)//+
{
try
{
List <CirlceView> cirlces = new List <CirlceView>();
ParametrsForViewBll pBll = new ParametrsForViewBll()
{
Page = parametrs.Page,
PageSize = Parametrs.PageSize,
PartOfName = parametrs.PartOfName ?? ""
};
int totalItems = _figuresService.GetCirclesCount(pBll);
bool isPAgeActual = ((parametrs.Page - 1) * Parametrs.PageSize < totalItems);
int updatedPage = isPAgeActual ? parametrs.Page : 1;
pBll.Page = updatedPage;
foreach (var item in _figuresService.GetAllCirlces(pBll))
{
cirlces.Add(new CirlceView()
{
Id = item.Id, Name = item.Name, Radius = item.Radius
});
}
PagingInfoView pagingInfo = new PagingInfoView
{
CurrentPage = pBll.Page,
ItemsPerPage = Parametrs.PageSize,
TotalItems = totalItems
};
CirlcesView c = new CirlcesView()
{
Cirlces = cirlces,
PagingInfo = pagingInfo,
};
return(View(c));
}
catch (Exception ex)
{
return(HttpNotFound(ex.Message));
}
}
public ActionResult Circles()//+
{
try
{
ParametrsForView p = new ParametrsForView();
List <CirlceView> cirlces = new List <CirlceView>();
ParametrsForViewBll pBll = new ParametrsForViewBll()
{
Page = p.Page,
PageSize = Parametrs.PageSize,
PartOfName = p.PartOfName
};
foreach (var item in _figuresService.GetAllCirlces(pBll))
{
cirlces.Add(new CirlceView()
{
Id = item.Id, Name = item.Name, Radius = item.Radius
});
}
PagingInfoView pagingInfo = new PagingInfoView
{
CurrentPage = p.Page,
ItemsPerPage = Parametrs.PageSize,
TotalItems = _figuresService.GetCirclesCount(pBll)
};
CirlcesView c = new CirlcesView()
{
Cirlces = cirlces,
PagingInfo = pagingInfo,
};
return(View(c));
}
catch (Exception ex)
{
return(HttpNotFound(ex.Message));
}
}