Save & Reload Grid Settings to Sql Server

[Michael]

I've followed the  tutorials and examples to save the Grid settings by user. I"m using the  GridSettingsPersister.cs class. Everything is working fine using the SessionState.  I have a requirement that a user should be able to save multiple settings and reload them as needed for a report.  Basically saving the report search criteria and display, so when the user comes back in the future they can select a saved report settings name from a combobox and the report will return results in the saved format.  I'm using a Sql Server table to save my named search and display criteria.  I'm having trouble getting the saved grid format to load.  It seems to be saving fine but when I get the value from the combobox_selectedIndex change event (autopostback=true).  The saved settings for the grid aren't applied.
I've followed this example: http://www.telerik.com/help/aspnet-ajax/grdsavingsettingsonperuserbasis.html but I'm missing something when restoring from the database.  What do I need to do to get this work properly?

Aspx:

                <telerik:RadComboBox ID="RadComboBox1" runat="server" EmptyMessage="Select Report Setting" LoadingMessage="Loading..." AutoPostBack="true" OnSelectedIndexChanged="RadComboBox1_SelectedIndexChanged" Skin="Outlook" Width="250px" ></telerik:RadComboBox>
                &nbsp;<telerik:RadTextBox ID="RadTextBox1" runat="server"  Skin="Outlook"></telerik:RadTextBox>&nbsp;
                <asp:Button ID="btnSaveSettings" runat="server" Text="Save Setttings" CommandName="SaveProfile" OnClick="btnSaveSettings_Click" />
            </td>
        </tr>
        <tr valign="top">
            <td><img src="images/spacer.gif" width="15px" alt=""  /></td>
            <td style="height:600px;">
                <telerik:RadGrid ID="ARGrid" runat="server" Width="95%" GridLines="None"
                    AutoGenerateColumns="False" PageSize="13" AllowSorting="True" AllowPaging="True"
                    OnNeedDataSource="ARGrid_NeedDataSource" ShowStatusBar="true" ShowGroupPanel="true" Skin="Outlook" >
                    <MasterTableView DataKeyNames="Userreportid" AllowFilteringByColumn="true" AllowMultiColumnSorting="true" Width="100%" CommandItemDisplay="None" EditMode="InPlace" >
                        <Columns>
                            <telerik:GridBoundColumn UniqueName="Userid" SortExpression="Userid" HeaderText="Userid" DataField="Userid"/>
                            <telerik:GridBoundColumn UniqueName="Fields" SortExpression="Fields" HeaderText="Fields" DataField="Fields"/>
                            <telerik:GridBoundColumn UniqueName="Grouping" SortExpression="Grouping" HeaderText="Grouping" DataField="Grouping"/>
                            <telerik:GridBoundColumn UniqueName="Sorting" SortExpression="Sorting" HeaderText="Sorting" DataField="Sorting"/>
                            <telerik:GridBoundColumn UniqueName="Filtering" SortExpression="Filtering" HeaderText="Filtering" DataField="Filtering"/>
                        </Columns>
                    </MasterTableView>
                    <ClientSettings AllowColumnsReorder="true" AllowDragToGroup="true" ColumnsReorderMethod="Reorder" >
                    </ClientSettings>
                     <GroupingSettings ShowUnGroupButton="true"  />
                </telerik:RadGrid>

Code Behind:

protected string SettingsState

{

get { return (string)Session["_settingsar"] ?? string.Empty; }

set { Session["_settingsar"] = value; }

}

protected void Page_Init(object sender, EventArgs e)

{

if (SettingsState != string.Empty)

{

GridSettingsPersister persister = new GridSettingsPersister(ARGrid);

persister.LoadSettings(SettingsState);

}

}

protected void Page_PreRender(object sender, EventArgs e)

{

GridSettingsPersister persister = new GridSettingsPersister(ARGrid);

SettingsState = persister.SaveSettings();

}

protected void RadComboBox1_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)

