Manage dynamic website settings stored in the database

Question

I'm trying to create something to store global site settings on our ASP.NET website - things like site name, Google Analytics account number, facebook url, etc. A site can have several “brands” or “subcategories”, sites related to it, therefore we would also like to be able to put the sitguid column into groups, so the group column — for example, TEST AND PRODUCTION (installed using the web.config application appsetting).

I do not want any of the KEYs to be hardcoded anywhere, but I would like to be able to refer to them as easily as possible in the code, for example. SiteSetting.getSetting ("SiteName") (they can be used in templates (master pages, etc.), which will be created by younger developers)

I would also like to be able to administer existing settings in our admin console and create new settings.

The data type column is for the edit form so that you can use the correct input element, for example. checkbox for bit types, text box for varchar, etc.

SiteSettings Database Table:

[sts_sitGuid] [uniqueidentifier] NOT NULL, -- tells us which site the setting is for [sts_group] [nvarchar](50) NOT NULL, -- used to group settings eg test/live [sts_name] [nvarchar](max) NULL, -- the display name of the setting, for edit forms [sts_alias] [nvarchar](50) NOT NULL, -- the name for the setting [sts_value] [nvarchar](max) NOT NULL, -- the settings value [sts_dataType] [nvarchar](50) NULL, -- indicates the control to render on edit form [sts_ord] [tinyint] NULL, -- The order they will appear in on the admin form 

I partially participate in the fact that this is working at the moment, but I am not happy with the way I did it, and I would like some of them to have tips that could help find the right solution! I'm sure people did it in front of me. (I would share what I still have, but I do not want to distort the answers in any particular way). All I am looking for is an overview of how best to do this without looking for someone to write it for me; )

I did quite a bit of searching both here and Google, and actually did not find what I was looking for, especially the ability to add new settings, as well as edit existing settings.

The system runs on ASP.NET using webforms, everything is written in C # and uses MSSQL 2008.

As always, any help is much appreciated!

EDIT . To clarify, I'm going to explain what I have created so far. I am dead to save all this in SQL, because we don’t want the web.config or other xml files or another database to float, as this will give us more options when we deploy the application to other users.

So far I have the SiteSettings class, this one has the GetSetting method, which I can call using GetSetting ("SettingAlias") to get the value for "SettingAlias" in the database. This class constructor extracts all settings for the current site from the database and stores them in a dictionary, GetSetting reads from this dictionary. I am still satisfied with this whole part.

The part I'm struggling with is spawning an edit form. In the previous version, this used webservice to get / set parameters, and I try to continue to use something similar to saving work, but they were all defined in the code, for example, GoogleAnalyticsID, Sitename, etc .... and each of them had a column in the database the change I am doing is to save these parameters as ROWS (since then it has become easier to add more, no need to change the scheme and the whole sitesettings class). Currently, my SiteSettings class has a SiteSettingsEditForm method that grabs all the information from db, creates a bunch of controls for the form elements, puts them on a temporary page and does it, and then passes the HTML generated to our control system via ajax. This seems wrong and a little awkward, and the reason for posting it here, it’s hard for me to understand how to save this material through a web service, but more importantly, to create a bunch of HTML by executing the page containing the download, the management form just feels like a wrong way to do this is.

So, in general, I (I think) want to write a class to be able to cache and read several rows from the database table, and also give me an edit form (or provide data to something else to create the form), which is dynamic based on the contents of the same database table (for example, where is my column of type “bit.” I want to check the box where it is “text”, I want text input)

Answer 1

Sometimes this problem is easier to visualize if you start with a data model. If you need customization for each row, then it is probably best to store two tables:

Site:

 SiteId SiteKey SiteName
 1 XYGZ4345 Client Site 1
 2 AZT43752 Client Site 2

