Connect to SqlServer Database, retrieve data from model (table), and perform CRUD operations

[Moataz ElSherbini]

Sir,

Based on a sample posted in this forums, I have been able to add a grid to a page, format it and retrieve data from a table. I think everything is fine up to  here. When I try to modify a record, Delete a record or add one, the grid is modified without modifying the actual table in the database.

I attached the controller file, the model and the view.

1- what I am doing wrong.

2- The next step for me is to convert the grid to excel and pdf files.

3- can you provide me with a sample that performs Grid CRUD operations connecting to SqlServer database then converting to excel?

4- Another question, is there an "auto width" option that makes the grid column adjust automatically to it's content without wrapping?  AutoFitColumns method.

I use Asp.net core 2.2 and SqlServer Express.

I am not lazy by the way, I read everything but there must be something I missed :)

Thank you.

Attachment: files_9c7e1ece.zip

[Gurupriyadharshini Sankaranarayanan]

Hi Moataz, 

 

Greetings from Syncfusion support. 

 

Query 1: Based on a sample posted in this forums, I have been able to add a grid to a page, format it and retrieve data from a table. I think everything is fine up to  here. When I try to modify a record, Delete a record or add one, the grid is modified without modifying the actual table in the database. I attached the controller file, the model and the view. 

what I am doing wrong. 

 

We have checked your query and you have to handle the grid actions (paging, sorting, searching and filtering) in server side. Please refer the below code snippet. 

 

[Index.cshtml] 

    <ejs-grid id="Grid" toolbar="@(new List<string>() { "Add", "Edit","Delete", "Update", "Cancel", "Search" })" allowPaging="true"> 

        <e-data-manager url="/Home/UrlDatasource" adaptor="UrlAdaptor" insertUrl="/Home/Insert" updateUrl="/Home/Update" removeUrl="/Home/Remove1"></e-data-manager> 

        <e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true"></e-grid-editSettings>        

        <e-grid-pagesettings pageCount="5"></e-grid-pagesettings> 

        <e-grid-columns> 

            <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" validationRules="@(new { required=true})" textAlign="Right" width="120"></e-grid-column> 

            .  .  .  . 

        </e-grid-columns> 

    </ejs-grid> 

 

[HomeController.cs] 

        public ActionResult UrlDatasource([FromBody]ExtendedDataManager dm) 

            { 

            IEnumerable DataSource = orddata.ToList(); 

            DataOperations operation = new DataOperations(); 

            if (dm.Search != null && dm.Search.Count > 0) 

            { 

                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search 

            } 

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 

            { 

                DataSource = operation.PerformSorting(DataSource, dm.Sorted); 

            } 

            if (dm.Where != null && dm.Where.Count > 0) //Filtering 

            { 

                 

                DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); 

            } 

            int count = DataSource.Cast<OrdersDetails>().Count(); 

            if (dm.Skip != 0) 

            { 

                DataSource = operation.PerformSkip(DataSource, dm.Skip);   //Paging 

            } 

            if (dm.Take != 0) 

            { 

                DataSource = operation.PerformTake(DataSource, dm.Take); 

            } 

            return Json(new { result = DataSource, count = count }); 

        } 

 

Query 2: The next step for me is to convert the grid to excel and pdf files. 

 

You can convert the grid into excel and pdf files by referring the below documentation link. 

 

Documentation: https://ej2.syncfusion.com/aspnetcore/documentation/grid/excel-exporting/?no-cache=1   

                                    https://ej2.syncfusion.com/aspnetcore/documentation/grid/pdf-export/  

 

Query 3: can you provide me with a sample that performs Grid CRUD operations connecting to SqlServer database then converting to excel? 

 

We have prepared a sample in which URL adaptor is used and CRUD operation is handled. Please refer the following sample link. 

 

Sample : http://www.syncfusion.com/downloads/support/directtrac/general/ze/CRUD-operation-396938151  

 

Query 4: Another question, is there an "auto width" option that makes the grid column adjust automatically to it's content without wrapping?  

 

You can use autoFitColumns() method in the load event to adjust the column without any wrapping. Please refer the below documentation link. 

 

Documentation: https://ej2.syncfusion.com/documentation/api/grid#autofitcolumns  

Regards, 

Gurupriyadharshini S. 

[Moataz ElSherbini]

Thank you for the reply and the sample.

However the sample is too broad, but I was able to reach a point of showing the table headers, the buttons and the controls, but the data couldn't be retrieved from the database and showing the spinning "loading sign".

Please help me in this.

I attached the controller, the view and model I reach until now and please see if I loaded all the required js files in the layoutview.

Attachment: Files_fbb97d9e.zip

[Pavithra Subramaniyam]

Hi Moataz, 

 

Thanks for your update. 

 

Query: However the sample is too broad, but I was able to reach a point of showing the table headers, the buttons and the controls, but the data couldn't be retrieved from the database and showing the spinning "loading sign". 

I attached the controller, the view and model I reach until now and please see if I loaded all the required js files in the layoutview. 

 

We have checked your query and attached code sample. Please use the below reference link in your Layout file. 

 

[_Layout.cshtml] 

<head>     

    <link rel='nofollow' href="https://cdn.syncfusion.com/ej2/material.css" rel="stylesheet"> 

   <script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script> 

</head> 

 

In your code, we have found that you have used hdrdata as empty string. Please find the below way to bind dataSource to grid (already given in the last update). If you have bound the dataSource in proper way, then share the corresponding code for us to provide better solution.  

 

[HeadersController.cs] 

public class Employee1Details 

        { 

            public static List<Employee1Details> order = new List<Employee1Details>(); 

            public Employee1Details() 

            { 

            } 

            public Employee1Details(int EmployeeId, string FirstName, string LastName, int ReportsTO) 

            { 

                this.EmployeeID = EmployeeId; 

                .   .   .   .  . 

            } 

            public static List<Employee1Details> GetAllRecords() 

            { 

                if (order.Count() == 0) 

                { 

                    for (int i = 1; i < 2; i++) 

                    { 

                        order.Add(new Employee1Details(i + 0, "Nancy", "Davolio", i + 0)); 

                        .  .  .  .  

                    } 

                } 

                return order; 

            } 

            public int? EmployeeID { get; set; } 

             .  .  .  .  

        } 

 

You have mentioned that Grid Page is showing the spinning “loading sign”. Please Share the below details that will be helpful for us to provide a solution. 

 

1. Share the stack trace if you have faced any script error while loading the page.
2. Bind the ‘actionFailure’ event in your Grid and share the arguments if it hits.

 

Please get back to us with all these details mentioned above. 

 

Regards, 

Pavithra S.