{

if (RadComboBox1.SelectedItem != null)

{

List<UserReport> ursettings = UserReport.getByUserid(Master.loggedUser.Userid);

foreach (UserReport ur in ursettings)

{

if (ur.Fields == RadComboBox1.SelectedItem.Text)  //property for grid settings

{

SettingsState = ur.Filtering;  //set sesssion var for the grid settings

}

}

}

}    protected void btnSaveSettings_Click(Object sender, EventArgs e)   {   string settingname = RadTextBox1.Text;       if (RadTextBox1.Text != string.Empty)   {   string grsettings = string.Empty;     GridSettingsPersister persister = new GridSettingsPersister(ARGrid);   grsettings = persister.SaveSettings();   UserReport ursettings = new UserReport();   ursettings.Userid = Master.loggedUser.Userid; ursettings.Fields = RadTextBox1.Text; ursettings.Filtering = grsettings; ursettings.Grouping= "group stuff";   ursettings.save(); BindSavedSettings(); }   }

[Michael]

I added

GridSettingsPersister persister = new GridSettingsPersister(ARGrid);
persister.LoadSettings(SettingsState);

 to the RadComboBox1_SelectedIndexChanged event. However, it seems to lock up the browser for some reason.

So I guess it's back to the drawing board. I need to get this working somehow.

[Michael]

Anyone have any ideas on this?  I really need to get this to work some how.  Any suggestions would be appreciated.  I've tried setting a radtextbox in the client selectindexchange then postback using a buttong but the textbox control isn't available in Page_init.  

Is the Page_init the only place to load the grid settings?

[Veli]

Hi Michael,

As you observed correctly, postback events that you use to load the settings are fired later than Page_Init which you use to apply the settings to RadGrid. So you need to explicitly re-load the settings in the postback event. The approach you have taken:

GridSettingsPersister persister = new GridSettingsPersister(ARGrid);
persister.LoadSettings(SettingsState);

seems correct, though and I wonder what's the reason for the browser freeze with this code. You can check for infinite loops for example.

Alternatively, attached is yet another implementation of the GridSettingsPersister class with claims to be more readable as well as save/load a larger set of grid settings. The public API and usage is the same, so you should not have any problems substituting the class in your project. Check it out.

Best wishes,
Veli
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Michael]

I've finally been able to get back to this after completing the rest of my project. This is the last piece and it's still giving me fits.  I've tried the new GridSettingsPersister class and I'm getting the same results.  I've simplified my implementation to follow the example given in the demos with the addition of saving the settings to a database (a requirement for the project) but I'm still getting a locked up when I load.  It seems to work alright on the first load but if I chose another report setup things get locked up.  I've also noticed both my implementation and the one on the demo page have troubles recallng the grid settings when grouping is involved.   Any additional help would be appreciated.

Thanks,

mike

[Veli]

Hello Michael,

If you are getting freeze-ups with 2 different unrelated implementations of the grid settings persister, then, probably, the issue should be looked for elsewhere. Additionally, the sample project I sent you seems to work OK with grouping, so you may want to check out any differences in the usage of the persister. Generally, you should be restoring settings on Init and saving them on PreRender.

Greetings,
Veli
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Steve]

Telerik,

Is there a way to serialize the state of the grid on the client using javascript? The reason why I ask this, is some of us will bind our grids using JSON data objects and work only with the client-side. I would like to save the state of the grid for each user but call a method on the grid object to serialize the state of the grid to a base64 string. 

So, my question is, is there a way to save the state of the grid to persist all settings using javascript? If not, why?

Thanks

[Tsvetoslav]

Hi Michael,

There is no straightforward way that you can achieve that. You have to code it using the grid's client-side API to extract the necessary information and then convert it to JSON and send it to the server for persistence.

Best wishes,
Tsvetoslav
the Telerik team

[Steve]

Telerik,

Is it possible to bind the grid using JSON in the client, but also create a postback to have the server side read the grid's settings and persist the data on the server side?

Steve

[Tsvetoslav]

Hi Steve,

If you are binding the grid client-side, you are using a web service. In the web service method that returns your json you can send from the client any information you need to persist (filter expressions, sort expression, etc.) and save it to a data-base or other preferred storage mechanism. Then in the pageLoad client-side event you should load those settings again through a web service call. So, everything should happen on the client but, of course, this requires coding through the grid's client-side API.

Regards,
Tsvetoslav
the Telerik team

[Sompop]

Hi there,

I downloaded gridsettingspersister.zip and found out that it uses LINQ which cannot be used with VS2005 or I may have to install some kind of framework. Is there another sample code without LINQ that I can use with VS2005?

Thank you

Sompop

[Veli]

Hello Vincent,

