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.
Unfortunately, activation email could not send to your email. Please try again.

Inline Sql Populate Grid

Thread ID:

Created:

Updated:

Platform:

Replies:

131212 Jun 28,2017 10:31 AM Jul 3,2017 08:35 AM ASP.NET MVC 3
loading
Tags: Grid
Richard Dublon
Asked On June 28, 2017 10:31 AM

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.


Venkatesh Ayothi Raman [Syncfusion]
Replied On June 29, 2017 09:53 PM

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. 


Richard Dublon
Replied On June 30, 2017 11:28 AM

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?


Venkatesh Ayothi Raman [Syncfusion]
Replied On July 3, 2017 08:35 AM

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. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;