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.
Syncfusion Feedback

Autorefresh grid with SQL data

Thread ID:

Created:

Updated:

Platform:

Replies:

129949 Apr 11,2017 01:36 PM UTC Jul 5,2017 04:14 PM UTC ASP.NET Web Forms 13
loading
Tags: Grid
Ilya
Asked On April 11, 2017 01:36 PM UTC

Hi dear Syncfusion.

I found this example for autorefresh Grid. Can you provide me, how to adapt it to SQL data filling? I searching it in forums and examples, but cant find. 

Thavasianand Sankaranarayanan [Syncfusion]
Replied On April 12, 2017 04:41 PM UTC

Hi Ilya, 

Thanks for contacting Syncfusion support. 

Query: How to adapt it to SQL data filling. 
 
We have analyzed your query and we suspect that you want use the sql dataSource for the grid with auto refreshing the grid. 
 
Refer the code example. 
 
 
<ej:Button ID="Start" runat="server" Type="Button" Text="Start" ClientSideOnClick="start" Width="100px"> 
                    </ej:Button> 
    <ej:Button ID="Stop" runat="server" Type="Button" Text="Stop" Enabled="False" ClientSideOnClick="stop" Width="100px"> 
                    </ej:Button> 
    <div> 
         
        <ej:Grid ID="Grid1" runat="server" AllowSelection="False" EnableRowHover="False" ClientIDMode="Static" DataSourceID="SqlData" Query ="new ej.Query().from('Product').select(['OrderID', 'CustomerID', 'Freight', 'EmployeeID', 'ShipCity']).take(5)">  
       <ClientSideEvents QueryCellInfo="update" /> 
            <Columns> 
                 
                ---------------------------                 
 
            </Columns>            
                         
        </ej:Grid> 
        <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>" 
            SelectCommand="SELECT * FROM [Orders]"></asp:SqlDataSource> 
    </div> 
        </div> 
</div> 
     
    <script type="text/javascript"> 
        function update(args) { 
            if (args.column !== undefined && args.column.field === "Freight") { 
                var $element = $(args.cell); 
                if (ej.parseFloat(ej.format(args.text, "c")) < 30) 
                    $element.css("background-color", "#5ec026").css("color", "black"); 
                else 
                    $element.css("background-color", "#86dd55").css("color", "black"); 
            } 
        } 
        function start() { 
            timerID = setInterval(refreshGrid, 2000); 
            $('#<%= Start.ClientID %>').ejButton("instance").disable(); 
            $('#<%= Stop.ClientID %>').ejButton("instance").enable(); 
        } 
        function stop() { 
            clearInterval(timerID); 
            $('#<%= Stop.ClientID %>').ejButton("instance").disable(); 
            $('#<%= Start.ClientID %>').ejButton("instance").enable(); 
        } 
        function refreshGrid() { 
            var model = $('#<%= Grid1.ClientID %>').ejGrid("model"); 
            for (var i = 0; i < model.dataSource.length; i++) { 
                model.dataSource[i].Freight = Math.floor(Math.random() * 50 + 1); 
                model.dataSource[i].EmployeeID = Math.floor(Math.random() * 100); 
                model.dataSource[i].OrderID = Math.floor(Math.random() * 5) * 10; 
            } 
            $('#<%= Grid1.ClientID %>').ejGrid("refreshContent"); 
        } 
    </script> 
 
 
 
 
We have prepared a sample and it can be downloadable from the below location. 
 

Regards, 
Thavasianand S. 


Ilya
Replied On April 14, 2017 12:26 PM UTC

Hello.

Cant find Page.aspx where you place your table in example. And 2nd question. If we will update table with new SQL-querry, why we need this ramdomizer?

            var model = $('#<%= Grid1.ClientID %>').ejGrid("model"); 
            for (var i = 0; i < model.dataSource.length; i++) { 
                model.dataSource[i].Freight = Math.floor(Math.random() * 50 + 1); 
                model.dataSource[i].EmployeeID = Math.floor(Math.random() * 100); 
                model.dataSource[i].OrderID = Math.floor(Math.random() * 5) * 10; 
            } 

Isuriya Rajan [Syncfusion]
Replied On April 17, 2017 12:55 PM UTC

Hi Ilya,  
 
We have included this function in gridFeatures.aspx .As per your previous requirement we have used sql dataSource in provided sample. Also we have randomly changing the grid model data’s locally .For effecting the local changes in grid we have called the refreshContent method to refresh the grid content. 
 
Please share the below details this will help us to prompt your solution. 
 