The GridSettingsPersister implementation does not use LINQ. A reference to System.Linq stays by default in any new class definition in .NET 3.5 or newer. You can easily port the persister for .NET20. I have attached a .NET20 compatible version of the persister to this post.

Kind regards,
Veli
the Telerik team

[Sompop]

Hi Veli,

Thank you very much. This is almost exactly what I like. There are three questions after I run the demo app:

1. This one does not have show/hide implementation which is very important. Is there a sample including this feature?
2. I realise that it will save only to ViewState or session. That means everytime I logoff and login, I have to set the settings again. Is there a sample for saving settings to Sql Server like the topic of this post "Save & Reload Grid Settings to Sql Server"?
3. If my application have a dozen or more data grids and if I want to save settings in the database, according to the way you implment, I have to create as many as fields of every single data grid to store the settings. Is that right?

Regards,

Sompop

[Veli]

Hi Vincent,

In the same order of your questions:

1. I am not sure what you mean by "show/hide implementation". Do you mean that RadGrid needs to be shown or hidden depending on the serialized settings, or that detail tables in a hierarchical grid need to be shown or hidden based on these settings? The former case can be implemented by just adding an extra property to the GridSettingsCollection class (e.g. Visible) that you need to save/load in the SaveSettings/LoadSettings methods of the GridSettingsPersister class, respectively. The latter case (with the hierarchical tables) is not supported by the persister.

2. The GridSettingsPersister itself does not impose any limitations as to where to save the serialized settings. In its most general form, settings are serialized in a string. Thus, they can be saved in Session, ViewState, SQL, XML or other persistence medium. Note, however, that if you need to save the serialized settings for long periods of time, you may not be able to restore them after rebuilding your project. The reason for this is the LosFormatter that is used to serialize binary data. The LosFormatter uses the ObjectStateFormatter internally. The latter does not support deserializing data serialized from a previous  version of the assembly that contains the serialized class. The workaround is to use the BinaryFormatter instead. When the BinaryFormatter.AssemblyFormat property is set to Simple, the BinaryFormatter can serialize and deserialize data between different assembly versions. If you want to save settings to SQL, it is advisable that you switch the formatter in the GridSettingsPersister to BinaryFormatter. One caveat to using this approach is that the BinaryFormatter requires a binary serialization permission and cannot be used in Medium Trust under default settings.

3. Your observation is correct. You need to create one record for every grid instance whose settings are saved/loaded from database. You can uniquely identify a RadGrid instance on your page by a key formed by the URL of the page + the UniqueID of the grid. The UniqueID of a server control is unique in the context of an .aspx page. A page, on its side, is identified by a URL. The combination of page URL + Grid UniqueID identifies your grids in the application uniquely.

Greetings,
Veli
the Telerik team

[Sompop]

Hi Veli,

Thank you again. Before reading your answer, I think my initial goal was to reach #2 and #3. After reading, I just want to be able to save settings for show/hide column in the session only because I do not think it will be possible at the moment. I think what I meant was depending on the serialize settings. I understand what you meant by adding an extra property to the GridSettingsCollection class (e.g. Visible). However, I am not sure what data type it should be ... as you can see some of the properties are the followings:

 

private int _pageSize;

 

 

private object[] _groupByStates;

 

 

private object _sortState;

 

 

private string _filterExpression;

 

 

private List<ColumnSetting> _columnSettings;

 

 

private List<ColumnSetting> _autoColumnSettings;

 

If I have to guess, it should be a list of something because show/hide column is not like a pagesize which is only one variable.

In addition, each implementation of LoadSettings() and SaveSettings() are completely different and I don't know which part of the gridview I should call and which function I need to use to cast to be an appropriate type. Please see some of the LoadSettings() and SaveSettings() below.

For LoadSettings()

protected

 

virtual void LoadSortExpressions()

 

{

((

IStateManager)Grid.MasterTableView.SortExpressions).LoadViewState(Settings.SortExpressionsState);

 

}

 

protected virtual void LoadFilterExpression()

 

{

Grid.MasterTableView.FilterExpression = Settings.FilterExpression;

}

 

protected virtual void LoadColumnSettings()

 

