none
Dataset cache RRS feed

  • Question

  • Hi, i have a Devexpress grid in SharePoint, where i am pulling data from the Database. But the data updates daily to the database and i have written code to "cache dataset" to refresh grid for every 2 hours after database is updated, but looks like grid is not refreshing. But when i recycle app pool then it is working. Here is my code, but same code is working in my development. Please let me know to make any changes to code to refresh the grid for every 2 hours

                             

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.Threading.Tasks;
    using System.Configuration;
    using System.Runtime.Caching;
    using System.Web.Caching;
    namespace Grid
    {
        public class GridDS
        {
            private bool _Loaded = false;
            private DataSet _ListDS = null;
            private string _ConnStr = string.Empty;

            public DataSet ListDS
            {
                get
                {
                    return _ListDS;
                }
            }

            public bool IsLoaded
            {
                get
                {
                    return _Loaded;
                }
            }

            public GridDS(string ConnStr)
            {
                _ConnStr = ConnStr;
                 Loadtable();

            }

             private void Loadtable()
            {
                ObjectCache cache = MemoryCache.Default;
                string cacheKey = "RefreshData";
                DataSet dsreturn = new DataSet();

                if (cache.Contains(cacheKey))
                {
                    dsreturn = (DataSet)cache.Get(cacheKey);
                    if (dsreturn != null && dsreturn.Tables[0].Rows.Count > 0)
                    {
                        dsreturn.Tables[0].PrimaryKey = new DataColumn[1] { dsreturn.Tables[0].Columns[0] };
                        _ListDS = dsreturn;
                    }
                }
                else
                {
                    dsreturn = LoadListDS();
                    CacheItemPolicy itemPolicy = new CacheItemPolicy();
                    itemPolicy.AbsoluteExpiration = DateTime.Now.AddHours(2.0);
                    cache.Add(cacheKey, dsreturn, itemPolicy);
                }
            }

            private DataSet LoadListDS()
            {
                try
                {
                    DataSet ds = new DataSet();
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
                    SqlCommand cmd = new SqlCommand("Users", con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    con.Open();
                    da.Fill(ds);
                    con.Close();

                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        ds.Tables[0].PrimaryKey = new DataColumn[1] { ds.Tables[0].Columns[0] };
                        _ListDS = ds;
                    }

                    return ds;
                }
                catch (Exception ex)
                {
                    throw new Exception("Error loading grid from db: " + ex.Message);

                }
          }

       }

    }



    • Edited by cb39 Wednesday, December 21, 2016 4:16 PM
    Wednesday, December 21, 2016 4:16 PM

