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. Image for the cookie policy date

Inline Sql Populate Grid

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.


3 Replies

VA Venkatesh Ayothi Raman Syncfusion Team June 30, 2017 01:53 AM UTC

Hi Richard, 

Thanks for contacting Syncfusion support. 

We have prepared the sample based on your requirement which can be download from following link, 

In this sample, we have rendered the Grid using inline SQL commands as well as bounded the Grid data using URL adaptor. Please refer to the following code example, 

Code example:  
@(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 
 
        } 
 
If we misunderstood your requirement, then could you please provide more details about your requirement? 
 
Regards, 
Venkatesh Ayothiraman. 



RD Richard Dublon June 30, 2017 03:28 PM UTC

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?



VA Venkatesh Ayothi Raman Syncfusion Team July 3, 2017 12:35 PM UTC

Hi Richard, 

Thanks for contacting Syncfusion support. 
 
We have prepared a sample based on your requirement which can be download from following link, 
 
In this sample, we have directly bound the data source using SQL inline comments. Please refer to the following code example, 
Code example
@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(); 
        } 
 
Please let us know if you have any further assistance on this. 
 
Regards, 
Venkatesh Ayothiraman. 


Loader.
Live Chat Icon For mobile
Up arrow icon