Connect to a Database?

[M Oliveira]

Hi.

I'm evaluating your Pivot Table and, though I have had no troubles integrating it with a React app, I can't find any information as to how this component can be connected to a database.

Is this possible? If so, is there documentation that explains how to do it?

Thanks in advance!

[Sivamathi Natarajan]

Hi Oliveira, 

 

Thank you for using Syncfusion Products. 

 

We would like to inform that we have introduced a serve side engine where the entire aggregation is performed in server and only the sliced information alone will be passed to client side. Along with this, provided CSV and SQL data sources support. 

 

And it has been included in the weekly release  (v18.2.0.54) and it is available in nuget.org (https://www.nuget.org/). 

 

Meanwhile, we have prepared a stand-alone WebAPI sample to do the aggregation in server side and passed only the sliced aggregated details in client side. Please check the guidelines in the below file to run the controller application. 

 

Guidelines and controller file:  https://www.syncfusion.com/downloads/support/directtrac/general/ze/Pivot_server_support-1277357809 

 

Sample: https://stackblitz.com/edit/react-dvvkud-fiwmj5?file=index.js 

 

You can connect the data base to pivot table in PivotController.cs file. And please check the below code for you reference. 

 

Code Example: 

public async Task<object> GetData(FetchData param) 

        { 

            return await _cache.GetOrCreateAsync("dataSource" + param.Hash, 

                async (cacheEntry) => 

                { 

                    cacheEntry.SetSize(1); 

                    cacheEntry.AbsoluteExpiration = DateTimeOffset.UtcNow.AddMinutes(60); 

                    // Here you need to return raw data 

 

                   // return new DataSource.PivotViewData().GetVirtualData(); 

                    return new DataSource.BusinessObjectsDataView().GetDataTable(); // To connect data base 

                }); 

        } 

 

 

Please let us know if you have concern. 

 

Regards, 

Sivamathi. 

[M Oliveira]

Hi Sivamathi.

I have spent some time looking at the examples and documentation you sent me, but I still have some questions.

From the Pivot Tables' documentation, I understood it could connect to a back-end and, from there, retrieve data to populate the table. This is something I have taken for granted, since all the front-end code I saw in Syncfusion's examples hint at such a connection. The example you provided also mocks this behavihor, which confirms my initial assumption.

However, I’m asked by my superiors to clarify a specific detail: is there any way the front-end React component of the Pivot Tables connects directly to a database?

For example, if I wanted to simply feed it the database credentials, would it be able to use those to connect and retrieve data from a database?

Thanks in advance!

[Sivamathi Natarajan]

Hi Oliveira,  

 

We have checked your requirement at our end. You can connect the database using ASP.NET Core 2.0 and React.js with the help of Entity Framework Core database. 

 

 

 

Code Example: (Home.tsx) 

 

public remoteData = new DataManager({ 

        url: "/Home/UrlDatasource", 

        adaptor: new UrlAdaptor(), 

        crossDomain: true 

    }).executeQuery(new Query().take(6)).then((e: ReturnOption) => { 

        var pivotObj = document.querySelector('.e-pivotview'); 

        if (pivotObj) 

            (pivotObj as any).ej2_instances[0].dataSourceSettings.dataSource = e.result as IDataSet[]; 

    }).catch((e) => true); 

 

 

 

HomeController.cs 

 

private readonly IHostingEnvironment _env; 

 

        public HomeController(IHostingEnvironment env) 

        { 

            _env = env; 

        } 

        static string cons; 

 

        static SqlConnection con; 

        public static List<Order> Orders { get; set; } 

        public IActionResult Index() 

        { 

            cons = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + _env.ContentRootPath + @"\Data\NORTHWND.MDF;Integrated Security=True"; 

            con = new SqlConnection(cons); 

            return View(); 

        } 

        public static DataSet CreateCommand(string queryString, string connectionString) 

        { 

            using (SqlConnection connection = new SqlConnection( 

                       connectionString)) 

            { 

 

                SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection); 

                DataSet dt = new DataSet(); 

                try 

                { 

                    connection.Open(); 

                    adapter.Fill(dt);// using sqlDataAdapter we process the query string and fill the data into dataset 

                } 

                catch (SqlException se) 

                { 

                    Console.WriteLine(se.ToString()); 

                } 

                finally 

                { 

                    connection.Close(); 

                } 

                return dt; 

            } 

        } 

        public object UrlDatasource([FromBody]DataManagerRequest dm) 

        { 

 

            string qs = "SELECT OrderID, ShipCountry, ShipCity FROM dbo.Orders ORDER BY OrderID; "; 

 

            DataSet data = CreateCommand(qs, cons); 

            Orders = data.Tables[0].AsEnumerable().Select(r => new Order 

            { 

                OrderID = r.Field<int>("OrderID"), 

                ShipCountry = r.Field<string>("ShipCountry"), 

                ShipCity = r.Field<string>("ShipCity"), 

            }).ToList();  // here we convert dataset into list 

            IEnumerable<Order> DataSource = Orders; 

            if (con.State != ConnectionState.Open) 

                con.Open(); 

            SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM dbo.Orders", con); 

            Int32 count = (Int32)comm.ExecuteScalar(); 

            con.Close(); 

            return DataSource; 

        } 

    } 

    public class Order 

    { 

        public int OrderID { get; set; } 

        public string CustomerID { get; set; } 

        public Nullable<int> EmployeeID { get; set; } 

        public Nullable<System.DateTime> OrderDate { get; set; } 

        public Nullable<System.DateTime> RequiredDate { get; set; } 

        public Nullable<System.DateTime> ShippedDate { get; set; } 

        public Nullable<int> ShipVia { get; set; } 

        public Nullable<decimal> Freight { get; set; } 

        public string ShipName { get; set; } 

        public string ShipAddress { get; set; } 

        public string ShipCity { get; set; } 

        public string ShipRegion { get; set; } 

        public string ShipPostalCode { get; set; } 

        public string ShipCountry { get; set; } 

    } 

 