{

 

if (Settings.AutoGeneratedColumnSettings.Count > 0)

 

{

Grid.ColumnCreated +=

new GridColumnCreatedEventHandler(Grid_ColumnCreated);

 

}

 

foreach (ColumnSetting colSetting in Settings.ColumnSettings)

 

{

 

GridColumn column = Grid.MasterTableView.GetColumnSafe(colSetting.UniqueName);

 

 

if (column != null)

 

{

SetColumnSettings(

ref column, colSetting);

 

}

}

}

For SaveSettings()

protected

 

virtual void SaveSortExpressions()

 

{

Settings.SortExpressionsState = ((

IStateManager)Grid.MasterTableView.SortExpressions).SaveViewState();

 

}

 

protected virtual void SaveFilterExpression()

 

{

Settings.FilterExpression = Grid.MasterTableView.FilterExpression;

}

 

protected virtual void SaveColumnSettings()

 

{

 

foreach (GridColumn column in Grid.MasterTableView.Columns)

 

{

Settings.ColumnSettings.Add(GetColumnSettings(column));

}

 

foreach (GridColumn column in Grid.MasterTableView.AutoGeneratedColumns)

 

{

Settings.AutoGeneratedColumnSettings.Add(GetColumnSettings(column));

}

It would be appreciated if you can provide me the code of this property and load and save functions for show/hide columns on the RadGrid.

Regards,

Sompop

[Veli]

Column visibility (Display and Visible properties of columns) is supported out-of-the-box by the GridSettingsPersister. Refer to the GetColumnSettings and SetColumnSettings methods in the persister:

private ColumnSetting GetColumnSettings(GridColumn column)
{
    ColumnSetting setting = new ColumnSetting();
    setting.UniqueName = column.UniqueName;
    setting.Width = column.HeaderStyle.Width;
    setting.Visible = column.Visible;
    setting.Display = column.Display;
    setting.OrderIndex = column.OrderIndex;
    setting.CurrentFilterValue = column.CurrentFilterValue;
    setting.CurrentFilterFunction = column.CurrentFilterFunction;

    return setting;

}

private void SetColumnSettings(ref GridColumn column, ColumnSetting setting)
{
    column.Display = setting.Display;
    column.Visible = setting.Visible;
    column.HeaderStyle.Width = setting.Width;
    column.OrderIndex = setting.OrderIndex;
    column.CurrentFilterFunction = setting.CurrentFilterFunction;
    column.CurrentFilterValue = setting.CurrentFilterValue;
}

You can see the Display and Visible properties of all columns are getting extracted and restored, respectively. The Display property of a column specifies the column visibility on the client-side (e.g. a column is always rendered, but may not be visible), while the Visible property controls server visiblity (e.g. a column with Visible="false" is not rendered in the HTML output at all).

Is this not working OK at your site?

Veli
the Telerik team

[Sompop]

Hi Veli,

It is interesting (maybe not for you.) For some reasons, I can right click on header of RadGrid to see a list of menus as follows:

Sort Ascending
Sort Descending
Clear Sorting
Group By
Ungroup
Columns (this is where I can do show/hide columns)

I don't know that and I cannot do that on any RadGrids that I have in my application. If I understand it right, the code that allows context menu is

<

 

MasterTableView EnableHeaderContextMenu="true">

 

 

<Columns>

 

 

<telerik:GridBoundColumn DataField="Name" HeaderText="Name"></telerik:GridBoundColumn>

 

 

<telerik:GridBoundColumn DataField="Date" HeaderText="Date"></telerik:GridBoundColumn>

 

 

</Columns>

 

 

</MasterTableView>

 

I think it works now. Just a little bug on the RadGrid. Say if I uncheck one of the column and it will disappear. If I don't do anything and go other pages and come back, that column will still exist (incorrect). However, if I uncheck that column and then try sorting the RadGrid once before leave the page and comeback, it will be disappeared (correct) when I come back to the page. 

Anyway, I think I can live with that.

Thank you very much Veli,

Regards,

Sompop

[Veli]

Yes, the GridTableView.EnableHeaderContextMenu allows you to use a handy context menu with a few column-related operations.

The bug you are describing - are you observing that when persisting grid settings, or the persister is disabled? When no persister is used, all columns are shown by default on every page load, so going back in the page history with the browser Back button will display all columns.

Veli
the Telerik team

[SIMPLE]

how to do code all above things in mvc4 instead of asp.net...

[SIMPLE]

how to do all cod in mvc4