737 lines
31 KiB
C#
737 lines
31 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Globalization;
|
|
using System.IO.Ports;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using DTS.Common;
|
|
using DTS.Common.Enums;
|
|
using DTS.Common.Enums.Sensors;
|
|
using DTS.Common.Storage;
|
|
using DTS.Common.Utilities.Logging;
|
|
|
|
namespace DTS.Slice.Users.UserSettings
|
|
{
|
|
public abstract class Defaults
|
|
{
|
|
#region Static Methods
|
|
|
|
/// <summary>
|
|
/// this function is designed to create any missing property settings if needed
|
|
/// this might happen if the database table was just created for instance
|
|
/// it will create defaults for all the settings in the db, and also settings for all users
|
|
/// currently it's used by DataPRO.App
|
|
/// </summary>
|
|
internal static void CreateAnyMissingUserSettingPropertyDefaults(Defaults userSetting)
|
|
{
|
|
//get a list of all known properties
|
|
var properties = userSetting.GetType().GetProperties();
|
|
var needToBeAdded = new Dictionary<int, PropertyInfo>();
|
|
|
|
foreach (var property in properties)
|
|
{
|
|
var id = PropertyIdAttribute.GetPropertyId(property);
|
|
needToBeAdded[id] = property;
|
|
}
|
|
//find if the property already exists in the db, if so, no need to add...
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.Text;
|
|
sql.CommandText = "SELECT [PropertyId] from [DefaultProperties]";
|
|
using (var ds = DbOperations.Connection.QueryDataSet(sql))
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
var id = Convert.ToInt32(row["PropertyId"]);
|
|
if (needToBeAdded.ContainsKey(id))
|
|
{
|
|
needToBeAdded.Remove(id);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
//if we have nothing to add, we are all done
|
|
if (needToBeAdded.Values.Count <= 0) return;
|
|
|
|
//otherwise add the defaults
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
var sb = new StringBuilder();
|
|
sb.Append(DbOperations.BEGIN_STATEMENT);
|
|
|
|
using (var needToBeAddedEnumerator = needToBeAdded.GetEnumerator())
|
|
{
|
|
var iIndex = 0;
|
|
while (needToBeAddedEnumerator.MoveNext())
|
|
{
|
|
var currentProperty = needToBeAddedEnumerator.Current.Value;
|
|
sb.AppendFormat(
|
|
"INSERT INTO [DefaultProperties] ([PropertyId],[PropertyName],[DefaultValue]) VALUES (@1_{0}, @2_{0}, @3_{0});",
|
|
iIndex);
|
|
DbOperations.CreateParam(sql, $"@1_{iIndex}", SqlDbType.Int,
|
|
PropertyIdAttribute.GetPropertyId(currentProperty));
|
|
DbOperations.CreateParam(sql, $"@2_{iIndex}", SqlDbType.NVarChar,
|
|
PropertyIdAttribute.GetPropertyIdEnum(currentProperty).ToString());
|
|
DbOperations.CreateParam(sql, $"@3_{iIndex}", SqlDbType.NVarChar,
|
|
GetDefaultValueAsString(currentProperty));
|
|
iIndex++;
|
|
}
|
|
}
|
|
|
|
sb.Append(DbOperations.COMMIT_STATEMENT);
|
|
sql.CommandText = sb.ToString();
|
|
DbOperations.Connection.ExecuteCommand(sql);
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
//finally add settings for any users we have currently
|
|
CreateMissingUserSettingProperties(needToBeAdded.Values.ToArray());
|
|
}
|
|
|
|
/// <summary>
|
|
/// creates missing user settings (for all users)
|
|
/// </summary>
|
|
/// <param name="needToBeAdded"></param>
|
|
internal static void CreateMissingUserSettingProperties(PropertyInfo[] needToBeAdded)
|
|
{
|
|
var userIds = new List<int>();
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandText = "SELECT ID FROM [Users]";
|
|
using (var ds = DbOperations.Connection.QueryDataSet(sql))
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
var id = Convert.ToInt32(row["ID"]);
|
|
if (!userIds.Contains(id))
|
|
{
|
|
userIds.Add(id);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
foreach (var id in userIds)
|
|
{
|
|
CreateMissingUserSettingProperties(id, needToBeAdded);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Inserts missing properties, and updates existing ones for given user
|
|
/// </summary>
|
|
/// <param name="id"></param>
|
|
/// <param name="allProperties"></param>
|
|
internal static void CreateOrUpdateUserSettingProperties(int id, PropertyInfo[] allProperties)
|
|
{
|
|
var existing = new HashSet<int>();
|
|
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.Text;
|
|
sql.CommandText = "SELECT PropertyId FROM [UserProperties] WHERE UserId=@1";
|
|
DbOperations.CreateParam(sql, "@1", SqlDbType.Int, id);
|
|
using (var ds = DbOperations.Connection.QueryDataSet(sql))
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
var propertyId = Convert.ToInt32(row[0]);
|
|
existing.Add(propertyId);
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.Text;
|
|
var index = 0;
|
|
var sb = new StringBuilder();
|
|
sb.Append(DbOperations.BEGIN_STATEMENT);
|
|
|
|
foreach (var property in allProperties)
|
|
{
|
|
var propId = PropertyIdAttribute.GetPropertyId(property);
|
|
if (existing.Contains(propId))
|
|
{
|
|
continue;
|
|
}
|
|
sb.AppendFormat(
|
|
"INSERT INTO [UserProperties] ([UserId],[PropertyId], [PropertyValue]) VALUES (@1_{0},@2_{0},@3_{0});",
|
|
index);
|
|
|
|
DbOperations.CreateParam(sql, $"@1_{index}", SqlDbType.Int, id);
|
|
DbOperations.CreateParam(sql, $"@2_{index}", SqlDbType.Int, propId);
|
|
DbOperations.CreateParam(sql, $"@3_{index}", SqlDbType.NVarChar,
|
|
GetDefaultValueAsString(property));
|
|
|
|
index++;
|
|
}
|
|
|
|
sb.Append(DbOperations.COMMIT_STATEMENT);
|
|
|
|
sql.CommandText = sb.ToString();
|
|
DbOperations.Connection.ExecuteCommand(sql);
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
if (existing.Any())
|
|
{
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
var index = 0;
|
|
var sb = new StringBuilder();
|
|
sb.Append(DbOperations.BEGIN_STATEMENT);
|
|
|
|
foreach (var property in allProperties)
|
|
{
|
|
var propId = PropertyIdAttribute.GetPropertyId(property);
|
|
if (!existing.Contains(propId))
|
|
{
|
|
continue;
|
|
}
|
|
sb.AppendFormat(
|
|
"UPDATE [UserProperties] SET PropertyValue=@1_{0} WHERE UserId=@2_{0} AND PropertyId=@3_{0};",
|
|
index);
|
|
DbOperations.CreateParam(sql, $"@1_{index}", SqlDbType.NVarChar,
|
|
GetUserSettingValue(id, (PropertyEnums.PropertyIds)propId, true) ?? string.Empty);
|
|
DbOperations.CreateParam(sql, $"@2_{index}", SqlDbType.Int, id);
|
|
DbOperations.CreateParam(sql, $"@3_{index}", SqlDbType.Int, propId);
|
|
index++;
|
|
}
|
|
|
|
sb.Append(DbOperations.COMMIT_STATEMENT);
|
|
|
|
sql.CommandText = sb.ToString();
|
|
sql.CommandType = CommandType.Text;
|
|
DbOperations.Connection.ExecuteCommand(sql);
|
|
}
|
|
finally { sql.Connection.Dispose(); }
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// this funtion creates a list of settings for a specific user
|
|
/// this may happen with a new database, it's called by the
|
|
/// CreateAnyMissingUserSettingPropertyDefaults function above
|
|
/// </summary>
|
|
/// <param name="id"></param>
|
|
/// <param name="needToBeAdded"></param>
|
|
internal static void CreateMissingUserSettingProperties(int id, PropertyInfo[] needToBeAdded)
|
|
{
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.Text;
|
|
var index = 0;
|
|
var sb = new StringBuilder();
|
|
sb.Append(DbOperations.BEGIN_STATEMENT);
|
|
|
|
foreach (var property in needToBeAdded)
|
|
{
|
|
sb.AppendFormat(
|
|
"INSERT INTO [UserProperties] ([UserId],[PropertyId], [PropertyValue]) VALUES (@1_{0},@2_{0},@3_{0});",
|
|
index);
|
|
|
|
DbOperations.CreateParam(sql, $"@1_{index}", SqlDbType.Int, id);
|
|
DbOperations.CreateParam(sql, $"@2_{index}", SqlDbType.Int,
|
|
PropertyIdAttribute.GetPropertyId(property));
|
|
DbOperations.CreateParam(sql, $"@3_{index}", SqlDbType.NVarChar,
|
|
GetDefaultValueAsString(property));
|
|
|
|
index++;
|
|
}
|
|
|
|
sb.Append(DbOperations.COMMIT_STATEMENT);
|
|
|
|
sql.CommandText = sb.ToString();
|
|
DbOperations.Connection.ExecuteCommand(sql);
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// get an invariant version of the value for db storage
|
|
/// this is used when we create or insert a new default into the db
|
|
/// </summary>
|
|
/// <param name="property"></param>
|
|
/// <returns></returns>
|
|
internal static string GetDefaultValueAsString(PropertyInfo property)
|
|
{
|
|
if (property == null) return "";
|
|
var attr = (DefaultValueAttribute)property.GetCustomAttribute(typeof(DefaultValueAttribute));
|
|
if (attr.Value is double d)
|
|
{
|
|
return d.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
if (attr.Value is float f)
|
|
{
|
|
return f.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
return attr.Value?.ToString() ?? string.Empty;
|
|
}
|
|
|
|
/// <summary>
|
|
/// gets a collection of all settings for a given user
|
|
/// </summary>
|
|
/// <param name="userId"></param>
|
|
/// <returns></returns>
|
|
protected static Defaults GetUserSettings(int userId, Defaults settings)
|
|
{
|
|
var properties = settings.GetType().GetProperties();
|
|
//first get all properties as filled out by code
|
|
foreach (var property in properties)
|
|
{
|
|
var d = property.GetCustomAttribute<DefaultValueAttribute>();
|
|
if (d != null)
|
|
{
|
|
property.SetValue(settings, d.Value);
|
|
}
|
|
}
|
|
|
|
//next make sure we get all the defaults from the db
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = DbOperationsEnum.StoredProcedure.sp_DefaultPropertiesGet.ToString();
|
|
cmd.Parameters.Add(new SqlParameter("@PropertyId", SqlDbType.Int) { Value = null });
|
|
using (var ds = DbOperations.Connection.QueryDataSet(cmd))
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
var id = Convert.ToInt32(row["PropertyId"]);
|
|
var dv = Convert.ToString(row["DefaultValue"]);
|
|
settings.SetValue(id, dv);
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex) { APILogger.Log("Failed to get Default Property", ex); }
|
|
finally { cmd.Connection.Dispose(); }
|
|
}
|
|
|
|
//finally get the user specific values
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = DbOperationsEnum.StoredProcedure.sp_UserPropertiesGet.ToString();
|
|
cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = userId });
|
|
cmd.Parameters.Add(new SqlParameter("@PropertyId", SqlDbType.Int) { Value = null });
|
|
|
|
using (var ds = DbOperations.Connection.QueryDataSet(cmd))
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
var id = Convert.ToInt32(row["PropertyId"]);
|
|
var value = Convert.ToString(row["PropertyValue"]);
|
|
settings.SetValue(id, value);
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex) { APILogger.Log("Failed to get User Property", ex); }
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
//if a user setting for a defaults type wasn't found, handle it in the inherited class
|
|
return settings;
|
|
}
|
|
|
|
protected static Defaults _allUserSettings = null;
|
|
|
|
/// <summary>
|
|
/// sets a specific user setting in the db
|
|
/// function should handle the invariant nature of values
|
|
/// </summary>
|
|
/// <param name="userId"></param>
|
|
/// <param name="id"></param>
|
|
/// <param name="value"></param>
|
|
public static void SetUserSetting(int userId, PropertyEnums.PropertyIds id, object value)
|
|
{
|
|
var sValue = value.ToString();
|
|
if (value is double d)
|
|
{
|
|
sValue = d.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
else if (value is float f)
|
|
{
|
|
sValue = f.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
try
|
|
{
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = DbOperationsEnum.StoredProcedure.sp_UserPropertiesUpdateInsert.ToString();
|
|
|
|
#region params
|
|
|
|
cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = userId });
|
|
cmd.Parameters.Add(new SqlParameter("@PropertyId", SqlDbType.Int) { Value = (int)id });
|
|
cmd.Parameters.Add(new SqlParameter("@PropertyValue", SqlDbType.NVarChar) { Value = sValue });
|
|
var errorNumberParam =
|
|
new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
|
|
cmd.Parameters.Add(errorNumberParam);
|
|
var errorMessageParam =
|
|
new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
|
|
{
|
|
Direction = ParameterDirection.Output
|
|
};
|
|
cmd.Parameters.Add(errorMessageParam);
|
|
|
|
#endregion params
|
|
|
|
cmd.ExecuteNonQuery();
|
|
if (int.Parse(errorNumberParam.Value.ToString()) != 0)
|
|
{
|
|
//errorMessageParam.Value
|
|
}
|
|
}
|
|
catch (Exception ex) { APILogger.Log("Failed to set User Property", ex); }
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex) { APILogger.LogException(ex); }
|
|
}
|
|
|
|
/// <summary>
|
|
/// inserts a default setting into the db
|
|
/// written for 18028 Remember test id suffix drop down state
|
|
/// this is in place of a migration script and updating the db when there's a new user
|
|
/// setting, instead if a setting is absent we can insert one
|
|
/// </summary>
|
|
public static void InsertUserSetting(PropertyEnums.PropertyIds property, string value)
|
|
{
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText =
|
|
"INSERT INTO [DefaultProperties] ([PropertyId],[PropertyName],[DefaultValue]) VALUES (@1,@2,@3)";
|
|
|
|
cmd.Parameters.Add(new SqlParameter("@1", SqlDbType.Int) { Value = (int)property });
|
|
cmd.Parameters.Add(new SqlParameter("@2", SqlDbType.NVarChar, 255) { Value = property.ToString() });
|
|
cmd.Parameters.Add(new SqlParameter("@3", SqlDbType.NVarChar, 255) { Value = value });
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
APILogger.Log(ex);
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// gets a specific user setting as a bool
|
|
/// </summary>
|
|
/// <param name="userId"></param>
|
|
/// <param name="property"></param>
|
|
/// <param name="defaultValue">optional parameter allowing inserting a setting if absent</param>
|
|
/// <returns></returns>
|
|
public static bool GetUserSettingValueBool(int userId, PropertyEnums.PropertyIds property, bool? defaultValue = false)
|
|
{
|
|
var defaultV = false;
|
|
var val = GetUserSettingValue(userId, property);
|
|
if (null != defaultValue && null == val)
|
|
{
|
|
//if we get here the user supplied a default value for the property
|
|
//and the property is not in the db, so insert it
|
|
InsertUserSetting(property, ((bool)defaultValue).ToString());
|
|
return (bool)defaultValue;
|
|
}
|
|
//if the string is empty or null, it won't convert, so just return the default
|
|
//otherwise return the converted value
|
|
return string.IsNullOrWhiteSpace(val) ? defaultV : Convert.ToBoolean(val);
|
|
}
|
|
|
|
/// <summary>
|
|
/// gets a specific user setting as a double
|
|
/// </summary>
|
|
/// <param name="userId"></param>
|
|
/// <param name="property"></param>
|
|
/// <returns></returns>
|
|
public static double GetUserSettingValueDouble(int userId, PropertyEnums.PropertyIds property)
|
|
{
|
|
return Convert.ToDouble(GetUserSettingValue(userId, property) ?? "0", CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
/// <summary>
|
|
/// gets a specific user setting as an integer
|
|
/// </summary>
|
|
/// <param name="userId"></param>
|
|
/// <param name="property"></param>
|
|
/// <returns></returns>
|
|
public static int GetUserSettingValueInt(int userId, PropertyEnums.PropertyIds property)
|
|
{
|
|
return Convert.ToInt32(GetUserSettingValue(userId, property) ?? "0", CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Gets a specific user setting as a string
|
|
/// </summary>
|
|
/// <param name="userid"></param>
|
|
/// <param name="property"></param>
|
|
/// <param name="defaultValue">optional parameter, if specified and the property is missing will insert the property</param>
|
|
/// <returns></returns>
|
|
public static string GetUserSettingValueString(int userid, PropertyEnums.PropertyIds property, string defaultValue = null)
|
|
{
|
|
return GetUserSettingValue(userid, property, true, defaultValue) ?? string.Empty;
|
|
}
|
|
|
|
/// <summary>
|
|
/// gets a user setting out of the db, this is an internal function used by all the public
|
|
/// single setting accessors.
|
|
/// setting is determined by user settings table, default values table
|
|
/// </summary>
|
|
/// <param name="userId"></param>
|
|
/// <param name="propertyId">numeric id if the property</param>
|
|
/// <param name="dontUseCache">whether to use cache when retrieving property value</param>
|
|
/// <param name="defaultValue">optional parameter, if setting is missing will insert property with this default value</param>
|
|
/// <returns></returns>
|
|
private static string GetUserSettingValue(int userId,
|
|
PropertyEnums.PropertyIds propertyId,
|
|
bool dontUseCache = true,
|
|
string defaultValue = null)
|
|
{
|
|
if (_allUserSettings != null && !dontUseCache)
|
|
{
|
|
var properties = _allUserSettings.GetType().GetProperties();
|
|
foreach (var prop in properties)
|
|
{
|
|
var id = PropertyIdAttribute.GetPropertyId(prop);
|
|
if (id == (int)propertyId) { return GetDefaultValueAsString(prop); }
|
|
}
|
|
}
|
|
//if the user has a specific value set for this property, just return it directly
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = DbOperationsEnum.StoredProcedure.sp_UserPropertiesGet.ToString();
|
|
cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = userId });
|
|
cmd.Parameters.Add(new SqlParameter("@PropertyId", SqlDbType.Int) { Value = propertyId });
|
|
|
|
using (var ds = DbOperations.Connection.QueryDataSet(cmd))
|
|
{
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
return Convert.ToString(ds.Tables[0].Rows[0]["PropertyValue"]);
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex) { APILogger.Log("failed to retrieve user setting, ", ex); }
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
//if there exists a specific default value set for this property, return that
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = DbOperationsEnum.StoredProcedure.sp_DefaultPropertiesGet.ToString();
|
|
cmd.Parameters.Add(new SqlParameter("@PropertyId", SqlDbType.Int) { Value = propertyId });
|
|
|
|
using (var ds = DbOperations.Connection.QueryDataSet(cmd))
|
|
{
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
return Convert.ToString(ds.Tables[0].Rows[0]["DefaultValue"]);
|
|
}
|
|
//there's no value, no default value, setting is unknown
|
|
//if we have a default value go ahead and insert a setting
|
|
else if (null != defaultValue)
|
|
{
|
|
InsertUserSetting(propertyId, defaultValue);
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex) { APILogger.Log("failed to retrieve default setting, ", ex); }
|
|
finally { cmd.Connection.Dispose(); }
|
|
}
|
|
//FB16165: we can get here if the db connection drops. It's been noted in the logs, so don't crash
|
|
return null;
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region methods
|
|
|
|
/// <summary>
|
|
/// sets a specific value given a property id
|
|
/// this is used when deserializing values out of the db
|
|
///
|
|
/// </summary>
|
|
/// <param name="id"></param>
|
|
/// <param name="dv"></param>
|
|
protected abstract void SetValue(int id, string dv);
|
|
|
|
/// <summary>
|
|
/// resets all the settings for a user in the db, using the default settings in the db
|
|
/// </summary>
|
|
/// <param name="userId"></param>
|
|
public void ResetUserSettings(int userId)
|
|
{
|
|
//TODO: move to stored procedure
|
|
|
|
//you can do this with one command with SQL, ie like
|
|
//http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match
|
|
//but since we haven't ditched SQlite yet, lets include support for both by separating it into two statements
|
|
var properties = new List<Tuple<int, string>>();
|
|
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = DbOperationsEnum.StoredProcedure.sp_DefaultPropertiesGet.ToString();
|
|
cmd.Parameters.Add(new SqlParameter("@PropertyId", SqlDbType.Int) { Value = null });
|
|
|
|
using (var ds = DbOperations.Connection.QueryDataSet(cmd))
|
|
{
|
|
properties.AddRange(from DataRow row in ds.Tables[0].Rows
|
|
let id = Convert.ToInt32(row["PropertyId"])
|
|
let value = Convert.ToString(row["DefaultValue"])
|
|
select new Tuple<int, string>(id, value));
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
|
|
foreach (var property in properties)
|
|
{
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = DbOperationsEnum.StoredProcedure.sp_UserPropertiesUpdateInsert.ToString();
|
|
|
|
#region params
|
|
|
|
cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = userId });
|
|
cmd.Parameters.Add(
|
|
new SqlParameter("@PropertyId", SqlDbType.Int) { Value = property.Item1 });
|
|
cmd.Parameters.Add(
|
|
new SqlParameter("@PropertyValue", SqlDbType.NVarChar) { Value = property.Item2 });
|
|
var errorNumberParam =
|
|
new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
|
|
cmd.Parameters.Add(errorNumberParam);
|
|
var errorMessageParam =
|
|
new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
|
|
{
|
|
Direction = ParameterDirection.Output
|
|
};
|
|
cmd.Parameters.Add(errorMessageParam);
|
|
|
|
#endregion params
|
|
|
|
cmd.ExecuteNonQuery();
|
|
if (int.Parse(errorNumberParam.Value.ToString()) != 0)
|
|
{
|
|
//errorMessageParam.Value
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
|
|
/// <summary>
|
|
/// Subcategory example
|
|
/// </summary>
|
|
[TypeConverter(typeof(ExpandableObjectConverter))]
|
|
public class SubCategory1
|
|
{
|
|
public string Property1 { get; set; }
|
|
|
|
public string Property2 { get; set; }
|
|
|
|
public override string ToString()
|
|
{
|
|
return string.Empty;
|
|
}
|
|
}
|
|
|
|
[TypeConverter(typeof(ExpandableObjectConverter))]
|
|
public class SubCategory2
|
|
{
|
|
public string Property3 { get; set; }
|
|
|
|
public override string ToString()
|
|
{
|
|
return string.Empty;
|
|
}
|
|
}
|
|
}
|