To load the data from database for different customers you can filter the data in code behind and remove the fields from filter dialog using memberEditorOpen event. Please check the below sample for your reference. 

 

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/ReactSql-1647333099 

 

Please let us know if you have concern. 

 

Regards, 

Sivamathi. 

[M Oliveira]

Hi Sivamathi,

Thanks for the code examples. I gave them a look and they are pretty similar to what we can find in the Pivot Tables documentation.

Unfortunately, our system's specs don't allow us to use these examples as we have Django back-end. That said, we are obliged to understand what the limitations of the Pivot Tables are so we can consider their usefulness on our end.

From your examples, I understand we will always need a some sort of back-end to retrieve the data that is to be shown in the Pivot Table as the table needs to retrieve the information it is to display. Is this assumption correct?

Furthermore, I need clarification about the React front-end component itself: it can only retrieve information from a remote database by connecting to whatever sever we have that links directly to the database, correct? 

I am just trying to confirm this information, since it does look like the front-end component serves only to display the data retrieved from the back-end.

Thanks in advance!

[Sivamathi Natarajan]

Hi Oliveira, 

 

Please find our response. 

 

Query	Response
I understand we will always need a some sort of back-end to retrieve the data that is to be shown in the Pivot Table as the table needs to retrieve the information it is to display. Is this assumption correct?	Recently we have provided the server-side engine support to connect the server-side databases directly. Here the entire aggregation will be done in server-side and only the view-port information alone will be passed to client side to render the pivot table. And we didn’t mention the feature in UG documentation yet but will include it asap.    Yes your assumption is correct. It needs WebAPI controller to define your data source. In React App you should mention the controller URL to fetch the information from server-side engine.
Furthermore, I need clarification about the React front-end component itself: it can only retrieve information from a remote database by connecting to whatever sever we have that links directly to the database, correct?	Yes in React front-end, you couldn’t able to connect your database directly but allows to connect the same database from remote server where it needs only remote server URL.

 

Regards, 

Sivamathi.