1. Home
  2. Forum
  3. Blazor
  4. problem with Grid and big database

problem with Grid and big database

Hello, I try to make a server-side web-page with a simple data grid using EjsGrid and SQL database. So far tested locally. It works properly up to 10000 record, but when I try to read more then the behavior becomes random, and for 100000 the grid doesn't appear at all and everything is locked. It doesn't matter if I use paging or virtual scroll. Maybe it would help to load only a part of data  for current page. Is there a demo project explaining how to do it?

5 Replies
RS Renjith Singh Rajendran Syncfusion Team February 5, 2020 12:01 PM UTC

Hi Andrzej, 

Thanks for contacting Syncfusion support. 

We believe that bringing large amount of data in-memory is the cause of the slowness and also bounding grid with large amount of data without paging or virtualization will leave the browser hang as it will use more memory. Hence we suggest you to bind data as IQueryable to Grid and use AllowPaging property in Grid to bind large data(100000) from an SQL database, hence now the requested page data will alone be fetched and given to the grid component.  

For your convenience, we have prepared a sample to bind data(for an example we have total 832 records) from an SQL database to Grid. Please download the sample from the link below, 

Please refer the below code and documentation links below, 
 
 
@inject OrderDataAccessLayer db 
 
    <EjsGrid @ref="Grid" DataSource="@Orders" AllowPaging="true"> 
        ... 
   </EjsGrid> 
 
@code{ 
    EjsGrid<Order> Grid { get; set; } 
    IQueryable<Order> Orders; 
    protected override void OnInitialized() 
    { 
        Orders = db.GetAllOrders(); 
    } 
} 

[OrderDataAccessLayer.cs] 

    public class OrderDataAccessLayer 
    { 
        OrderContext db = new OrderContext(); 
 
        //To Get all Orders details    
        public DbSet<Order> GetAllOrders() 
        { 
            try 
            { 
                return db.Orders; 
            } 
            catch 
            { 
                throw; 
            } 
        } 
        ... 
    } 

[OrderContext.cs] 

    public class OrderContext : DbContext 
    { 
        public virtual DbSet<Order> Orders { get; set; } 
 
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
        { 
            if (!optionsBuilder.IsConfigured) 
            { 
                optionsBuilder.UseSqlServer(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='C:\Users\RenjithSinghRajendra\Downloads\EFGrid\EFGrid\EFGrid\App_Data\NORTHWND.MDF';Integrated Security=True;Connect Timeout=30"); 
            } 
        } 
    } 


Documentations : 
Online demo :  
 
Please get back to us if you need further assistance. 

Regards, 
Renjith Singh Rajendran. 


AN Andrzej February 5, 2020 10:18 PM UTC

Thanks for the sample project EFGrid1106339687. I've put my DB-path optionsBuilder.UseSqlServe(), but the EjsGrid doesn't show up. Is there any way to catch possible error/exception which causes the grid not to render?
And another question - why is the error displayed all the time: "An unhandled exception has occurred. See browser dev tools for details.". Maybe it is correlated with a grid problem? Regards, Andrzej

RS Renjith Singh Rajendran Syncfusion Team February 6, 2020 11:09 AM UTC

Hi Andrzej, 

Thanks for your update. 

We suggest you to ensure to refer the necessary scripts and styles in your application. Please follow the below getting started link for more details, 

And also, we need more details to further proceed on this and provide you a solution as early as possible. Kindly get back to us with the following details. 

  1. Error : An unhandled exception has occurred. See browser dev tools for details. Kindly share the details of the script error shown in your browser console window.
Guide to open dev tools : For chrome browser, right click on browser page and select the Inspect option. Or you can use keyboard shortcut Ctrl + Shift + I. 
  1. Kindly share with us the sample which you have tried from your side. So that we could validate this problem from your side and provide you a solution.
  2. Share the details of script errors(in browser console) or exception occurred while running your application.
  3. Share a video demo showing the problem you are facing.

The provided information will help us analyze the problem, and provide you a solution as early as possible. 

Regards, 
Renjith Singh Rajendran. 


AN Andrzej February 25, 2020 09:41 PM UTC

Hello, it took me a long time to test this and other samples and I didn't find a solution. I need a logic which loads from a database only the records visible in current page. In the sample EFGrid1106339687, the whole database is loaded at once into memory despite using IQueryable. When I run this project with database containing 800 000 records, then I can see that the memory usage is 3GB. I also made a bigger DB and then I get System.OutOfMemoryException. Could you give me a hint or some code that reads only currently visible page/records into memory? Best regards

RS Renjith Singh Rajendran Syncfusion Team February 26, 2020 11:59 AM UTC

Hi Andrzej, 

We suggest you to bind the DataSource to Grid by using any one the adaptors (URLAdaptor, WebAPI, Odata etc) suggested in the below document. Using Adaptor, we can achieve a remote way of binding data to Grid. Refer the below documentation, 

We have prepared a sample to bind data to Grid by using the UrlAdaptor. Please download the sample form the link below, 
 
Please refer the code below,  

 
<EjsGrid @ref="Grid" TValue="Order" AllowPaging="true"> 
    <EjsDataManager Url="/api/Default" Adaptor="Adaptors.UrlAdaptor"></EjsDataManager> 
    ... 
</EjsGrid> 

[DefaultController.cs] 

        [HttpPost] 
        [Route("api/[controller]")] 
        public object Post([FromBody]DataManagerRequest dm) 
        { 
            IQueryable<Order> DataSource = db.GetAllOrders();            
            if (dm.Search != null && dm.Search.Count > 0) 
            { 
                DataSource = DataOperations.PerformSearching(DataSource, dm.Search);  //Search 
            } 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                DataSource = DataOperations.PerformSorting(DataSource, dm.Sorted); 
            } 
            if (dm.Where != null && dm.Where.Count > 0) //Filtering 
            { 
                DataSource = DataOperations.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); 
            } 
            int count = DataSource.Cast<Order>().Count(); 
            if (dm.Skip != 0) 
            { 
                DataSource = DataOperations.PerformSkip(DataSource, dm.Skip);   //Paging 
            } 
            if (dm.Take != 0) 
            { 
                DataSource = DataOperations.PerformTake(DataSource, dm.Take); 
            } 
            return new { result = DataSource, count = count }; 
        }        


Please get back to us if you need further assistance. 

Regards, 
Renjith Singh Rajendran. 


Loader.
Up arrow icon