We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Hi,
Is there a way to populate an MVC grid with inline sql?
Here is my controller code:
public IEnumerable WeeklyTotals(string day){
DateTime getDay = GetDate(day);
string query = "select pt.ID, pt.Description as 'PatientType' "
+ "from OfficeVisits ov "
+ "join PatientTypes pt on pt.id = ov.PatientTypeID "
+ " where VisitDate between '6/12/2017' and '6/16/2017'"
+ " group by pt.ID, pt.Description";
var data = adc.Database.SqlQuery<WeeklyTotalsViewModel>(query);
IEnumerable result = data;
return result;
}
Here is my View:
<div>
@(Html.EJ().Grid<ShoNuffModelClass.WeeklyTotals>("WeeklyTotals")
.Datasource(ds => ds.URL("Report/WeeklyTotals").Adaptor(AdaptorType.UrlAdaptor))
.EnableAltRow()
.ScrollSettings(col => { col.Width(200).Height(275); })
.Columns(col =>
{
col.Field("ID").HeaderText("ID").Width("50px").Visible(false).IsPrimaryKey(true).DefaultValue(5).Add();
col.Field("PatientType").HeaderText("Pat Type").Width("55px").IsPrimaryKey(true).Add();
})
)
</div>
Setting a breakpoint in my controller shows that the query returns the correct results but my view just shows an empty grid saying that No records to display.
Thanks.
|
@(Html.EJ().Grid<object>("FlatGrid")
.Datasource(ds => ds.URL(@Url.Action("DataSource"))
.Adaptor(AdaptorType.UrlAdaptor))
. . .
.Columns(col =>
{
. . .
})
)
@controller
public ActionResult DataSource(Syncfusion.JavaScript.DataManager dm)
{
int count = 0;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string JSONString = null;
string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString();
using (SqlConnection cn = new SqlConnection(connectionStr))
{
cn.Open();
//Getting the records based on skip and top value.
SqlCommand cmd = new SqlCommand("SELECT OrderID,CustomerID,EmployeeID,Freight,OrderDate FROM Orders", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
dt = (DataTable)ds.Tables[0];
JSONString = JsonConvert.SerializeObject(dt, new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
Formatting = Formatting.Indented
});
}
IEnumerable Data = (from DataRow row in dt.Rows
select new Orders
{
OrderID = Convert.ToInt32(row["OrderID"]),
CustomerID = row["CustomerID"].ToString(),
Freight = Convert.ToDecimal(row["Freight"]),
EmployeeID = Convert.ToInt32(row["EmployeeID"]),
OrderDate = Convert.ToDateTime(row["OrderDate"])
}).ToList();
Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations();
if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting
{
Data = operation.PerformSorting(Data, dm.Sorted);
}
if (dm.Where != null && dm.Where.Count > 0) //Filtering
{
Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Operator);
}
count = Data.AsQueryable().Count();
if (dm.Skip != 0)
{
Data = operation.PerformSkip(Data, dm.Skip);
}
if (dm.Take != 0)
{
Data = operation.PerformTake(Data, dm.Take);
}
return Json(new { result = Data, count = count }, JsonRequestBehavior.AllowGet); //result - records, count-total records from database
} |
Thanks for this.
Would it be simpler to not use the URL adapter? I don't need it as the grid is just going to be returning data and will be read only. Would it be simpler if I used the standard binding? Do you have an example of that?
|
@Grid
@(Html.EJ().Grid<object>("FlatGrid")
.Datasource((IEnumerable<object>)ViewBag.datasource)
.AllowPaging()
. . .
.Columns(col =>
{
. . .
})
)
@controller
public ActionResult Index()
{
int count = 0;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string JSONString = null;
string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString();
using (SqlConnection cn = new SqlConnection(connectionStr))
{
cn.Open();
//Getting the records based on skip and top value.
SqlCommand cmd = new SqlCommand("SELECT OrderID,CustomerID,EmployeeID,Freight,OrderDate FROM Orders", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
dt = (DataTable)ds.Tables[0];
. . .
IEnumerable Data = (from DataRow row in dt.Rows
select new Orders
{
OrderID = Convert.ToInt32(row["OrderID"]),
CustomerID = row["CustomerID"].ToString(),
Freight = Convert.ToDecimal(row["Freight"]),
EmployeeID = Convert.ToInt32(row["EmployeeID"]),
OrderDate = Convert.ToDateTime(row["OrderDate"])
}).ToList();
ViewBag.datasource = Data;
return View();
} |