This will determine the list of sites for which you have a config. I would use SiteKey, as you would put this in your web.config, and it is better to abstract it to a random line or GUID (so that it would be harder to accidentally load another user's configuration), the client may change its name and you will not get confused in the future, as you did not use your old name as a key, etc. etc.

The configuration table itself is also simple if we consider each parameter as a string:

SiteSetting:

 SettingId SiteId SettingName SettingValue
 1 1 Brand KrustyBrand
 2 1 GoogleId MSFTSUX0R
 3 2 Brand ConfigMasters (TM)

You can download the entire configuration quite simply:

 SELECT * FROM SiteSetting INNER JOIN Site ON (SiteSetting.SiteId = Site.SiteId) WHERE Site.SiteKey = 'XYGZ4345' 

Now we have a list of pairs of values ​​that you could save in the class, for example:

 public class SiteSetting { public Site Site { get; set; //Site would just be a simple class consisiting of Id, Key and Name to match the database table } protected Dictionary<String, String> Settings { get; set; } //Simple key value pairs } 

So this is a very simple solution. However, we can take this further - all that needs to be considered:

1) Is it possible to add an environment to this somewhere?

We could add the site on Wednesday

OR

Add the environment to the SiteSetting table. The advantage of this is that you can define enironment = 'ALL' to avoid duplication.

OR

The database loaded by the configuration defines the environment; therefore, you change the connection configuration line in the application configuration. Of course, in order to connect to another environment, you need to change app.config, but you still have to do this to change the client key and / or environment.

2) Add the concept of user-configurable settings - some parameters you want to change, some of which you want to block. The bit column containing "UserDefinable" will allow you to sort this file.

3) Enter the settings.

This is a little trickier. You might have something like:

 PropertyId PropertyName PropertyType Format UserDefined
 1 Brand String NULL 1
 2 DatePresentation DateTime "yyyy-MM-dd" 1

In the Settings table, only the value and PropertyId are defined. The advantage of this is that you can start to increase the information about each setting that you store and reuse this information as the design is more normalized. Then the Settings class is changed as follows:

 public List<PropertyValue> { get; set; } //replacing the dictionary 

The PropertyValue property looks something like this:

 public class PropertyValue { int Id { get; set; } public string Name { get; set; } public string Value { get; set; } public string PVType { get; set; } //Could be an enum public string DisplayFormat { get; set; private string _RawValue; public string Value{ get{ switch(PVType){ case "DateTime": return Convert.ToDateTime(_RawValue).ToString(DisplayFormat); break; case "Double": return Convert.ToDouble(_RawValue).ToString(DisplayFormat); break; default: return _RawValue; } } set{ _RawValue = value; } } } 

Things like the Value method need to be improved to support robust error handling (you can also explore with Convert.ChangeType to simplify the switch block)

This question is as simple or complex as you choose ;-)

editing

Regarding their conservation; a very simple graphical interface allows the user to see all their properties in a tabular format. You might think that lines in which UserDefinable = 0 are read-only, otherwise the user can edit and add lines. You will need to check, especially for duplicate settings names, for example.

The easiest way to do this is to use a DataGrid; a simple layout might look something like this:

And a more complex approach might look something like this

Thus, creating a form is as simple as attaching a database of PropertyValue objects to the grid of your choice.

Answer 2

As you probably discovered, there are many ways you can do this, from Microsoft, recommended up to 100%. You are currently at your own end of the spectrum, which, it seems to me, is usually better.

In any case, basically what you are looking for is a StateBag. Something weakly typed and customizable, which is not very suitable for RDBMSs, but if you already have SQL Server, it will probably be easiest to use this.

Option 1: Redis

However, another option is to run something like Redis to configure the site. You can save it as a collection of key / value pairs in a Redis object, pull it into a JSON object, and then either convert it to a Hashtable, where the search will be very fast, or some other hashed collection. A simple wrapper class around the collection will suffice.

Redis is very lightweight, and in your case you won’t need a lot of configuration or maintenance.

Option 2: SQL Server

As you mentioned, this is actually a pretty good way to do this, rather than querying the database every time, caching a strongly typed collection or Hashtable and expiring every few hours.