could you please share your code example of dataSource update and share which method do you used to applied. 
 
Note : if you update table  data directly  means , the grid will affect update datasource once grid dataSource refreshed 
  
Regards, 
Isuriya R     
 


Ilya
Replied On June 8, 2017 06:59 AM UTC

Hello dear Syncfusion!

I can not understand your example. Your table "ProductsGrid" has columns: "ProductID", "ProductName", "UnitPrice", "UnitInStock", "UnitOnOrder". But query in tag is some another:

<ej:Grid ID="Grid1" runat="server" AllowSelection="False"EnableRowHover="False" ClientIDMode="Static" DataSourceID="SqlData" Query ="new ej.Query().from('Product').select(['OrderID', 'CustomerID', 'Freight', 'EmployeeID', 'ShipCity']).take(5)">

What does it mean: 'Product', 'OrderID', 'CustomerID', 'Freight', 'EmployeeID', 'ShipCity'  ?
--------------------------------------------------------------------------------------------------------------------------
And my example is:

        <div id="GRD" style="max-height:650px">
        <ej:Grid ID="FlatGrid" runat="server" DataSourceID="SqlData" Height="600" AllowSorting="True" AllowScrolling="true">
            <ClientSideEvents QueryCellInfo="update" />
            <Columns>
                <ej:Column Field="Sensor" HeaderText="Сенсор" IsPrimaryKey="True" TextAlign="Center" Width="75" />
                <ej:Column Field="Value" HeaderText="Значение" TextAlign="Center" Width="75" />
                <ej:Column Field="HLL" HeaderText="Нижний аварийный уровень" TextAlign="Center" Width="110" />
                <ej:Column Field="LL" HeaderText="Нижний предел" TextAlign="Center" Width="110" />
                <ej:Column Field="HL" HeaderText="Верхний предел" TextAlign="Center" Width="110" />
                <ej:Column Field="HHL" HeaderText="Верхний аварийный уровень" TextAlign="Center" Width="110" />
            </Columns>           
        </ej:Grid>
        <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="server=192.168.0.1;user=user;port=3306;password=password;"
            providerName="MySql.Data.MySqlClient" SelectCommand="SelectCommand="SELECT Sensor,Value, HLL, LL, HL, HHL FROM holod_2016.set_points;"></asp:SqlDataSource>
        
    </div>

<script>
        function update(args) {
            if (args.column !== undefined && args.column.field === "Value") {
                var $element = $(args.cell);

                if ((ej.parseFloat(ej.format(args.text)) < args.data.HL) || (args.data.HL > ej.parseFloat(ej.format(args.text))))
                    $element.css("background-color", "rgba(0,255,0,0.5)").css("color", "black");
                else
                    $element.css("background-color", "rgba(255,0,0,0.5)").css("color", "black");
            }
        }
    </script>

I need write some like this? 
<ej:Grid ID="FlatGrid" runat="server" AllowSelection="False"EnableRowHover="False" ClientIDMode="Static" DataSourceID="SqlData" Query ="new ej.Query().from('Holod_2016.set_points').select(['Sensor', 'Value', 'HLL', 'LL', 'HL','HHL']).take(6)">

Thavasianand Sankaranarayanan [Syncfusion]
Replied On June 9, 2017 11:44 AM UTC

Hi Ilya, 

Sorry for the inconvenience caused. 

We have share the incorrect code snippets with you and refer below code example for the corrected snippets 


<ej:Grid ID="Grid1" runat="server" DataSourceID="SqlData"  
 
         Query ="new ej.Query().from('Orders').select(['OrderID', 'CustomerID', 'EmployeeID', 'ShipCity', 'Freight']).take(5)">  
        
            <Columns> 
                 
                ---------------- 
                
            </Columns>            
                         
        </ej:Grid> 
 
        <asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>" 
            SelectCommand="SELECT * FROM [Orders]"></asp:SqlDataSource> 


In the above code snippets we have refer the Orders table with the columns “OrderID, CustomerID, EmployeeID, ShipCity, Freight”.  

In the given example of you query, you can proceed with your table Holod_2016.set_points with the columns “Sensor, Value, HLL, LL, HL, HHL”. 

We have prepared a sample and it can be downloadable from the below location. 


Regards, 
Thavasianand S. 


Ilya
Replied On June 13, 2017 11:14 AM UTC

Hi, dear Syncfusion!

Sorry, maybe i do something wrong, but your method is not working. 

Please check attachment. i modifyed your example for my BD, but numbers are not refreshing.