Answers

  • Hi,

    This question not seems to be SharePoint topic, still you can check the below option.

    Could you please compare the development Server's IIS config settings and Production Server IIS config settings ?

    May be the ClientCache control mode has been set to "DisableCache" on your IIS config file as outlined here

    Or in your SharePoint Web application's config file , please check the "<configuration> block any tag that could stop or manage the "Cache" mechanism on the application level.

    Please let us know, how your progressing on it, if you found any other solution please post here, that could be helpful for others.


    Murugesa Pandian | MCPD | MCTS | SharePoint 2010 |

    • Marked as answer by cb39 Wednesday, January 11, 2017 4:59 PM
    Thursday, December 22, 2016 2:10 PM
  • the simplest way is to log to text file, e.g. to c:\temp folder:

    private static void log(string msg, params object[] args)
    {
        try
        {
            msg = string.Format(msg, args);
            msg = string.Format("{0} (thread id: {1})\t{2}\n", DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss"), Thread.CurrentThread.ManagedThreadId, msg);
            File.AppendAllText("c://temp/log.txt", msg);
        }
        catch
        {
        }
    }
    In order to make it work create c:\temp folder (or whatever you will use in the code) and grant write permissions to Everyone. Then run your code which will write traces and check c:\temp\log.txt.

    Blog - http://sadomovalex.blogspot.com
    Dynamic CAML queries via C# - http://camlex.codeplex.com

    • Marked as answer by cb39 Wednesday, January 11, 2017 4:58 PM
    Tuesday, December 27, 2016 2:53 PM
  • Hi,

    The “Logging()” method in my reply is a custom method which needs to be built according to what info you may want to log out, also you can name it with another name such as “CustomLogging()”.

    To save logs in SharePoint list, Server Object Model should be your choice cause we can use it to access/manipulate SharePoint objects(sites, lists or libraries).

    About adding data in SharePoint list, the demos in this documentation should be helpful:

    https://msdn.microsoft.com/en-us/library/office/ms467435(v=office.14).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by cb39 Wednesday, January 11, 2017 4:58 PM
    Friday, January 6, 2017 5:31 AM
    Moderator

All replies

  • Hi,

    A suggestion is: Add logging statements in your code, which can help to make sure whether the latest data can be retrieved by the code, or whether it is simply a rendering issue.

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, December 22, 2016 8:45 AM
    Moderator
  • where it can be added in above code and how to add?
    • Edited by cb39 Thursday, December 22, 2016 2:09 PM
    Thursday, December 22, 2016 2:08 PM
  • Hi,

    This question not seems to be SharePoint topic, still you can check the below option.

    Could you please compare the development Server's IIS config settings and Production Server IIS config settings ?

    May be the ClientCache control mode has been set to "DisableCache" on your IIS config file as outlined here

    Or in your SharePoint Web application's config file , please check the "<configuration> block any tag that could stop or manage the "Cache" mechanism on the application level.

    Please let us know, how your progressing on it, if you found any other solution please post here, that could be helpful for others.


    Murugesa Pandian | MCPD | MCTS | SharePoint 2010 |

    • Marked as answer by cb39 Wednesday, January 11, 2017 4:59 PM
    Thursday, December 22, 2016 2:10 PM
  •  Thank you,it is a sharepoint webpart using Dev express grid where data is pulling from Database to the grid. The above code is used to pull data and to cache dataset for every 2 hours which didn't worked in production.

    compared both development and production IIS config and web config settings, and they look same. But when i check your article it shows me how to enable caching in IIS. This link also the same. http://aspnetfaq.com/enable-caching-in-iis-to-speed-performance

    what if i do same steps from the above link and set "expire web content" to 1 day. Does it work same as recycling app pool, because when i recycle app po0l in production(dataset cache is working).



    • Edited by cb39 Thursday, December 22, 2016 3:01 PM
    Thursday, December 22, 2016 3:01 PM
  • Hi cb39,

    You can add the logging statements at the end of each method, log out the information(including timestamp) and save in a SharePoint list or in the local drive.

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, December 26, 2016 2:51 AM
    Moderator
  • Hi,

    i tried adding logging statements and saveed to the sharepoint list but it didn't work. can i get the sample code to add in my above methods.



    • Edited by cb39 Tuesday, December 27, 2016 2:17 PM
    Tuesday, December 27, 2016 2:16 PM
  • the simplest way is to log to text file, e.g. to c:\temp folder:

    private static void log(string msg, params object[] args)
    {
        try
        {
            msg = string.Format(msg, args);
            msg = string.Format("{0} (thread id: {1})\t{2}\n", DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss"), Thread.CurrentThread.ManagedThreadId, msg);
            File.AppendAllText("c://temp/log.txt", msg);
        }
        catch
        {
        }
    }
    In order to make it work create c:\temp folder (or whatever you will use in the code) and grant write permissions to Everyone. Then run your code which will write traces and check c:\temp\log.txt.

    Blog - http://sadomovalex.blogspot.com
    Dynamic CAML queries via C# - http://camlex.codeplex.com

    • Marked as answer by cb39 Wednesday, January 11, 2017 4:58 PM
    Tuesday, December 27, 2016 2:53 PM
  • In my case, where do i need to call log method in my code? Basically i want to know, it is refreshed successfully or not.

    • Edited by cb39 Tuesday, December 27, 2016 3:41 PM
    Tuesday, December 27, 2016 3:41 PM
  • Hi,

    For example, you can add the logging statements in the places like this:

    private void Loadtable()
    {
    	ObjectCache cache = MemoryCache.Default;
    	string cacheKey = "RefreshData";
    	DataSet dsreturn = new DataSet();
    
    
    	//logging here
    	Logging(cache.Contains(cacheKey));
    
    
    	if (cache.Contains(cacheKey))
    	{
    		dsreturn = (DataSet)cache.Get(cacheKey);
    		if (dsreturn != null && dsreturn.Tables[0].Rows.Count > 0)
    		{
    			dsreturn.Tables[0].PrimaryKey = new DataColumn[1] { dsreturn.Tables[0].Columns[0] };
    			_ListDS = dsreturn;
    		}
    	}
    	else
    	{
    		dsreturn = LoadListDS();
    		CacheItemPolicy itemPolicy = new CacheItemPolicy();
    		itemPolicy.AbsoluteExpiration = DateTime.Now.AddHours(2.0);
    		cache.Add(cacheKey, dsreturn, itemPolicy);
    	}
    
    
    	//logging here
    	Logging(dsreturn);
    
    }
    
    private DataSet LoadListDS()
    {
    	try
    	{
    		DataSet ds = new DataSet();
    		SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
    		SqlCommand cmd = new SqlCommand("Users", con);
    		SqlDataAdapter da = new SqlDataAdapter(cmd);
    		con.Open();
    		da.Fill(ds);
    		con.Close();
    
    
    		//logging here
    		Logging(ds);
    
    
    		if (ds != null && ds.Tables[0].Rows.Count > 0)
    		{
    			ds.Tables[0].PrimaryKey = new DataColumn[1] { ds.Tables[0].Columns[0] };
    			_ListDS = ds;
    		}
    
    
    		//logging here
    		Logging(dsreturn);
    
    
    		return ds;
    	}
    	catch (Exception ex)
    	{
    		throw new Exception("Error loading grid from db: " + ex.Message);
    	}
    }

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, December 28, 2016 1:53 AM
    Moderator
  • Hi cb39,

    Any progress now?

    Best regards,

    Patrick


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, January 3, 2017 8:25 AM
    Moderator
  • Hi Patrick,

    The Logging statements above is throwing an errors, but i think we have to write a separate method for Logging. Let me know the method for Logging. This code is not working


    private static void Logging(string msg, params object[] args)

    { try { msg = string.Format(msg, args); msg = string.Format("{0} (thread id: {1})\t{2}\n", DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss"), Thread.CurrentThread.ManagedThreadId, msg); File.AppendAllText("c://temp/log.txt", msg); } catch { } }



    • Edited by cb39 Tuesday, January 3, 2017 2:40 PM
    Tuesday, January 3, 2017 2:40 PM
  • Hi,

    When there is a need to access local drive in a server machine, we need elevated privilege.

    Please try something like this:

    SPSecurity.RunWithElevatedPrivileges(delegate ()
    {
        File.AppendAllText(@"c:\temp\log.txt", "log here...");
    });

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, January 4, 2017 1:37 AM
    Moderator
  • Hi Patrick,

    i am not using a Farm solution, just a sandbox solution. It says the name SPSecurity does not exist in current context, i think we can't use SPSecurity for Sandbox solutions. I followed all the previous steps and i can't use Logging method, like Logging (ds) or Logging(dsreturn), since it says the method has some invalid arguements.



    • Edited by cb39 Wednesday, January 4, 2017 2:36 PM
    Wednesday, January 4, 2017 2:36 PM
  • How can i save log statements to the SharePoint list instead of C drive to the above code?



    • Edited by cb39 Wednesday, January 4, 2017 7:08 PM
    Wednesday, January 4, 2017 7:08 PM
  • Hi,

    The “Logging()” method in my reply is a custom method which needs to be built according to what info you may want to log out, also you can name it with another name such as “CustomLogging()”.

    To save logs in SharePoint list, Server Object Model should be your choice cause we can use it to access/manipulate SharePoint objects(sites, lists or libraries).

    About adding data in SharePoint list, the demos in this documentation should be helpful:

    https://msdn.microsoft.com/en-us/library/office/ms467435(v=office.14).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

    Best regards,

    Patrick

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by cb39 Wednesday, January 11, 2017 4:58 PM
    Friday, January 6, 2017 5:31 AM
    Moderator