If you essentially use what you have, you can save it like this:

 Setting SettingId Int PK, Key NVarchar(50), Name NVarchar(50), Description NVarchar(1000), Type NVarchar(50), Priority Int Configuration SiteId Int, SettingId Int FK(Setting), SettingValue NVarchar(1000) 

Do not store things like live / test / dev in the database. You need to have completely separate databases and servers to distinguish between live / test / dev / stage / QA, etc.

Answer 3

im currenctly does this by creating a class that has all settings as properties, like

 class Setting { GUID siteGuid{get; set;} //other settings } 

then I created a static SettingManager class like this

 public static class SettingManager { private ConcurrentDictionary<GUID,Setting> settings= new ConcurrentDictionary<GUID,Setting>; GetSetting(Guid siteGUID) { settings.TryGet(siteGuid); Lastrefreshed = DateTime.Now; //other code } Private DateTime LastRefreshedOn = DateTime.Now; public void PopulateSetingsDic() { //populate the settings dictionary by getting the values from the database } } 

now, anywhere in your code, the namespace just turns on and use the settings.
u can fill the settings once or every interval in application_start using the lastRefreshedOn variable it will be fast because you have all the settings inside the memory.

also, if you want u to be able to dynamically add settings, you can use ExpandoObject and dynamically add settings using column names from the database or just matching then ull will be able to use the settings by completing ExpandoObject settings to IDictionary<string,object> here string can be converted to GUID string

Edit: - http://zippedtech.blogspot.in/2012/04/dynaminism-in-net-40.html check the link .. I added a new message to solve such problems.

Answer 4

If I understand your question correctly, you are looking for a centralized configuration structure. For configuration and server management, I usually recommend Chef or Puppet, however for ASP.NET I did some quick searches, and it looks like Configuration Service can do the trick for you. The document I'm attached to is a step-by-step guide for the configuration service used in the .NET StockTrader 5 Sample Application .

Answer 5

I would use xml to create a class that can take xpath as your "key"
Ex. MySett.get("//level1/mysetting") or even MySett.get("//mysetting")

where everyone can return the collection, only the first, or what you want. You can even overload. I like xml because it is great flexibility, and also to reduce code, just write a class. Downside, you need to upload your document when you start the application and save it when you finish. Here is an example class in VB code. (c code will still be very similar, I just used vb because it was at that time

 Imports System.Xml Public Class XSett Public xml As XmlDocument Public Overloads Function gett(ByVal xp As String) Return CType(xml.SelectSingleNode(xp), XmlElement).InnerXml 'by using inner xml, you can have either text setting 'or more markup that you might need for another function 'your choice. you could even cast it into another instance 'of this class End Function Public Overloads Function gett(ByVal xp As String, ByVal sel As Integer) Return CType(xml.SelectNodes(xp)(sel), XmlElement).InnerXml 'here, you can have multiple and choose the one you want End Function Public Overloads Sub gett(ByVal xp As String, ByRef col As Collection) Dim i As Integer Dim nds = xml.SelectNodes(xp) For i = 0 To nds.Count - 1 col.Add(CType(nds(i), XmlElement).InnerXml) Next 'Creted an entire collection of elemens. 'i used vb "collection" object, but any collection would do End Sub Public Overloads Sub sett(ByVal ap As String, ByVal name As String, ByVal data As String) 'assume add here. 'ap asks for existing parent element. eg: //guids 'name ask for name of setting element Dim ts = xml.CreateElement(name) ts.InnerXml = data If ap = "" Then 'we assume document element xml.DocumentElement.AppendChild(ts) Else Dim p = CType(xml.SelectSingleNode(ap), XmlElement) p.AppendChild(ts) End If End Sub Public Overloads Sub sett(ByVal xp As String, ByVal sel As Integer, ByVal data As String) 'just change existing setting CType(xml.SelectNodes(xp)(sel), XmlElement).InnerXml = data End Sub 'naturally you can expand infinitely if needed End Class