//----------------------------------------------------------------------------------------------------
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_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);
}
}
}
}
}
//----------------------------------------------------------------------------------------------------
/// <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);
}
//----------------------------------------------------------------------------------------------------
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());
}
//----------------------------------------------------------------------------------------------------
/// <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 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 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);
}
//----------------------------------------------------------------------------------------------------
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 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 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 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());
}
//----------------------------------------------------------------------------------------------------
private static Column[] Tables__Get__GetColumns(TableStructure tableStructure)
{
var rtn = new List <Column>();
string sql = SQLServer_SQLBuilders.BuildSql__Column_SelectAll(tableStructure);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var column = new Column()
{
ConnectionId = tableStructure.ConnectionId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
OrdinalPosition = reader.Get("OrdinalPosition", 0),
ColumnName = reader.Get("ColumnName", ""),
DataType_Name = reader.Get("DataType", ""),
MaxLength = reader.Get("MaxLength", 0),
Precision = reader.Get("Precision", 0),
Scale = reader.Get("Scale", 0),
AllowNulls = reader.Get("AllowNulls", "NO").ToUpper() == "YES",
DefaultValue = reader.GetString_OrNullDefault("DefaultValue"),
IsPrimaryKey = reader.Get("IsPrimaryKey", 0) == 1,
IsIdentity = reader.Get("IsIdentity", 0) == 1,
Identity = null
};
if (column.IsIdentity)
{
column.Identity = new Identity()
{
ConnectionId = tableStructure.ConnectionId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
ColumnName = reader.Get("ColumnName", ""),
Seed = reader.Get("Identity_Seed", 0),
Increment = reader.Get("Identity_Increment", 0),
CurrentIdentity = reader.Get("Identity_CurrentIdentity", 0)
}
}
;
column.Populate__DataType_FullString();
column.Fix_DefaultValue_ForDisplay();
rtn.Add(column);
}
}
}
return(rtn.ToArray());
}
//----------------------------------------------------------------------------------------------------
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 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 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 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);
}
//----------------------------------------------------------------------------------------------------
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()));
}
}
//----------------------------------------------------------------------------------------------------
/// <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 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 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 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 int SaveUser(UserInfo userInfo)
{
// CONFIRM Username is not already taken ! ! !
// CONFIRM At least one user is still an Admin ! ! !
string sql = "";
bool doInsert = (userInfo.UserId < 0);
if (doInsert)
{
sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users]
([Username], [FirstName], [LastName], [Email], [Active], [RequirePasswordReset], [IsAdmin], [TimeCreated], [Password])
values
(@Username, @FirstName, @LastName, @Email, @Active, @RequirePasswordReset, @IsAdmin, @TimeCreated, '');
select scope_identity();
";
}
else
{
sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users]
set [Username] = @Username,
[FirstName] = @FirstName,
[LastName] = @LastName,
[Email] = @Email,
[Active] = @Active,
[RequirePasswordReset] = @RequirePasswordReset,
[IsAdmin] = @IsAdmin
where [UserId] = @UserId; ";
}
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@Username", userInfo.Username);
command.AddParameter("@FirstName", userInfo.FirstName);
command.AddParameter("@LastName", userInfo.LastName);
command.AddParameter("@Email", userInfo.Email);
command.AddParameter("@Active", userInfo.Active);
command.AddParameter("@RequirePasswordReset", userInfo.RequirePasswordReset);
command.AddParameter("@IsAdmin", userInfo.IsAdmin);
if (doInsert)
{
command.AddParameter("@TimeCreated", DateTime.Now);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
try { userInfo.UserId = Int32.Parse(reader.Reader[0].ToString()); }
catch { }
}
}
}
else
{
command.AddParameter("@UserId", userInfo.UserId);
command.ExecuteNonQuery();
}
}
sql = "";
if (!doInsert)
{
sql = @" delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UsersToGroups] where [UserId] = @UserId; ";
}
if (userInfo.UserGroups != null && userInfo.UserGroups.Length > 0)
{
var groupIds = (from r in userInfo.UserGroups where r.IsMember == true orderby r.GroupId select r.GroupId).ToList();
foreach (int groupId in groupIds)
{
sql += String.Format(@" insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UsersToGroups] ([UserId], [GroupId]) values (@UserId, {0});
", groupId);
}
}
if (sql.Length > 0)
{
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@UserId", userInfo.UserId);
command.ExecuteNonQuery();
}
}
return(userInfo.UserId);
}
//----------------------------------------------------------------------------------------------------
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);
}
//----------------------------------------------------------------------------------------------------
private static void CheckForSQLStructureUpdates()
{
string installedVersion = Config.ASPdb_Values.GetFirstValue("InstalledVersion", "");
string lastInstallTime = Config.ASPdb_Values.GetFirstValue("LastInstallTime", "");
if (installedVersion == Config.SystemProperties.Version)
{
return;
}
//--------------------
bool add__ASPdb_Tables__AppProperties = false;
bool add__ASPdb_Views = false;
bool add__ASPdb_History = false;
//--------------------
try
{
string sql = String.Format("select top 1 [{2}] from [{0}].[{1}]", Config.SystemProperties.AppSchema, "ASPdb_Tables", "AppProperties");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
reader.Read();
}
catch { add__ASPdb_Tables__AppProperties = true; }
try
{
string sql = String.Format("select top 1 * from [{0}].[{1}]", Config.SystemProperties.AppSchema, "ASPdb_Views");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
reader.Read();
}
catch { add__ASPdb_Views = true; }
try
{
string sql = String.Format("select top 1 * from [{0}].[{1}]", Config.SystemProperties.AppSchema, "ASPdb_History");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
reader.Read();
}
catch { add__ASPdb_History = true; }
//--------------------
if (add__ASPdb_Tables__AppProperties)
{
string sql = String.Format(@"ALTER TABLE [{0}].[{1}] ADD [{2}] {3}", Config.SystemProperties.AppSchema, "ASPdb_Tables", "AppProperties", "ntext");
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.ExecuteNonQuery();
}
}
if (add__ASPdb_Views)
{
string sql = String.Format(@"
CREATE TABLE [{0}].[ASPdb_Views](
[ViewId] [int] IDENTITY(1,1) NOT NULL,
[ConnectionId] [int] NOT NULL,
[ViewName] [nvarchar](250) NOT NULL,
[Schema] [nvarchar](100) NOT NULL,
[Hide] [bit] NOT NULL,
[UserFilter] [nvarchar](250) NOT NULL,
CONSTRAINT [PK_ASPdb_Views__001] PRIMARY KEY CLUSTERED
(
[ViewId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY];
ALTER TABLE [ASPdb].[ASPdb_Views] ADD DEFAULT (N'dbo') FOR [Schema];
ALTER TABLE [ASPdb].[ASPdb_Views] ADD DEFAULT ((0)) FOR [Hide];
ALTER TABLE [ASPdb].[ASPdb_Views] ADD DEFAULT (N'') FOR [UserFilter];
ALTER TABLE [ASPdb].[ASPdb_Views] WITH CHECK ADD CONSTRAINT [FK_ASPdb_Views_ASPdb_Connections_001] FOREIGN KEY([ConnectionId])
REFERENCES [ASPdb].[ASPdb_Connections] ([ConnectionId]);
ALTER TABLE [ASPdb].[ASPdb_Views] CHECK CONSTRAINT [FK_ASPdb_Views_ASPdb_Connections_001];
", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.ExecuteNonQuery();
}
}
if (add__ASPdb_History)
{
string sql = String.Format(@"
CREATE TABLE [{0}].[ASPdb_History](
[HistoryId] [int] IDENTITY(1,1) NOT NULL,
[TableId] [int] NOT NULL,
[KeyValue] [nvarchar](50) NOT NULL,
[KeyValueIsTruncated] [bit] NOT NULL,
[Revision] [int] NOT NULL,
[HistoryType] [nvarchar](10) NOT NULL,
[IsPartial] [bit] NOT NULL,
[TimeSaved] [datetime] NOT NULL,
[ByUserId] [int] NOT NULL,
[ByUsername] [nvarchar](50) NOT NULL,
[HistoryJSON] [ntext] NOT NULL,
CONSTRAINT [PK_ASPdb_ASPdb_History__6] PRIMARY KEY CLUSTERED
(
[HistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
CREATE NONCLUSTERED INDEX [IX_ASPdb_ASPdb_History_0001] ON [ASPdb].[ASPdb_History]
(
[TableId] ASC,
[KeyValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.ExecuteNonQuery();
}
}
Config.ASPdb_Values.Set("InstalledVersion", Config.SystemProperties.Version);
Config.ASPdb_Values.Set("LastInstallTime", DateTime.Now.ToString());
}
//----------------------------------------------------------------------------------------------------
public static int SaveGroup(GroupInfo groupInfo)
{
string sql = "";
bool doInsert = (groupInfo.GroupId < 0);
if (doInsert)
{
sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UserGroups]
([GroupName], [Active], [TimeCreated])
values
(@GroupName, @Active, @TimeCreated);
select scope_identity(); ";
}
else
{
sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UserGroups]
set [GroupName] = @GroupName,
[Active] = @Active
where [GroupId] = @GroupId; ";
}
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@GroupName", groupInfo.GroupName);
command.AddParameter("@Active", groupInfo.Active);
if (doInsert)
{
command.AddParameter("@TimeCreated", DateTime.Now);
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
try { groupInfo.GroupId = Int32.Parse(reader.Reader[0].ToString()); }
catch { }
}
}
}
else
{
command.AddParameter("@GroupId", groupInfo.GroupId);
command.ExecuteNonQuery();
}
}
var all_CurrentRecs = new Dictionary <string, Permission>();
var current_GroupInfo = GetGroup(groupInfo.GroupId);
if (current_GroupInfo.Permissions != null)
{
foreach (var item1 in current_GroupInfo.Permissions) // Connections
{
string key1 = item1.Get_UniqueKey();
if (key1 != null && !all_CurrentRecs.ContainsKey(key1))
{
all_CurrentRecs.Add(key1, item1);
}
if (item1.SubPermissions != null)
{
foreach (var item2 in item1.SubPermissions) // Schemas
{
string key2 = item2.Get_UniqueKey();
if (key2 != null && !all_CurrentRecs.ContainsKey(key2))
{
all_CurrentRecs.Add(key2, item2);
}
if (item2.SubPermissions != null)
{
foreach (var item3 in item2.SubPermissions) // Tables
{
string key3 = item3.Get_UniqueKey();
if (key3 != null && !all_CurrentRecs.ContainsKey(key3))
{
all_CurrentRecs.Add(key3, item3);
}
}
}
}
}
}
}
var tmpFiltered1 = new Dictionary <string, Permission>();
foreach (string key in all_CurrentRecs.Keys)
{
var item = all_CurrentRecs[key];
if (item.View || item.Edit || item.Insert || item.Delete)
{
tmpFiltered1.Add(key, item);
}
}
all_CurrentRecs = tmpFiltered1;
var all_NewRecs = new Dictionary <string, Permission>();
if (groupInfo.Permissions != null)
{
foreach (var item1 in groupInfo.Permissions) // Connections
{
string key1 = item1.Get_UniqueKey();
if (key1 != null && !all_NewRecs.ContainsKey(key1))
{
all_NewRecs.Add(key1, item1);
}
if (item1.SubPermissions != null)
{
foreach (var item2 in item1.SubPermissions) // Schemas
{
string key2 = item2.Get_UniqueKey();
if (key2 != null && !all_NewRecs.ContainsKey(key2))
{
all_NewRecs.Add(key2, item2);
}
if (item2.SubPermissions != null)
{
foreach (var item3 in item2.SubPermissions) // Tables
{
string key3 = item3.Get_UniqueKey();
if (key3 != null && !all_NewRecs.ContainsKey(key3))
{
all_NewRecs.Add(key3, item3);
}
}
}
}
}
}
}
var tmpFiltered2 = new Dictionary <string, Permission>();
foreach (string key in all_NewRecs.Keys)
{
var item = all_NewRecs[key];
if (item.View || item.Edit || item.Insert || item.Delete)
{
tmpFiltered2.Add(key, item);
}
}
all_NewRecs = tmpFiltered2;
var recs_ToDelete = new List <Permission>();
var recs_ToInsert = new List <Permission>();
var recs_ToUpdate = new List <Permission>();
foreach (string key in all_CurrentRecs.Keys)
{
if (!all_NewRecs.ContainsKey(key))
{
recs_ToDelete.Add(all_CurrentRecs[key]);
}
}
foreach (string key in all_NewRecs.Keys)
{
if (!all_CurrentRecs.ContainsKey(key))
{
all_NewRecs[key].GroupId = groupInfo.GroupId;
recs_ToInsert.Add(all_NewRecs[key]);
}
else // check for update
{
var currentRec = all_CurrentRecs[key];
var newRec = all_NewRecs[key];
if (!currentRec.IsEqual(newRec))
{
recs_ToUpdate.Add(newRec);
}
}
}
foreach (var item in recs_ToDelete)
{
sql = "delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions] where [PermissionId] = @PermissionId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@PermissionId", item.PermissionId);
command.ExecuteNonQuery();
}
}
foreach (var item in recs_ToInsert)
{
sql = "";
switch (item.PermissionType)
{
case Permission.PermissionTypes.Connection:
sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
([GroupId], [PermissionType], [ConnectionId], [View], [Edit], [Insert], [Delete]) values (@GroupId, @PermissionType, @ConnectionId, @View, @Edit, @Insert, @Delete) ";
break;
case Permission.PermissionTypes.Schema:
sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
([GroupId], [PermissionType], [ConnectionId], [Schema], [View], [Edit], [Insert], [Delete]) values (@GroupId, @PermissionType, @ConnectionId, @Schema, @View, @Edit, @Insert, @Delete) ";
break;
case Permission.PermissionTypes.Table:
sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
([GroupId], [PermissionType], [ConnectionId], [Schema], [TableId], [View], [Edit], [Insert], [Delete]) values (@GroupId, @PermissionType, @ConnectionId, @Schema, @TableId, @View, @Edit, @Insert, @Delete) ";
break;
}
SaveGroup__Helper(true, sql, item);
}
foreach (var item in recs_ToUpdate)
{
sql = "";
switch (item.PermissionType)
{
case Permission.PermissionTypes.Connection:
sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
set [GroupId] = @GroupId, [PermissionType] = @PermissionType, [ConnectionId] = @ConnectionId, [View] = @View, [Edit] = @Edit, [Insert] = @Insert, [Delete] = @Delete
where [PermissionId] = @PermissionId ";
break;
case Permission.PermissionTypes.Schema:
sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
set [GroupId] = @GroupId, [PermissionType] = @PermissionType, [ConnectionId] = @ConnectionId, [View] = @View, [Edit] = @Edit, [Insert] = @Insert, [Delete] = @Delete,
[Schema] = @Schema
where [PermissionId] = @PermissionId ";
break;
case Permission.PermissionTypes.Table:
sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
set [GroupId] = @GroupId, [PermissionType] = @PermissionType, [ConnectionId] = @ConnectionId, [View] = @View, [Edit] = @Edit, [Insert] = @Insert, [Delete] = @Delete,
[Schema] = @Schema,
[TableId] = @TableId
where [PermissionId] = @PermissionId ";
break;
}
SaveGroup__Helper(false, sql, item);
}
return(groupInfo.GroupId);
}
//----------------------------------------------------------------------------------------------------
public static RecordInfo Get(string uniqueRowKey)
{
var userSessionInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser();
var usersPermissions = userSessionInfo.UserInfo.AllPermissions;
var uniqueRowObj = UI.TableGrid.Objs.UniqueRowKey.GetFrom_Base64Json(uniqueRowKey);
if (!uniqueRowObj.IsValid)
{
throw new Exception("Invalid Key");
}
if (uniqueRowObj.TableType != TableGrid.Objs.GridRequest.TableTypes.Table)
{
throw new Exception("TableType not supported.");
}
var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(uniqueRowObj.Id, false, true, false);
var aspdb_Connection = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(tableStructure.ConnectionId);
if (!aspdb_Connection.Active)
{
throw new Exception("This connection is inactive.");
}
var rtn = new RecordInfo()
{
TableType = uniqueRowObj.TableType,
ConnectionId = tableStructure.ConnectionId,
Id = uniqueRowObj.Id,
Schema = tableStructure.Schema,
TableName = tableStructure.TableName,
Columns = tableStructure.Columns,
ActionType = uniqueRowObj.ActionType,
UniqueRowObj = uniqueRowObj,
PermissionValues = usersPermissions.CheckPermissions(tableStructure.ConnectionId, tableStructure.Schema, tableStructure.TableId),
ChangeHistory_IsOn = UI.PageParts.Record.Backend.HistoryLogic.DoSaveHistory
};
if (rtn.PermissionValues.View == false)
{
throw new Exception("You do not have permission to view this record.");
}
var tmp_PrimaryKeyNames = new List <string>();
var tmp_PriamryKeyIndexPositions = new List <int>();
for (int i = 0; i < rtn.Columns.Length; i++)
{
if (rtn.Columns[i].IsPrimaryKey)
{
tmp_PrimaryKeyNames.Add(rtn.Columns[i].ColumnName);
tmp_PriamryKeyIndexPositions.Add(i);
}
}
rtn.PrimaryKeyNames = tmp_PrimaryKeyNames.ToArray();
rtn.PriamryKeyIndexPositions = tmp_PriamryKeyIndexPositions.ToArray();
if (uniqueRowObj.ActionType == TableGrid.Objs.UniqueRowKey.ActionTypes.New)
{
rtn.FieldValues = new FieldValue[rtn.Columns.Length];
for (int i = 0; i < rtn.Columns.Length; i++)
{
var fieldValue = new FieldValue()
{
Index = i
};
string defaultValue = rtn.Columns[i].DefaultValue;
if (defaultValue != null)
{
if (defaultValue.StartsWith("'") && defaultValue.EndsWith("'"))
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
}
if (rtn.Columns[i].DataType_Name == "bit")
{
if (defaultValue != null)
{
if (defaultValue == "1" || defaultValue.ToLower() == "true")
{
defaultValue = "true";
}
}
}
if (defaultValue != null)
{
fieldValue.Value = defaultValue;
fieldValue.IsNull = false;
}
else
{
fieldValue.Value = "";
fieldValue.IsNull = true;
}
rtn.FieldValues[i] = fieldValue;
}
}
else if (uniqueRowObj.ActionType == TableGrid.Objs.UniqueRowKey.ActionTypes.Clone)
{
}
else
{
if (rtn.PrimaryKeyNames.Length != uniqueRowObj.Values.Length)
{
throw new Exception("Invalid PrimaryKey.");
}
string tmpWhere = "";
var sqlParameters = new List <object[]>();
int p = 1;
for (int i = 0; i < uniqueRowObj.Values.Length; i++)
{
if (tmpWhere != "")
{
tmpWhere += " and ";
}
tmpWhere += String.Format(" [{0}] = @Param{1} ", rtn.PrimaryKeyNames[i], p);
sqlParameters.Add(new object[] { "@Param" + p, uniqueRowObj.Values[i] });
p++;
}
string sql = String.Format(@"
select * from [{0}].[{1}]
where {2}
", tableStructure.Schema, tableStructure.TableName, tmpWhere);
rtn.FieldValues = new FieldValue[rtn.Columns.Length];
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
foreach (object[] parameter in sqlParameters)
{
command.AddParameter(parameter[0].ToString(), parameter[1]);
}
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
for (int i = 0; i < rtn.Columns.Length; i++)
{
var fieldValue = new FieldValue()
{
Index = i
};
fieldValue.Value = reader.GetString_OrNullDefault(rtn.Columns[i].ColumnName);
if (fieldValue.Value == null)
{
fieldValue.Value = "";
fieldValue.IsNull = true;
}
rtn.FieldValues[i] = fieldValue;
}
}
else
{
//for (int i = 0; i < rtn.Columns.Length; i++)
// rtn.FieldValues[i] = new FieldValue() { Index = 1, Value = "", IsNull = true };
throw new Exception("\nData needs to be refreshed. \nTherefore the app will now return to the Records List.");
}
}
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
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 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("");
}
}