Attachment: Live_update_sql_c5eefa94.7z

Thavasianand Sankaranarayanan [Syncfusion]
Replied On June 14, 2017 03:39 PM UTC

Hi Ilya, 

We have checked your given code example and we unable to reproduce the reported issue from our end. 

We have prepared a video demonstration of not reproduction of the issue. 


Provide the following details for better assistance. 

  1. Screen shot or video demonstration of the issue.
  2. Please clarify that ej.Query() is not returning the given count of records or facing any issue in live update.
  3. If your query is related live update, then share those details.
  4. Share the stack trace, if you face any script errors.
  5. If possible share the sample or reproduce the issue in the attached sample.
  6. Essential Studio version.

Regards, 
Thavasianand S. 


Ilya
Replied On June 16, 2017 08:15 AM UTC

Hello dear Syncfusion!
I recorded video for you, but it is too large. I placed it on 1drive server.

I think you will not reproduced my code on 100% because you do not have access to my base. 

And I didn`t see "LiveUpdate" in your video. You just show, that your querry is working. I need execute querry every 1min and refresh table with new data.

Thank you.

Thavasianand Sankaranarayanan [Syncfusion]
Replied On June 20, 2017 12:07 PM UTC

Hi Ilya, 

In this query you have said that you have updated the values in DB but it is not get refreshed in Grid. So, we suggest you to enable adaptors in your sample. For, every postback it will call for update the dataSource to the Grid. 

Refer the below code example. 

[GridFeatures.aspx] 

<ej:Grid ID="Grid1" runat="server"  
         Query ="new ej.Query().from('Orders').select(['OrderID', 'CustomerID', 'EmployeeID', 'ShipCity', 'Freight']).take(10)">  
            <DataManager URL="GridFeatures.aspx/Data" Adaptor="WebMethodAdaptor" /> 
            <ClientSideEvents QueryCellInfo="update" /> 
            
       <Columns> 
                <ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="true"    TextAlign="Right" Width="90" /> 
                <ej:Column Field="CustomerID" HeaderText="Customer ID"   Width="100"  /> 
                <ej:Column Field="EmployeeID" HeaderText="Employee ID"   TextAlign="Right" Width="110"  /> 
                 
                 
            </Columns>            
                         
        </ej:Grid> 
 
---------------------- 
[GridFeatures.aspx.cs] 

[WebMethod] 
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
        public static object Data(Syncfusion.JavaScript.DataManager value) 
        { 
            var datatable = GetGridDT(); 
             
            var emp = (from DataRow row in datatable.Rows 
                       select new OrderTable 
                       { 
                           OrderID = Convert.ToInt32(row["OrderID"]), 
                           CustomerID = row["CustomerID"].ToString(), 
                           EmployeeID = Convert.ToInt32(row["EmployeeID"]), 
                           ShipCountry = row["ShipCountry"].ToString() 
 
                       }).ToList(); 
            IEnumerable Data = emp.ToList(); 
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ToString()); 
            DataTable dt = new DataTable("Employee"); 
            SqlCommand cmd = new SqlCommand(); 
            cmd.Connection = myConnection; 
            cmd.CommandText = "select * from Employees"; 
            cmd.CommandType = CommandType.Text; 
            SqlDataAdapter da = new SqlDataAdapter(); 
            da.SelectCommand = cmd; 
            if (myConnection.State == ConnectionState.Closed) 
            { 
                myConnection.Open(); 
            } 
            da.Fill(dt); 
            List<object> dropObj = new List<object>(); 
            //read data from the SQL table  
            using (SqlDataReader dr = cmd.ExecuteReader()) 
            { 
                while (dr.Read()) 
                { 
                    //Convert Table rows to text/value pair  
                    dropObj.Add(new { text = dr.GetValue(0).ToString(), value = dr.GetValue(0) }); 
                } 
            } 
            int count = emp.AsQueryable().Count(); 
            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations(); 
            if (value.Skip != 0) 
            { 
                Data = operation.PerformSkip(Data, value.Skip); 
            } 
            if (value.Take != 0) 
            { 
                Data = operation.PerformTake(Data, value.Take); 
            } 
            
            return new { result = Data, count = count }; 
            
        } 



We have prepared a sample and it can be downloadable from the below location. 


Note: In the above example we have define only how to update the Grid in postback using the adaptor. So, there is no update we don’t done in DB while clicking the start button. 

Refer the help documentation. 


For more information about UrlAdaptor, please refer the following Knowledge base link. 


Regards, 
Thavasianand S. 


Ilya
Replied On June 28, 2017 11:02 AM UTC

Hello dear Syncfusion.

But how to realize autorefresh? For example 1 time in 5 sec.


Thavasianand Sankaranarayanan [Syncfusion]
Replied On June 29, 2017 01:13 PM UTC

Hi Ilya, 

In the given solution, if we click on the start button then the db values get change and also the method refreshContent() were called. While calling for the refreshContent() method in a particular time out, a server side post back were sent and the db values are again bind to the Grid columns, if any changes are made in the db at that timeout then it will reflect in the Grid. So, that we suggest you to enable adaptor in your sample to show the updated values in Grid. 
 
Note: In the previous attached samples, we have call the refreshContent() method in every 2000ms. So, according to your db value changes you can set the interval in your sample. 

If we misunderstood your query then please get back to us with the following. 
  1. What was your expectation on this query.
  2. Share the exact scenario that you facing the difficulty.

Regards, 
Thavasianand S. 


Ilya
Replied On July 4, 2017 08:36 AM UTC

Hi dear Syncfusion.Sorry for my bad explaining. How works my system:1. DataBase - my database is MySQL DB. It feels with my service every 15 sec. Web-site DO NOT make any changes in DB. It only reads.2. Web-site - it needs to show table with data from DB ( point 1 ). But i want to refreshing data in DB, for example 1 time in 1 minute.What I want to get: DB, which fills from service 1 time in 15 sec (its already done and works fine), and web-site whith shows refreshing table (1 time in 1 minute ) with data from DB..

Thavasianand Sankaranarayanan [Syncfusion]
Replied On July 5, 2017 04:14 PM UTC

Hi Ilya, 

Query 1: We have update the DB once in every 15 seconds and Grid do not make any changes in DB, Grid will reads the DB. 
               For this query we suggest not to enable the editSettings property of ejGrid control. 
Query 2:  Refreshing Grid once in every 1 minute.  
                We can achieve your requirement by calling the refreshContent() method in the time interval of 60000ms(1 minute) to refresh the Grid. By clicking an external button(start), we can start for refreshing the Grid. 

Refer the below code example. 


<ej:Grid ID="Grid1" runat="server"  
         Query ="new ej.Query().from('Orders').select(['OrderID', 'CustomerID', 'EmployeeID', 'ShipCity', 'Freight']).take(10)">  
            <DataManager URL="GridFeatures.aspx/Data" Adaptor="WebMethodAdaptor" /> 
             
             
       <Columns> 
                <ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="true"    TextAlign="Right" Width="90" /> 
                <ej:Column Field="CustomerID" HeaderText="Customer ID"   Width="100"  /> 
                <ej:Column Field="EmployeeID" HeaderText="Employee ID"   TextAlign="Right" Width="110"  /> 
                <ej:Column Field="ShipCity" HeaderText="Ship City" Width="100" /> 
                <ej:Column Field="Freight" HeaderText="Freight"  TextAlign="Right" Width="90" Format="{0:C}" />                 
            </Columns>            
                         
        </ej:Grid> 
        </div> 
 
    <ej:Button ID="Start" runat="server" Type="Button" Text="Start" ClientSideOnClick="start" Width="100px"></ej:Button> 
 
    <ej:Button ID="Stop" runat="server" Type="Button" Text="Stop" Enabled="False" ClientSideOnClick="stop" Width="100px"></ej:Button> 
        </div> 
</div> 
    <script type="text/javascript"> 
        $(function () { 
            $("#sampleProperties").ejPropertiesPanel(); 
        }); 
         
        function start() { 
 
            timerID = setInterval(refreshGrid, 60000); // to refresh the Grid in every one minute 
            $('#<%= Start.ClientID %>').ejButton("instance").disable(); 
            $('#<%= Stop.ClientID %>').ejButton("instance").enable(); 
        } 
        function stop() { 
 
            clearInterval(timerID); // to stop refreshing the Grid. 
            $('#<%= Stop.ClientID %>').ejButton("instance").disable(); 
            $('#<%= Start.ClientID %>').ejButton("instance").enable(); 
        } 
        function refreshGrid() { 
            var model = $('#<%= Grid1.ClientID %>').ejGrid("model"); 
             
            $('#<%= Grid1.ClientID %>').ejGrid("refreshContent"); // to refreshing the Grid 
        } 
    </script> 


We have prepared a sample and it can be downloadable from the below location. 


Refer the help documentation. 

 
If we misunderstood your query then please get back to us. 

Regards, 
Thavasianand S. 


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.

Warning Icon 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.Close Icon

;