//----------------------------------------------------------------------------------------------------
/// <summary>
/// Key in return Dictionary is lowercase index name.
/// Does not include sub columns infos.
/// </summary>
private static Dictionary <string, Index> Index__GetAll_InConnection(int connectionId)
{
var rtn = new Dictionary <string, Index>();
string sql = SQLServer_SQLBuilders.BuildSql__Index_SelectAll();
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var index = new Index()
{
ConnectionId = connectionId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
IndexName = reader.Get("IndexName", ""),
Columns = null,
IsUnique = reader.Get("IsUnique", false)
};
string key_IndexName = index.IndexName.ToLower();
if (!rtn.ContainsKey(key_IndexName))
{
rtn.Add(key_IndexName, index);
}
}
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
private static void ASPdb_View__SyncViewsWithProperties_Helper1__GetAndPopulate(int connectionId, List <ASPdb_View> viewsList, Dictionary <string, ASPdb_View> dict)
{
string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Views] where [ConnectionId] = @ConnectionId order by [ViewName]";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@ConnectionId", connectionId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
string db_ViewName = reader.Get("ViewName", "");
string db_Schema = reader.Get("Schema", "");
string db_UniqueNameKey = db_Schema.ToLower().Trim() + "." + db_ViewName.ToLower().Trim();
if (dict.ContainsKey(db_UniqueNameKey))
{
ASPdb_View item = dict[db_UniqueNameKey];
item.ViewId = reader.Get("ViewId", -1);
item.ConnectionId = reader.Get("ConnectionId", -1);
item.ViewName = db_ViewName;
item.Schema = db_Schema;
item.Hide = reader.Get("Hide", false);
}
}
}
}
}
//----------------------------------------------------------------------------------------------------
public static ASPdb_Value[] Get(string key)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new List <ASPdb_Value>();
try
{
string sql = String.Format("select * from [{0}].[ASPdb_Values] where [Key] = @Key order by [ValueId]",
Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@Key", key);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
rtn.Add(new ASPdb_Value()
{
ValueId = reader.Get("ValueId", -1),
Key = reader.Get("Key", ""),
Value = reader.Get("Value", "")
});
}
}
}
}
catch { }
return(rtn.ToArray());
}
//----------------------------------------------------------------------------------------------------
public static SchemaInfo[] Schemas__GetAll(int connectionId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new List <SchemaInfo>();
string connectionString = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(connectionId).GetConnectionString();
string sql = @"
SELECT t1.Schema_Id, t1.principal_id,
t2.Catalog_Name, t2.Schema_Name, t2.Schema_Owner FROM sys.schemas t1
Inner Join INFORMATION_SCHEMA.SCHEMATA t2 on t1.name = t2.SCHEMA_NAME
where t2.SCHEMA_OWNER = 'dbo'
order by t1.name
";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionString, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var schemaInfo = new SchemaInfo()
{
SchemaId = reader.Get("Schema_Id", -1),
PrincipalId = reader.Get("Principal_Id", -1),
CatalogName = reader.Get("Catalog_Name", ""),
SchemaName = reader.Get("Schema_Name", ""),
SchemaOwner = reader.Get("Schema_Owner", "")
};
rtn.Add(schemaInfo);
}
}
}
return(rtn.ToArray());
}
//----------------------------------------------------------------------------------------------------
public static PrimaryKey PrimaryKey__Get(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
PrimaryKey rtn = null;
var colDict = new Dictionary <string, Column>();
foreach (var item in tableStructure.Columns)
{
if (!colDict.ContainsKey(item.ColumnName.ToLower()))
{
colDict.Add(item.ColumnName.ToLower(), item);
}
}
var primaryKeyColumns_List = new List <PrimaryKeyColumn>();
string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Select(tableStructure);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
if (rtn == null)
{
rtn = new PrimaryKey()
{
ConnectionId = tableStructure.ConnectionId,
TableId = tableStructure.TableId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
ConstraintName = reader.Get("ConstraintName", "")
}
}
;
var primaryKeyItem = new PrimaryKeyColumn()
{
ColumnName = reader.Get("ColumnName", ""),
OrdinalPosition = reader.Get("OrdinalPosition", 0)
};
if (colDict.ContainsKey(primaryKeyItem.ColumnName.ToLower()))
{
primaryKeyItem.Identity = colDict[primaryKeyItem.ColumnName.ToLower()].Identity;
}
primaryKeyColumns_List.Add(primaryKeyItem);
}
}
}
if (rtn != null)
{
rtn.Columns = primaryKeyColumns_List.ToArray();
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
/// <summary>
/// Always uses cache (if already stored in cache).
/// Does not perform table sync.
/// </summary>
public static ASPdb_View ASPdb_View__Get(int viewId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var cache = ASPdatabaseNET.Memory.AppCache.Get();
if (cache.ASPdb_View_Dictionary2 != null)
{
if (cache.ASPdb_View_Dictionary2.ContainsKey(viewId) && cache.ASPdb_View_Dictionary2[viewId] != null)
{
return(cache.ASPdb_View_Dictionary2[viewId]); // return from cache if it's there
}
}
ASPdb_View rtn = null;
int connectionId = -1;
string sql = "select [ConnectionId] from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Views] where [ViewId] = @ViewId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@ViewId", viewId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
connectionId = reader.Get("ConnectionId", -1);
}
if (connectionId < 0)
{
return(null);
}
}
}
var aspdb_Views = ASPdb_View__GetAll(connectionId);
for (int i = 0; i < aspdb_Views.Count; i++)
{
if (aspdb_Views[i].ViewId == viewId)
{
rtn = aspdb_Views[i];
i = aspdb_Views.Count + 1;
}
}
// store in cache before returning
if (rtn != null)
{
if (cache.ASPdb_View_Dictionary2 == null)
{
cache.ASPdb_View_Dictionary2 = new Dictionary <int, ASPdb_View>();
}
if (cache.ASPdb_View_Dictionary2.ContainsKey(viewId))
{
cache.ASPdb_View_Dictionary2[viewId] = rtn;
}
else
{
cache.ASPdb_View_Dictionary2.Add(viewId, rtn);
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static int Get_HistoryCount(int tableId, string[] keyValue)
{
int rtn = -1;
string keyValueStr = PrimaryKey_ToString(keyValue);
string keyValueStr_Orig = keyValueStr;
bool keyValueIsTruncated = (keyValueStr.Length > 50);
if (keyValueIsTruncated)
{
keyValueStr = keyValueStr.Substring(0, 50);
}
if (!keyValueIsTruncated)
{
string sql = String.Format("select count(*) as [Count1] from [{0}].[{1}] where [TableId] = @TableId and [KeyValue] = @KeyValue and [KeyValueIsTruncated] = @KeyValueIsTruncated",
Config.SystemProperties.AppSchema, "ASPdb_History");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(History_ConnectionString(), sql))
{
command.AddParameter("@TableId", tableId);
command.AddParameter("@KeyValue", keyValueStr);
command.AddParameter("@KeyValueIsTruncated", false);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
if (reader.Read())
{
rtn = reader.Get("Count1", -1);
}
}
}
else
{
rtn = 0;
string sql = String.Format("select [HistoryId], [HistoryJSON] from [{0}].[{1}] where [TableId] = @TableId and [KeyValue] = @KeyValue and [KeyValueIsTruncated] = @KeyValueIsTruncated",
Config.SystemProperties.AppSchema, "ASPdb_History");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(History_ConnectionString(), sql))
{
command.AddParameter("@TableId", tableId);
command.AddParameter("@KeyValue", keyValueStr);
command.AddParameter("@KeyValueIsTruncated", true);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
if (reader.Read())
{
try
{
int historyId = reader.Get("HistoryId", -1);
var historyJsonObj = (new JavaScriptSerializer()).Deserialize <HistoryJsonObj>(reader.Get("HistoryJSON", ""));
if (historyJsonObj.KeyValue == keyValueStr_Orig)
{
rtn++;
}
}
catch { }
}
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static UserInfo Login(string username, string password)
{
if (!Subscription.SubscriptionAppState.ValidateActiveSubscribers())
{
throw new Exception("Validation Error");
}
string errorMsg = "Invalid Login Credentials";
try
{
string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] where [Username] = @Username and [Active] = 1";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@Username", username);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
if (BCrypt.Net.BCrypt.Verify(password, reader.Get("Password", "")))
{
var userSession = GetUser();
userSession.UserInfo = new UserInfo()
{
UserId = reader.Get("UserId", -1),
Username = reader.Get("Username", ""),
Email = reader.Get("Email", ""),
FirstName = reader.Get("FirstName", ""),
LastName = reader.Get("LastName", ""),
Active = reader.Get("Active", false),
IsAdmin = reader.Get("IsAdmin", false)
};
userSession.Impersonation_IsAllowed = userSession.UserInfo.IsAdmin;
SaveLastLoginTime(userSession.UserInfo.UserId);
userSession.UserInfo.AllPermissions = LoadAllPermissions(userSession.UserInfo.UserId, userSession.UserInfo.IsAdmin);
if (userSession.Impersonation_IsAllowed)
{
userSession.Impersonation_ActualUser = userSession.UserInfo;
}
return(userSession.UserInfo);
}
}
}
}
}
catch (Exception exc) { errorMsg = "Error in Login"; ASPdb.Framework.Debug.RecordException(exc); }
throw new Exception(errorMsg);
}
//----------------------------------------------------------------------------------------------------
public static Index[] Index__Get(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new Dictionary <string, Index>(); // key : indexName_L
string sql = SQLServer_SQLBuilders.BuildSql__Index_Select(tableStructure);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
string schema = reader.Get("Schema", "");
string tableName = reader.Get("TableName", "");
string indexName = reader.Get("IndexName", "");
string indexName_L = indexName.ToLower();
bool isUnique = reader.Get("IsUnique", false);
int columnId = reader.Get("ColumnId", 0);
string columnName = reader.Get("ColumnName", "");
bool isDescending = reader.Get("IsDescending", false);
Index index;
if (rtn.ContainsKey(indexName_L))
{
index = rtn[indexName_L];
}
else
{
index = new Index();
rtn.Add(indexName_L, index);
index.ConnectionId = tableStructure.ConnectionId;
index.Schema = schema;
index.TableName = tableName;
index.IndexName = indexName;
index.IsUnique = isUnique;
index.Columns = new IndexColumn[0];
}
var indexColumnsList = index.Columns.ToList();
var indexColumn = new IndexColumn()
{
ColumnId = columnId,
ColumnName = columnName,
SortDirection = IndexColumn.E_SortTypes.Ascending
};
if (isDescending)
{
indexColumn.SortDirection = IndexColumn.E_SortTypes.Descending;
}
indexColumnsList.Add(indexColumn);
index.Columns = indexColumnsList.ToArray();
}
}
}
return(rtn.Values.ToArray());
}
//----------------------------------------------------------------------------------------------------
public static DefaultConstraint DefaultConstraint__Get(Column column)
{
AjaxService.ASPdatabaseService.GetSetVal();
var dict = new Dictionary <string, DefaultConstraint>();
string sql = SQLServer_SQLBuilders.BuildSql__DefaultConstraint_Select(column);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(column.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
string columnName = reader.Get("ColumnName", "");
string columnName_L = columnName.ToLower();
var defaultConstraint = new DefaultConstraint()
{
ConnectionId = column.ConnectionId,
Schema = column.Schema,
TableName = column.TableName,
ColumnName = reader.Get("ColumnName", ""),
DefaultConstraintName = reader.Get("DefaultConstraintName", ""),
DefaultDefinition = reader.Get("DefaultDefinition", "")
};
if (!dict.ContainsKey(columnName_L))
{
dict.Add(columnName_L, defaultConstraint);
}
}
}
}
if (dict.ContainsKey(column.ColumnName.ToLower()))
{
return(dict[column.ColumnName.ToLower()]);
}
else
{
return(null);
}
}
//----------------------------------------------------------------------------------------------------
public static bool SetImpersonationUser(int userId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var userSession = GetUser();
if (userSession != null && userSession.IsLoggedIn && userSession.Impersonation_IsAllowed && userSession.Impersonation_IsOn)
{
string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] where [UserId] = @UserId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@UserId", userId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
userSession.UserInfo = new UserInfo()
{
UserId = reader.Get("UserId", -1),
Username = reader.Get("Username", ""),
Email = reader.Get("Email", ""),
FirstName = reader.Get("FirstName", ""),
LastName = reader.Get("LastName", ""),
IsAdmin = reader.Get("IsAdmin", false)
};
userSession.UserInfo.AllPermissions = LoadAllPermissions(userSession.UserInfo.UserId, userSession.UserInfo.IsAdmin);
}
}
}
return(true);
}
return(false);
}
//----------------------------------------------------------------------------------------------------
private static void ASPdb_Connection__CheckForDuplicateConnectionName(ASPdb_Connection connectionInfo)
{
bool isADuplicate = false;
string sql = "select [ConnectionId], [ConnectionName] from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] where SiteId = @SiteId and ConnectionName = @ConnectionName";
try
{
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@SiteId", connectionInfo.SiteId);
command.AddParameter("@ConnectionName", connectionInfo.ConnectionName);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
int connectionId = reader.Get("ConnectionId", -1);
string connectionName = reader.Get("ConnectionName", "").Trim().ToLower();
if (connectionInfo.ConnectionName.Trim().ToLower() == connectionName)
{
if (connectionInfo.ConnectionId != connectionId)
{
isADuplicate = true;
}
else if (connectionId <= 0)
{
isADuplicate = true;
}
}
}
}
}
}
catch { }
if (isADuplicate)
{
throw new Exception(String.Format("The Database Connection Name \"{0}\" already exists." +
"\n\nPlease choose a different name.", connectionInfo.ConnectionName.Trim()));
}
}
//----------------------------------------------------------------------------------------------------
private static void ASPdb_Connection__SaveInsert(ASPdb_Connection connectionInfo)
{
ASPdb_Connection__CheckForDuplicateConnectionName(connectionInfo);
int?userId = null;
try { userId = Users.UserSessionLogic.GetUser().UserInfo.UserId; } catch { }
string sql = @" insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections]
(SiteId, ConnectionName, Active, DateTimeCreated)
values (@SiteId, @ConnectionName, @Active, @DateTimeCreated)";
if (userId.HasValue)
{
sql = @" insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections]
(SiteId, ConnectionName, Active, DateTimeCreated, CreatedByUserId)
values (@SiteId, @ConnectionName, @Active, @DateTimeCreated, @CreatedByUserId)";
}
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@SiteId", connectionInfo.SiteId);
command.AddParameter("@ConnectionName", connectionInfo.ConnectionName);
command.AddParameter("@Active", false);
command.AddParameter("@DateTimeCreated", DateTime.Now);
if (userId.HasValue)
{
command.AddParameter("@CreatedByUserId", userId);
}
command.Command.ExecuteNonQuery();
}
sql = "select [ConnectionId] from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] where [SiteId] = @SiteId and [ConnectionName] = @ConnectionName";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@SiteId", connectionInfo.SiteId);
command.AddParameter("@ConnectionName", connectionInfo.ConnectionName);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
connectionInfo.ConnectionId = reader.Get("ConnectionId", -1);
}
}
}
if (connectionInfo.ConnectionId < 0)
{
throw new Exception("Error while attempting to save new database connection. Please check connections list and try again.");
}
ASPdb_Connection__SaveUpdate(connectionInfo);
}
//----------------------------------------------------------------------------------------------------
public static UserInfo[] GetImpersonationList()
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new List <UserInfo>();
var userSession = GetUser();
if (userSession.Impersonation_ActualUser == null)
{
return(null);
}
var actualUser = userSession.Impersonation_ActualUser;
rtn.Add(new UserInfo()
{
UserId = actualUser.UserId,
LastName = "You",
FirstName = "",
Username = actualUser.Username,
Active = actualUser.Active,
Email = actualUser.Email
});
string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] where [UserId] > -1 order by [Active] desc, [LastName], [FirstName], [Username]";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var userInfo = new UserInfo()
{
UserId = reader.Get("UserId", -1),
Username = reader.Get("Username", ""),
FirstName = reader.Get("FirstName", ""),
LastName = reader.Get("LastName", ""),
Email = reader.Get("Email", ""),
Active = reader.Get("Active", false)
};
if (userInfo.UserId != actualUser.UserId)
{
rtn.Add(userInfo);
}
}
}
}
return(rtn.ToArray());
}
//----------------------------------------------------------------------------------------------------
public static void SavePassword(int userId, string oldPassword, string newPassword)
{
try
{
string sql = String.Format("select * from [{0}].[ASPdb_Users] where [UserId] = @UserId", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@UserId", userId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
reader.Read();
if (!BCrypt.Net.BCrypt.Verify(oldPassword, reader.Get("Password", "")))
{
throw new Exception("");
}
}
}
}
catch { throw new Exception("Incorrect Old Password"); }
SavePassword(userId, newPassword);
}
//----------------------------------------------------------------------------------------------------
/// <summary>
/// Key == lowercase ConstraintName.
/// Value == ConstraintName.
/// </summary>
public static Dictionary <string, string> Constraint__GetAll_InConnection(int connectionId)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new Dictionary <string, string>();
string sql = SQLServer_SQLBuilders.BuildSql__Constraint_SelectAll();
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
string constraintName = reader.Get("ConstraintName", "");
string constraintName_L = constraintName.ToLower();
if (!rtn.ContainsKey(constraintName_L))
{
rtn.Add(constraintName_L, constraintName);
}
}
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static List <BaseTableInfo> ASPdb_Table__GetRawList_NoCache(ASPdb_Connection aspdb_Connection)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new List <BaseTableInfo>();
if (aspdb_Connection.E_ConnectionType == ASPdb_Connection.Enum_ConnectionTypes.SQLServer ||
aspdb_Connection.E_ConnectionType == ASPdb_Connection.Enum_ConnectionTypes.SQLServerAzure)
{
string sql = @"
select
t2.schema_id,
t2.[name] [SchemaName],
t1.[name] [TableName],
t1.object_id,
t1.create_date,
t1.modify_date
from sys.tables t1
inner join
sys.schemas t2 on t1.schema_id = t2.schema_id
order by t2.[name], t1.[name]
";
//sql = sql.Replace("[", "`").Replace("]", "`");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(aspdb_Connection.GetConnectionString(), sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
DateTime?nullDate = null;
while (reader.Read())
{
var item = new BaseTableInfo();
item.SchemaId = reader.Get("schema_id", "");
item.SchemaName = reader.Get("SchemaName", "");
item.TableName = reader.Get("TableName", "");
item.ObjectId = reader.Get("object_id", "");
item.CreateDate = reader.Get("create_date", nullDate);
item.ModifyDate = reader.Get("modify_date", nullDate);
rtn.Add(item);
}
}
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static GroupInfo GetGroup(int groupId)
{
GroupInfo rtn = null;
try
{
string sql = "";
if (groupId >= 0)
{
sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UserGroups] where [GroupId] = @GroupId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@GroupId", groupId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
rtn = new GroupInfo()
{
GroupId = reader.Get("GroupId", -1),
GroupName = reader.Get("GroupName", ""),
Active = reader.Get("Active", false),
TimeCreated_Str = reader.Get("TimeCreated", ""),
Permissions = new Permission[0]
}
}
;
}
}
}
else
{
rtn = new GroupInfo()
{
GroupId = -1, Active = true
}
};
if (rtn != null)
{
if (!String.IsNullOrEmpty(rtn.TimeCreated_Str))
{
rtn.TimeCreated_Str = DateTime.Parse(rtn.TimeCreated_Str).ToString("M/dd/yy h:mm tt");
}
var allPermissions = new Dictionary <string, Permission>();
sql = @"select
C.ConnectionId, C.ConnectionName, T.TableId, T.[Schema], T.TableName
from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] C inner join [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Tables] T on C.ConnectionId = T.ConnectionId
where C.Active = 1 and T.Hide = 0
order by C.ConnectionName, T.[Schema], T.TableName";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var permission = new Permission()
{
GroupId = groupId,
ConnectionId = reader.Get("ConnectionId", -1),
ConnectionName = reader.Get("ConnectionName", ""),
TableId = reader.Get("TableId", -1),
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", "")
};
string connection_Key = "C_" + permission.ConnectionId;
string schema_Key = "S_" + permission.ConnectionId + "_[" + permission.Schema + "]";
string table_Key = "T_" + permission.ConnectionId + "_[" + permission.Schema + "]_[" + permission.TableId + "]";
if (!allPermissions.ContainsKey(connection_Key))
{
allPermissions.Add(connection_Key, new Permission(Permission.PermissionTypes.Connection, permission.PermissionId, groupId)
{
ConnectionId = permission.ConnectionId,
ConnectionName = permission.ConnectionName,
Schema = "",
TableId = -1,
TableName = ""
});
}
if (!allPermissions.ContainsKey(schema_Key))
{
allPermissions.Add(schema_Key, new Permission(Permission.PermissionTypes.Schema, permission.PermissionId, groupId)
{
ConnectionId = permission.ConnectionId,
ConnectionName = permission.ConnectionName,
Schema = permission.Schema,
TableId = -1,
TableName = ""
});
}
if (!allPermissions.ContainsKey(table_Key))
{
allPermissions.Add(table_Key, new Permission(Permission.PermissionTypes.Table, permission.PermissionId, groupId)
{
ConnectionId = permission.ConnectionId,
ConnectionName = permission.ConnectionName,
Schema = permission.Schema,
TableId = permission.TableId,
TableName = permission.TableName
});
}
}
}
}
sql = @"
select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
where [GroupId] = @GroupId
order by [ConnectionId], [Schema], [TableId]";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@GroupId", groupId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var permission = new Permission()
{
GroupId = groupId,
PermissionId = reader.Get("PermissionId", -1),
PermissionType = Permission.PermissionTypes.NotSet,
ConnectionId = reader.Get("ConnectionId", -1),
Schema = reader.Get("Schema", ""),
TableId = reader.Get("TableId", -1),
View = reader.Get("View", false),
Edit = reader.Get("Edit", false),
Insert = reader.Get("Insert", false),
Delete = reader.Get("Delete", false),
};
permission.Set_PermissionType(reader.Get("PermissionType", "N"));
string key = permission.Get_UniqueKey();
if (key != null && allPermissions.ContainsKey(key))
{
allPermissions[key].PermissionId = permission.PermissionId;
allPermissions[key].View = permission.View;
allPermissions[key].Edit = permission.Edit;
allPermissions[key].Insert = permission.Insert;
allPermissions[key].Delete = permission.Delete;
}
}
}
}
rtn.Permissions = (from r in allPermissions.Values where r.PermissionType == Permission.PermissionTypes.Connection orderby r.ConnectionName select r).ToArray();
foreach (var item_Connection in rtn.Permissions)
{
item_Connection.SubPermissions = (from r in allPermissions.Values
where r.PermissionType == Permission.PermissionTypes.Schema &&
r.ConnectionId == item_Connection.ConnectionId
select r).ToArray();
foreach (var item_Schema in item_Connection.SubPermissions)
{
item_Schema.SubPermissions = (from r in allPermissions.Values
where r.PermissionType == Permission.PermissionTypes.Table &&
r.ConnectionId == item_Connection.ConnectionId &&
r.Schema == item_Schema.Schema
select r).ToArray();
}
}
}
}
catch (Exception exc) { ASPdb.Framework.Debug.RecordException(exc); }
if (rtn == null)
{
throw new Exception("GroupId not found.");
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static UsersMenuInfo GetMenuInfo()
{
if (!ASPdatabaseNET.Subscription.SubscriptionAppState.ValidateActiveSubscribers())
{
throw new Exception("Validation Error");
}
var rtn = new UsersMenuInfo();
rtn.UserSubscriptions_Total = ASPdatabaseNET.Subscription.SubscriptionAppState.GetSubscribersCount();
//rtn.UserSubscriptions_Active = 7;
rtn.UserSubscriptions_Message = "User Subscriptions Message";
var users_Active = new List <UsersMenuItem>();
var users_Inactive = new List <UsersMenuItem>();
var groups_Active = new List <UsersMenuItem>();
var groups_Inactive = new List <UsersMenuItem>();
string sql = String.Format(@"
select [UserId], [Username], [FirstName], [LastName], [Active]
from [{0}].[ASPdb_Users]
order by [Active], [LastName], [FirstName], [Username]", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var item = new UsersMenuItem()
{
Id = reader.Get("UserId", -1), Active = reader.Get("Active", false), MenuType = UsersMenuItem.MenuTypes.User
};
string firstName = reader.Get("FirstName", "");
string lastName = reader.Get("LastName", "");
string username = reader.Get("Username", "?");
if (firstName.Length > 0 && lastName.Length > 0)
{
item.DisplayName = String.Format("<b>{0}, {1}</b> <br />({2})", lastName, firstName, username);
}
else //if(firstName.Length > 0 || lastName.Length > 0)
{
item.DisplayName = String.Format("<b>{0} {1}</b> <br />({2})", lastName, firstName, username);
}
//else
// item.DisplayName = String.Format("({2})", lastName, firstName, username);
if (item.Active)
{
users_Active.Add(item);
}
else
{
users_Inactive.Add(item);
}
}
}
}
sql = "select [GroupId], [GroupName], [Active] from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UserGroups] order by [Active], [GroupName]";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var item = new UsersMenuItem()
{
Id = reader.Get("GroupId", -1), DisplayName = reader.Get("GroupName", "?"), Active = reader.Get("Active", false), MenuType = UsersMenuItem.MenuTypes.Group
};
if (item.Active)
{
groups_Active.Add(item);
}
else
{
groups_Inactive.Add(item);
}
}
}
}
rtn.Users_Active = users_Active.ToArray();
rtn.Users_Inactive = users_Inactive.ToArray();
rtn.Groups_Active = groups_Active.ToArray();
rtn.Groups_Inactive = groups_Inactive.ToArray();
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static UserInfo GetUser(int userId)
{
UserInfo rtn = null;
try
{
string sql = "";
if (userId >= 0)
{
sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_Users] where [UserId] = @UserId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@UserId", userId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
rtn = new UserInfo()
{
UserId = reader.Get("UserId", -1),
Username = reader.Get("Username", ""),
FirstName = reader.Get("FirstName", ""),
LastName = reader.Get("LastName", ""),
Email = reader.Get("Email", ""),
Active = reader.Get("Active", false),
TimeCreated_Str = reader.Get("TimeCreated", ""),
LastLoginTime_Str = reader.Get("LastLoginTime", ""),
RequirePasswordReset = reader.Get("RequirePasswordReset", false),
IsAdmin = reader.Get("IsAdmin", false),
UserGroups = new UserToGroup_Assignment[0]
}
}
;
}
}
}
else
{
rtn = new UserInfo()
{
UserId = -1, Active = true
}
};
if (rtn != null)
{
if (!String.IsNullOrEmpty(rtn.TimeCreated_Str))
{
rtn.TimeCreated_Str = DateTime.Parse(rtn.TimeCreated_Str).ToString("M/dd/yy h:mm tt");
}
if (!String.IsNullOrEmpty(rtn.LastLoginTime_Str))
{
rtn.LastLoginTime_Str = DateTime.Parse(rtn.LastLoginTime_Str).ToString("M/dd/yy h:mm tt");
}
var userGroups = new Dictionary <int, UserToGroup_Assignment>();
sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UserGroups] where [Active] = 1 order by [GroupName]";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
int groupId = reader.Get("GroupId", -1);
if (!userGroups.ContainsKey(groupId))
{
userGroups.Add(groupId, new UserToGroup_Assignment()
{
GroupId = groupId,
GroupName = reader.Get("GroupName", ""),
IsMember = false,
UserId = rtn.UserId
});
}
}
}
}
sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UsersToGroups] where [UserId] = @UserId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@UserId", userId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
int groupId = reader.Get("GroupId", -1);
if (groupId >= 0 && userGroups.ContainsKey(groupId))
{
userGroups[groupId].IsMember = true;
}
}
}
}
rtn.UserGroups = userGroups.Values.ToArray();
}
}
catch { }
if (rtn == null)
{
throw new Exception("UserId not found.");
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static void Save(MyAccountInfo myAccountInfo)
{
var user = ASPdatabaseNET.Users.UserSessionLogic.GetUserSession_ForClient().UserInfo;
if (user.UserId != myAccountInfo.UserId)
{
throw new Exception("Invalid user info.");
}
if (String.IsNullOrEmpty(myAccountInfo.FirstName))
{
throw new Exception("Please enter your first name.");
}
if (String.IsNullOrEmpty(myAccountInfo.LastName))
{
throw new Exception("Please enter your last name.");
}
if (String.IsNullOrEmpty(myAccountInfo.Email) && myAccountInfo.Email.Length < 5 && !myAccountInfo.Email.Contains("@"))
{
throw new Exception("Please enter your email address.");
}
try
{
string sql = String.Format(@"
update [{0}].[ASPdb_Users]
set [FirstName] = @FirstName
,[LastName] = @LastName
,[Email] = @Email
where [UserId] = @UserId
", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@FirstName", myAccountInfo.FirstName);
command.AddParameter("@LastName", myAccountInfo.LastName);
command.AddParameter("@Email", myAccountInfo.Email);
command.AddParameter("@UserId", myAccountInfo.UserId);
command.ExecuteNonQuery();
}
sql = String.Format("select * from [{0}].[ASPdb_Users] where [UserId] = @UserId", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@UserId", myAccountInfo.UserId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
user.FirstName = reader.Get("FirstName", "");
user.LastName = reader.Get("LastName", "");
user.Email = reader.Get("Email", "");
}
}
}
}
catch (Exception exc)
{
ASPdb.Framework.Debug.RecordException(exc);
throw new Exception("Error while trying to save account info.");
}
}
//----------------------------------------------------------------------------------------------------
public static InstallInfo GetInstallState()
{
var rtn = new InstallInfo()
{
InstallState = InstallInfo.InstallStates.NotSet
};
rtn.AboutPageInfo = UI.PageParts.About.Backend.AboutPageLogic.Get();
string exc_Message = "";
try { rtn.ConnectionString = ConfigurationManager.ConnectionStrings["ASPdb_AppData"].ConnectionString; }
catch (Exception exc) { ASPdb.Framework.Debug.RecordException(exc); }
if (String.IsNullOrEmpty(rtn.ConnectionString))
{
rtn.InstallState = InstallInfo.InstallStates.NoConnectionString;
}
else
{
try
{
rtn.InstallState = InstallInfo.InstallStates.Installed;
var tableNames = new string[]
{
"ASPdb_Connections",
"ASPdb_Permissions",
"ASPdb_Tables",
"ASPdb_UserGroups",
"ASPdb_Users",
"ASPdb_UsersToGroups",
"ASPdb_Values"
};
string sql = "";
foreach (string table in tableNames)
{
sql = String.Format("select top 1 * from [{0}].[{1}];", Config.SystemProperties.AppSchema, table);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionString, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { if (reader.Read())
{
}
}
}
}
int usersCount = 0;
sql = String.Format("select count(*) as [Count1] from [{0}].[ASPdb_Users] where Len([Password]) > 0", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionString, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
usersCount = reader.Get("Count1", 0);
}
}
}
if (usersCount < 1)
{
throw new Exception("Users table is empty.");
}
}
catch (Exception exc)
{
ASPdb.Framework.Debug.RecordException(exc);
string exc_L = exc.Message.ToLower();
if (exc_L.Contains("cannot open database") || exc_L.Contains("login failed"))
{
rtn.InstallState = InstallInfo.InstallStates.CannotConnectToDB;
}
else
{
rtn.InstallState = InstallInfo.InstallStates.DatabaseNotReady;
}
exc_Message = exc.Message; // +"<br />" + exc.StackTrace;
//throw exc;
}
}
var cache = Memory.AppCache.Get();
if (cache.AnyData.ContainsKey(AppKey_IsInstalled))
{
cache.AnyData.Remove(AppKey_IsInstalled);
}
if (rtn.InstallState == InstallInfo.InstallStates.Installed)
{
cache.AnyData.Add(AppKey_IsInstalled, "true");
}
rtn.ResponseMsg = rtn.InstallState.ToString();
switch (rtn.InstallState)
{
case InstallInfo.InstallStates.NoConnectionString:
rtn.ResponseMsg = "Connection string not found.";
break;
case InstallInfo.InstallStates.CannotConnectToDB:
rtn.ResponseMsg = "Could not connect to database. <hr />" + exc_Message;
break;
case InstallInfo.InstallStates.DatabaseNotReady:
break;
case InstallInfo.InstallStates.Installed:
break;
}
ASPdb.Framework.Debug.WriteLine("002a..." + rtn.InstallState.ToString());
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static AppPropertiesInfo Get(int tableId, bool useCache)
{
AppPropertiesInfo rtn = null;
var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(tableId, false, useCache, false);
try
{
string sql = String.Format(@"
select *
from [{0}].[ASPdb_Tables]
where [TableId] = @TableId", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@TableId", tableId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
if (reader.Read())
{
rtn = ASPdb.Ajax.AjaxHelper.New.FromJson <AppPropertiesInfo>(reader.Get("AppProperties", ""));
}
}
}
catch { }
if (rtn == null)
{
rtn = new AppPropertiesInfo()
{
Columns = new AppPropertiesItem[0]
}
}
;
var dictJson = new DictStringKey <AppPropertiesItem>();
var dictOut = new DictStringKey <AppPropertiesItem>();
foreach (var item in tableStructure.Columns)
{
if (dictOut.DoesNot_ContainKey(item.ColumnName))
{
dictOut.Insert(item.ColumnName, new AppPropertiesItem()
{
Index = item.OrdinalPosition,
ColumnName = item.ColumnName.Trim(),
DataType_Name = item.DataType_Name,
IsPrimaryKey = item.IsPrimaryKey,
IsIdentity = item.IsIdentity,
AppColumnType = AppPropertiesItem.AppColumnTypes.Default,
AdditionalInfo = ""
});
}
}
foreach (var item in rtn.Columns)
{
dictJson.Insert(item.ColumnName, item);
}
foreach (var key in dictOut.TheDictionary.Keys)
{
if (dictJson.ContainsKey(key))
{
var item1 = dictOut.Get(key);
var item2 = dictJson.Get(key);
item1.AppColumnType = item2.AppColumnType;
}
}
var dropdownDict = new DictStringKey <DropdownList>();
if (rtn.DropdownListItems != null)
{
foreach (var item in rtn.DropdownListItems)
{
dropdownDict.Insert(item.ColumnName, item);
}
}
foreach (var item in dictOut.TheDictionary.Values)
{
item.AppColumnType_Str = item.AppColumnType.ToString();
if (dropdownDict.ContainsKey(item.ColumnName) && item.AppColumnType == AppPropertiesItem.AppColumnTypes.DropdownList)
{
item.AdditionalInfo = "(" + dropdownDict.Get(item.ColumnName).Items.Length + ")";
}
}
rtn.Columns = (from o in dictOut.TheDictionary.Values orderby o.Index select o).ToArray();
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static bool ValidateActiveSubscribers()
{
AjaxService.ASPdatabaseService.GetSetVal();
try
{
bool isFreeAccount = false;
int subscribersCount = GetSubscribersCount();
if (subscribersCount < 1)
{
isFreeAccount = true;
subscribersCount = 1;
}
string sql = String.Format(@"
select [UserId], [Username], [Active], [IsAdmin]
from [{0}].[ASPdb_Users] where [Active] = 1 order by [UserId]", Config.SystemProperties.AppSchema);
var list = new List <Users.UserInfo>();
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
list.Add(new Users.UserInfo()
{
UserId = reader.Get("UserId", -1),
Username = reader.Get("Username", ""),
Active = reader.Get("Active", false),
IsAdmin = reader.Get("IsAdmin", false)
});
}
}
}
string userIds_ToDeactivate = "";
if (list.Count > subscribersCount)
{
for (int i = subscribersCount; i < list.Count; i++)
{
userIds_ToDeactivate += list[i].UserId + ", ";
}
}
if (userIds_ToDeactivate != "")
{
userIds_ToDeactivate = userIds_ToDeactivate.Substring(0, userIds_ToDeactivate.Length - 2);
sql = String.Format(@"update [{0}].[ASPdb_Users] set [Active] = 0 where [UserId] in ({1})",
Config.SystemProperties.AppSchema, userIds_ToDeactivate);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.ExecuteNonQuery();
}
}
if (isFreeAccount)
{
var ids = new List <int>();
sql = String.Format("select [ConnectionId] from [{0}].[ASPdb_Connections] where [Active] = 1 order by [ConnectionId]", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
ids.Add(reader.Get("ConnectionId", -1));
}
}
}
if (ids.Count > 1)
{
string idsToDeactivate = "";
for (int i = 1; i < ids.Count; i++)
{
if (idsToDeactivate != "")
{
idsToDeactivate += ", ";
}
idsToDeactivate += ids[i];
}
sql = String.Format(@"update [{0}].[ASPdb_Connections] set [Active] = 0 where [ConnectionId] in ({1})", Config.SystemProperties.AppSchema, idsToDeactivate);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.ExecuteNonQuery();
}
}
}
return(true);
}
catch
{
return(false);
}
}
//----------------------------------------------------------------------------------------------------
public static HistoryRecord Get_HistoryRecord(int tableId, int historyId)
{
HistoryRecord rtn = null;
string sql = String.Format("select * from [{0}].[{1}] where [TableId] = @TableId and [HistoryId] = @HistoryId",
Config.SystemProperties.AppSchema, "ASPdb_History");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(History_ConnectionString(), sql))
{
command.AddParameter("@TableId", tableId);
command.AddParameter("@HistoryId", historyId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
if (reader.Read())
{
string keyValueStr = reader.Get("KeyValue", "");
bool keyValueIsTruncated = reader.Get("KeyValueIsTruncated", false);
HistoryJsonObj historyJsonObj = null;
try { historyJsonObj = (new JavaScriptSerializer()).Deserialize <HistoryJsonObj>(reader.Get("HistoryJSON", "")); }
catch { }
if (keyValueIsTruncated && historyJsonObj != null)
{
keyValueStr = historyJsonObj.KeyValue;
}
string[] keyValue = PrimaryKey_ToArray(keyValueStr);
var historyType = HistoryRecord.HistoryTypes.NotSet;
switch (reader.Get("HistoryType", ""))
{
case "Insert": historyType = HistoryRecord.HistoryTypes.Insert; break;
case "Update": historyType = HistoryRecord.HistoryTypes.Update; break;
case "Delete": historyType = HistoryRecord.HistoryTypes.Delete; break;
}
DateTime?nullDateTime = null;
rtn = new HistoryRecord()
{
HistoryId = reader.Get("HistoryId", -1),
TableId = reader.Get("TableId", -1),
KeyValue = keyValue,
HistoryType = historyType,
IsPartial = reader.Get("IsPartial", false),
TimeSaved = reader.Get("TimeSaved", DateTime.Now),
TimeSaved_String = reader.Get("TimeSaved", nullDateTime).ToString(),
ByUserId = reader.Get("ByUserId", -1),
ByUsername = reader.Get("ByUsername", ""),
HistoryJsonObj = historyJsonObj
};
}
}
var itemsDict = new Dictionary <string, Item>();
foreach (var item in rtn.HistoryJsonObj.Fields)
{
if (!itemsDict.ContainsKey(item.cn))
{
itemsDict.Add(item.cn, item);
}
}
var curHistoryRecord = new HistoryRecord()
{
HistoryId = -1,
TableId = tableId,
KeyValue = rtn.KeyValue
};
Initialize_HistoryRecord_AllFields(curHistoryRecord, "right");
var fields_3ValArr = new List <Item_3Values>();
foreach (var item in curHistoryRecord.HistoryJsonObj.Fields)
{
string cn = item.cn;
string v1 = null;
string v2 = null;
string v3 = item.v2;
bool match = true;
if (itemsDict.ContainsKey(cn))
{
v1 = itemsDict[cn].v1;
v2 = itemsDict[cn].v2;
match = itemsDict[cn].match;
itemsDict.Remove(cn);
}
fields_3ValArr.Add(new Item_3Values()
{
ColumnName_OrigCasing = cn,
cn = cn,
v1 = v1,
v2 = v2,
v3 = v3,
match = match // (v1 == v2)
});
}
if (itemsDict.Count > 0)
{
foreach (var item in itemsDict.Values) // -- in cases where columns have been removed from table structure
{
fields_3ValArr.Add(new Item_3Values()
{
ColumnName_OrigCasing = item.cn,
cn = item.cn,
v1 = item.v1,
v2 = item.v2,
v3 = null,
match = false
});
}
}
rtn.Fields_3ValArr = fields_3ValArr.ToArray();
var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(tableId, false, true, false);
var dict = new Dictionary <string, string>();
foreach (var item in tableStructure.Columns)
{
string key = item.ColumnName.Trim().ToLower();
string value = item.ColumnName.Trim();
if (!dict.ContainsKey(key))
{
dict.Add(key, value);
}
}
foreach (var item in rtn.Fields_3ValArr)
{
if (dict.ContainsKey(item.cn))
{
item.ColumnName_OrigCasing = dict[item.cn];
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static int Save_HistoryRecord(HistoryRecord historyRecord)
{
int rtn = -1;
if (!DoSaveHistory)
{
return(rtn);
}
string keyValueStr = PrimaryKey_ToString(historyRecord.KeyValue);
historyRecord.HistoryJsonObj.KeyValue = keyValueStr;
string historyJson = (new JavaScriptSerializer()).Serialize(historyRecord.HistoryJsonObj);
bool keyValueIsTruncated = (keyValueStr.Length > 50);
if (keyValueIsTruncated)
{
keyValueStr = keyValueStr.Substring(0, 50);
}
var userInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser().UserInfo;
historyRecord.ByUserId = userInfo.UserId;
historyRecord.ByUsername = userInfo.Username;
historyRecord.TimeSaved = DateTime.Now;
historyRecord.TimeSaved_String = historyRecord.TimeSaved.ToString();
int revision = 1;
var topHistoryRecord = Get_HistoryRecords(historyRecord.TableId, historyRecord.KeyValue, 1);
if (topHistoryRecord != null && topHistoryRecord.Length > 0 && topHistoryRecord[0].Revision > 0)
{
revision = topHistoryRecord[0].Revision + 1;
}
string sql = String.Format(@"
insert into [{0}].[{1}]
([TableId], [KeyValue], [KeyValueIsTruncated], [Revision], [HistoryType], [IsPartial], [TimeSaved], [ByUserId], [ByUsername], [HistoryJSON])
values
(@TableId, @KeyValue, @KeyValueIsTruncated, @Revision, @HistoryType, @IsPartial, @TimeSaved, @ByUserId, @ByUsername, @HistoryJSON);
SELECT @@IDENTITY AS [Identity]; "
, Config.SystemProperties.AppSchema, "ASPdb_History");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(History_ConnectionString(), sql))
{
command.AddParameter("@TableId", historyRecord.TableId);
command.AddParameter("@KeyValue", keyValueStr);
command.AddParameter("@KeyValueIsTruncated", keyValueIsTruncated);
command.AddParameter("@Revision", revision);
command.AddParameter("@HistoryType", historyRecord.HistoryType.ToString());
command.AddParameter("@IsPartial", historyRecord.IsPartial);
command.AddParameter("@TimeSaved", historyRecord.TimeSaved);
command.AddParameter("@ByUserId", historyRecord.ByUserId);
command.AddParameter("@ByUsername", historyRecord.ByUsername);
command.AddParameter("@HistoryJSON", historyJson);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
if (reader.Read())
{
rtn = reader.Get("Identity", -1);
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static List <ASPdb_Connection> ASPdb_Connection__GetAll(bool useCache)
{
AjaxService.ASPdatabaseService.GetSetVal();
var cache = ASPdatabaseNET.Memory.AppCache.Get();
if (useCache)
{
if (cache.ASPdb_Database_List != null && cache.ASPdb_Database_List.Count > 0)
{
return(cache.ASPdb_Database_List);
}
}
var rtn = new List <ASPdb_Connection>();
string sql = @"
select T1.*, T2.Username from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] as T1
left join [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] as T2 on T1.CreatedByUserId = T2.UserId
order by [ConnectionName]";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
DateTime?nullDateTime = null;
while (reader.Read())
{
var item = new ASPdb_Connection()
{
ConnectionId = reader.Get("ConnectionId", -1),
SiteId = reader.Get("SiteId", -1),
ConnectionName = reader.Get("ConnectionName", ""),
ConnectionType = reader.Get("ConnectionType", ""),
ParametersType = reader.Get("ParametersType", ""),
Active = reader.Get("Active", false),
DateTimeCreated = reader.Get("DateTimeCreated", nullDateTime),
CreatedByUserId = reader.Get("CreatedByUserId", -1),
CreatedByUsername = reader.Get("Username", ""),
Param_ServerAddress = reader.Get("Param_ServerAddress", ""),
Param_DatabaseName = reader.Get("Param_DatabaseName", ""),
Param_U = reader.Get("Param_U", ""),
Param_P = ASPdb_Connection__GetDecryptedPassword_OrNull(reader.Get("Param_P", "")),
Param_ConnectionString = reader.Get("Param_ConnectionString", "")
};
rtn.Add(item);
}
}
}
if (useCache)
{
cache.ASPdb_Database_List = rtn;
foreach (var item in rtn)
{
item.Param_P = ""; // don't leave passwords text in cache
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static string Save(RecordInfo recordInfo)
{
if (recordInfo.UniqueRowObj.TableType != TableGrid.Objs.GridRequest.TableTypes.Table)
{
throw new Exception("TableType not supported.");
}
var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(recordInfo.UniqueRowObj.Id, false, true, false);
var userSessionInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser();
var permission = userSessionInfo.UserInfo.AllPermissions.CheckPermissions(tableStructure.ConnectionId, tableStructure.Schema, tableStructure.TableId);
if (recordInfo.FieldValues == null || recordInfo.FieldValues.Length < 1)
{
throw new Exception("Nothing to save.\n\nPlease edit a field before saving.");
}
string sql = "";
var sqlParameters = new List <object[]>();
int p = 1;
bool doUpdate = (recordInfo.ActionType == TableGrid.Objs.UniqueRowKey.ActionTypes.Get);
if (doUpdate) // -- update -----------------------------------------------------------------------
{
if (!permission.Edit)
{
throw new Exception("You do not have edit permission on this table.");
}
string set = "", where = "";
for (int i = 0; i < recordInfo.FieldValues.Length; i++)
{
var column = recordInfo.Columns[recordInfo.FieldValues[i].Index];
if (!column.IsIdentity)
{
if (set != "")
{
set += ", ";
}
set += String.Format("[{0}] = @Param{1}", column.ColumnName, p);
object value = recordInfo.FieldValues[i].Value;
if (recordInfo.FieldValues[i].IsNull || value == null)
{
value = DBNull.Value;
}
sqlParameters.Add(new object[] { "@Param" + p, value });
p++;
}
}
for (int i = 0; i < recordInfo.UniqueRowObj.Values.Length; i++)
{
string primaryKeyName = tableStructure.PrimaryKey.Columns[i].ColumnName;
if (where != "")
{
where += " and ";
}
where += String.Format(" ([{0}] = @Param{1} ) ", primaryKeyName, p);
sqlParameters.Add(new object[] { "@Param" + p, recordInfo.UniqueRowObj.Values[i] });
p++;
}
sql = String.Format(@"
update [{0}].[{1}]
set {2}
where {3}
",
tableStructure.Schema, tableStructure.TableName,
set,
where);
Objs_History.HistoryRecord historyRecord1 = null;
Objs_History.HistoryRecord historyRecord2 = null;
var keyValue = recordInfo.UniqueRowObj.Values;
historyRecord1 = new Objs_History.HistoryRecord()
{
HistoryId = -1,
TableId = tableStructure.TableId,
KeyValue = keyValue,
HistoryType = PageParts.Record.Objs_History.HistoryRecord.HistoryTypes.Update,
IsPartial = false
};
HistoryLogic.Initialize_HistoryRecord_AllFields(historyRecord1, "Left");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) // Do Update
{
foreach (object[] parameter in sqlParameters)
{
command.AddParameter(parameter[0].ToString(), parameter[1]);
}
command.ExecuteNonQuery();
}
historyRecord2 = new Objs_History.HistoryRecord()
{
HistoryId = -1,
TableId = tableStructure.TableId,
KeyValue = keyValue,
HistoryType = PageParts.Record.Objs_History.HistoryRecord.HistoryTypes.Update,
IsPartial = false
};
HistoryLogic.Initialize_HistoryRecord_AllFields(historyRecord2, "Right");
var newValuesDict = new Dictionary <string, Objs_History.Item>();
foreach (var item in historyRecord2.HistoryJsonObj.Fields)
{
if (!newValuesDict.ContainsKey(item.cn))
{
newValuesDict.Add(item.cn, item);
}
}
int historyCount = HistoryLogic.Get_HistoryCount(tableStructure.TableId, keyValue);
historyRecord1.IsPartial = (historyCount > 0);
var fieldsList = new List <Objs_History.Item>();
foreach (var item1 in historyRecord1.HistoryJsonObj.Fields)
{
var item2 = new Objs_History.Item();
if (newValuesDict.ContainsKey(item1.cn))
{
item2 = newValuesDict[item1.cn];
}
item1.v2 = item2.v2;
if (item1.v1 == item1.v2)
{
item1.match = true;
item1.v2 = null;
}
if (historyRecord1.IsPartial == false || item1.match == false)
{
fieldsList.Add(item1);
}
}
historyRecord1.HistoryJsonObj.Fields = fieldsList.ToArray();
HistoryLogic.Save_HistoryRecord(historyRecord1);
}
else // -- insert new ----------------------------------------------------------------------------
{
if (!permission.Insert)
{
throw new Exception("You do not have insert permission on this table.");
}
string columnsSQL = "", valuesSQL = "";
for (int i = 0; i < recordInfo.FieldValues.Length; i++)
{
var column = recordInfo.Columns[recordInfo.FieldValues[i].Index];
if (!column.IsIdentity)
{
if (columnsSQL != "")
{
columnsSQL += ", ";
valuesSQL += ", ";
}
columnsSQL += "[" + column.ColumnName + "]";
valuesSQL += "@Param" + p;
object value = recordInfo.FieldValues[i].Value;
if (recordInfo.FieldValues[i].IsNull || value == null)
{
value = DBNull.Value;
}
sqlParameters.Add(new object[] { "@Param" + p, value });
p++;
}
}
sql = String.Format(@"
insert into [{0}].[{1}]
( {2} )
OUTPUT inserted.*
values
( {3} )
",
tableStructure.Schema, tableStructure.TableName,
columnsSQL,
valuesSQL);
var keyValue = new string[0];
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) // Do Insert
{
foreach (object[] parameter in sqlParameters)
{
command.AddParameter(parameter[0].ToString(), parameter[1]);
}
//command.ExecuteNonQuery();
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
if (reader.Read())
{
var keyValue_List = new List <string>();
foreach (var item in tableStructure.PrimaryKey.Columns)
{
keyValue_List.Add(reader.Get(item.ColumnName, ""));
}
keyValue = keyValue_List.ToArray();
}
}
if (keyValue.Length > 0)
{
var historyRecord = new Objs_History.HistoryRecord()
{
HistoryId = -1,
TableId = tableStructure.TableId,
KeyValue = keyValue,
HistoryType = PageParts.Record.Objs_History.HistoryRecord.HistoryTypes.Insert,
IsPartial = false
};
HistoryLogic.Initialize_HistoryRecord_AllFields(historyRecord, "Right");
HistoryLogic.Save_HistoryRecord(historyRecord);
}
}
//ASPdb.Framework.Debug.WriteLine(sql);
if (doUpdate)
{
return(recordInfo.UniqueRowObj.To_Base64Json());
}
else
{
return("");
}
}
//----------------------------------------------------------------------------------------------------
public static GridResponse Load_Table(GridRequest gridRequest, bool truncateValues, bool isAView)
{
var userSessionInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser();
var usersPermissions = userSessionInfo.UserInfo.AllPermissions;
DbInterfaces.TableObjects.TableStructure tableStructure = null;
if (!isAView)
{
tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(gridRequest.Id, false, true, false);
}
else
{
tableStructure = DbInterfaces.SQLServerInterface.Views__Get(gridRequest.Id, true, false);
}
var aspdb_Connection = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(tableStructure.ConnectionId);
if (!aspdb_Connection.Active)
{
throw new Exception("This connection is inactive.");
}
var uniqueRowKey_ForNew = new UniqueRowKey()
{
TableType = GridRequest.TableTypes.Table, Id = tableStructure.TableId, ActionType = UniqueRowKey.ActionTypes.New
};
var rtn = new GridResponse()
{
TableType = GridRequest.TableTypes.Table,
ConnectionId = tableStructure.ConnectionId,
Id = gridRequest.Id,
TableName = tableStructure.TableName,
Schema = tableStructure.Schema,
UniqueKey_ForNewRecord = uniqueRowKey_ForNew.To_Base64Json(),
PermissionValues = usersPermissions.CheckPermissions(tableStructure.ConnectionId, tableStructure.Schema, tableStructure.TableId),
IsAdmin = usersPermissions.IsAdmin
};
if (isAView)
{
rtn.TableType = GridRequest.TableTypes.View;
}
if (rtn.PermissionValues.View == false)
{
throw new Exception("You do not have permission to view this table/view.");
}
Helper1__PopulateAdditionalTableName(rtn);
//var columnsDict = new Dictionary<string, DbInterfaces.TableObjects.Column>();
//foreach (var item in tableStructure.Columns)
// if (!columnsDict.ContainsKey(item.ColumnName.Trim().ToLower()))
// columnsDict.Add(item.ColumnName.Trim().ToLower(), item);
var primaryKeyNames_L = new string[0];
if (tableStructure.PrimaryKey != null)
{
primaryKeyNames_L = (from c in tableStructure.PrimaryKey.Columns select c.ColumnName.Trim().ToLower()).ToArray();
}
var columnNames_L = (from c in tableStructure.Columns select c.ColumnName.Trim().ToLower()).ToArray();
List <object[]> sqlParameters;
string sqlPart1, sqlPart2_OrderBy;
Load_Table__BuildBottomSQL(gridRequest, rtn, true, columnNames_L, out sqlParameters, out sqlPart1, out sqlPart2_OrderBy);
string top = String.Format(" top {0} ", gridRequest.DisplayTopNRows);
if (gridRequest.DisplayTopNRows < 1)
{
top = " top 100 ";
}
if (gridRequest.DisplayTopNRows == -2)
{
top = " ";
}
string sql_Select = String.Format(@"
select {0}
*
{1}
{2}
"
, top
, sqlPart1
, sqlPart2_OrderBy);
string sql_TotalCount = String.Format(@"
select count(*) as [Count1]
{0}
"
, sqlPart1);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionId, sql_Select))
{
foreach (var param in sqlParameters)
{
command.AddParameter(param[0].ToString(), param[1]);
}
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
// build header
var headersList = new List <GridHeaderItem>();
var tmp1 = new List <string>(); var tmp2 = new List <int>();
for (int i = 0; i < reader.FieldCount; i++)
{
var headerItem = new GridHeaderItem();
headerItem.IndexPosition = i;
headerItem.FieldName = reader.GetName(i);
headerItem.DataTypeName = reader.GetDataTypeName(i);
headersList.Add(headerItem);
string key_L = headerItem.FieldName.Trim().ToLower();
if (primaryKeyNames_L.Contains(key_L))
{
headerItem.IsPrimaryKey = true;
tmp1.Add(headerItem.FieldName);
tmp2.Add(headerItem.IndexPosition);
}
}
rtn.HeaderItems = headersList.ToArray();
rtn.PrimaryKeyNames = tmp1.ToArray();
rtn.PriamryKeyIndexPositions = tmp2.ToArray();
int truncateLength = 100;
var rowsList = new List <GridRow>();
while (reader.Read())
{
var row = new GridRow();
row.Values = new string[rtn.HeaderItems.Length];
for (int i = 0; i < rtn.HeaderItems.Length; i++)
{
string value = reader.Get(i, "");
value = HttpContext.Current.Server.HtmlEncode(value);
if (truncateValues)
{
if (value.Length > truncateLength + 1)
{
value = value.Substring(0, truncateLength);
var chopLength = value.Split(new char[] { ' ' }).Last().Length;
if (chopLength < 21)
{
value = value.Substring(0, value.Length - chopLength) + "...";
}
else
{
value += " ...";
}
}
}
row.Values[i] = value;
}
var uniqueRowKey = Get_UniqueRowKey("t", rtn.Id, row, rtn.PriamryKeyIndexPositions);
row.UniqueKey = uniqueRowKey.To_Base64Json();
rowsList.Add(row);
}
rtn.Rows = rowsList.ToArray();
rtn.Count_DisplayItems = rtn.Rows.Length;
}
}
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionId, sql_TotalCount))
{
foreach (var param in sqlParameters)
{
command.AddParameter(param[0].ToString(), param[1]);
}
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
rtn.Count_TotalItems = reader.Get("Count1", -1);
}
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static AllPermissionsInfo LoadAllPermissions(int userId, bool isAdmin)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new AllPermissionsInfo()
{
UserId = userId, IsAdmin = isAdmin
};
if (isAdmin)
{
return(rtn);
}
rtn.PermissionValuesDict = new Dictionary <string, PermissionValues>();
string sql = String.Format(@"
select
T2.[GroupId], T2.[GroupName],
T3.[PermissionType], T3.[ConnectionId], T3.[Schema], T3.[TableId], T3.[View], T3.[Edit], T3.[Insert], T3.[Delete]
from
[{0}].[ASPdb_UsersToGroups] as T1
inner join
[{0}].[ASPdb_UserGroups] as T2 on T1.[GroupId] = T2.[GroupId]
inner join
[{0}].[ASPdb_Permissions] as T3 on T1.[GroupId] = T3.[GroupId]
where
T1.[UserId] = @UserId and T2.[Active] = 1
order by
T3.[ConnectionId], T3.[Schema], T3.[TableId]
", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@UserId", userId);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
string permissionType = reader.Get("PermissionType", "");
int connectionId = reader.Get("ConnectionId", -1);
string schema = reader.Get("Schema", "");
int tableId = reader.Get("TableId", -1);
var permissionValues = new PermissionValues()
{
View = reader.Get("View", false),
Edit = reader.Get("Edit", false),
Insert = reader.Get("Insert", false),
Delete = reader.Get("Delete", false)
};
string key = "";
switch (permissionType)
{
case "C": key = "C_" + connectionId; break;
case "S": key = "S_" + connectionId + "_" + schema.ToLower(); break;
case "T": key = "T_" + connectionId + "_" + schema.ToLower() + "_" + tableId; break;
}
if (!rtn.PermissionValuesDict.ContainsKey(key))
{
rtn.PermissionValuesDict.Add(key, permissionValues);
}
}
}
}
return(rtn);
}