//----------------------------------------------------------------------------------------------------
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>
/// 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 TableDesignResponse Table__Create(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new TableDesignResponse()
{
ResponseType = TableDesignResponse.ResponseTypesEnum.CreateTable,
ConnectionId = tableStructure.ConnectionId, Schema = tableStructure.Schema, TableName = tableStructure.TableName
};
tableStructure.Columns = (from c in tableStructure.Columns
where c.ColumnName.Length > 0 &&
c.DataType.Length > 0
orderby c.OrdinalPosition
select c).ToArray();
var primaryKey = new PrimaryKey()
{
ConnectionId = tableStructure.ConnectionId, TableId = tableStructure.TableId, Schema = tableStructure.Schema, TableName = tableStructure.TableName
};
var tmp_PrimaryKeyColumns = new List <PrimaryKeyColumn>();
int i = 1;
foreach (var column in tableStructure.Columns)
{
if (column.IsPrimaryKey)
{
tmp_PrimaryKeyColumns.Add(new PrimaryKeyColumn()
{
ColumnName = column.ColumnName, OrdinalPosition = i++
});
}
column.Fix_DefaultValue_ForSaving();
}
primaryKey.Columns = tmp_PrimaryKeyColumns.ToArray();
string sql = SQLServer_SQLBuilders.BuildSql__Table_Create(tableStructure);
ASPdb.Framework.Debug.WriteLine(sql);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
PrimaryKey__Create(primaryKey, false);
rtn.TableId = -1;
Memory.AppCache.Reset();
var allTables = DataAccess.SQLObjectsCRUD.ASPdb_Table__GetAll(tableStructure.ConnectionId, true);
foreach (var table in allTables)
{
if (table.Schema.ToLower() == rtn.Schema.ToLower() &&
table.TableName.ToLower() == rtn.TableName.ToLower())
{
rtn.TableId = table.TableId;
}
}
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 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 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 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 void ForeignKey__Delete(ForeignKey foreignKey)
{
AjaxService.ASPdatabaseService.GetSetVal();
string sql = SQLServer_SQLBuilders.BuildSql__ForeignKey_Drop(foreignKey);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(foreignKey.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
public static void Index__Delete(Index index)
{
AjaxService.ASPdatabaseService.GetSetVal();
string sql = SQLServer_SQLBuilders.BuildSql__Index_Drop(index);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(index.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
public static void Column__Create(Column column)
{
AjaxService.ASPdatabaseService.GetSetVal();
string sql = SQLServer_SQLBuilders.BuildSql__Column_Create(column);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(column.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
public static void ForeignKey__Create(ForeignKey foreignKey)
{
AjaxService.ASPdatabaseService.GetSetVal();
var allConstraints = Constraint__GetAll_InConnection(foreignKey.ConnectionId);
string sql = SQLServer_SQLBuilders.BuildSql__ForeignKey_Create(foreignKey, true, allConstraints);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(foreignKey.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
public static void PrimaryKey__Delete(PrimaryKey primaryKey)
{
AjaxService.ASPdatabaseService.GetSetVal();
string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Drop(primaryKey);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(primaryKey.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(primaryKey.TableId, false, true, true);
}
//----------------------------------------------------------------------------------------------------
public static void Index__Create(Index index)
{
AjaxService.ASPdatabaseService.GetSetVal();
var allIndexes = Index__GetAll_InConnection(index.ConnectionId);
string sql = SQLServer_SQLBuilders.BuildSql__Index_Create(index, true, allIndexes);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(index.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
public static void ASPdb_Connection__Delete(int connectionId)
{
AjaxService.ASPdatabaseService.GetSetVal();
string sql = "delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] where [ConnectionId] = @ConnectionId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@ConnectionId", connectionId);
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
private static void SaveLastLoginTime(int userId)
{
string sql = String.Format("update [{0}].[ASPdb_Users] set [LastLoginTime] = @LastLoginTime where [UserId] = @UserId", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@LastLoginTime", DateTime.Now);
command.AddParameter("@UserId", userId);
command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
public static void Add(string key, string value)
{
AjaxService.ASPdatabaseService.GetSetVal();
string sql = "insert into [" + Config.SystemProperties.AppSchema + "].[ASPdb_Values] ([Key], [Value]) values (@Key, @Value) ";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@Key", key);
command.AddParameter("@Value", value);
command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
private static string ExeSQL(string connectionString, string sql, string successMsg, string failedMsg)
{
try
{
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionString, sql))
{
command.ExecuteNonQuery();
}
return(successMsg);
}
catch (Exception exc) { ASPdb.Framework.Debug.RecordException(exc); return(failedMsg); }
}
//----------------------------------------------------------------------------------------------------
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 void PrimaryKey__Update(PrimaryKey primaryKey)
{
AjaxService.ASPdatabaseService.GetSetVal();
string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Update(primaryKey);
ASPdb.Framework.Debug.WriteLine(sql);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(primaryKey.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
PrimaryKey__SeeIfIdentityChanged(primaryKey);
var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(primaryKey.TableId, false, true, true);
}
//----------------------------------------------------------------------------------------------------
public static void Schemas__Delete(int connectionId, string schemaName)
{
AjaxService.ASPdatabaseService.GetSetVal();
ASPdb.Framework.Validation.ValidateTextForSql1(schemaName, true);
string connectionString = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(connectionId).GetConnectionString();
string sql = String.Format("Drop Schema [{0}]", schemaName);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionString, sql))
{
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
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 void SaveGroup__Helper(bool doInsert, string sql, Permission permission)
{
string tmp_PermissionType = "N";
int? tmp_TableId = null;
switch (permission.PermissionType)
{
case Permission.PermissionTypes.Connection:
tmp_PermissionType = "C";
permission.Schema = null;
break;
case Permission.PermissionTypes.Schema:
tmp_PermissionType = "S";
break;
case Permission.PermissionTypes.Table:
tmp_PermissionType = "T";
tmp_TableId = permission.TableId;
break;
}
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@GroupId", permission.GroupId);
command.AddParameter("@PermissionType", tmp_PermissionType);
command.AddParameter("@ConnectionId", permission.ConnectionId);
//command.AddParameter("@Schema", permission.Schema);
//command.AddParameter("@TableId", tmp_TableId);
command.AddParameter("@View", permission.View);
command.AddParameter("@Edit", permission.Edit);
command.AddParameter("@Insert", permission.Insert);
command.AddParameter("@Delete", permission.Delete);
if (!doInsert)
{
command.AddParameter("@PermissionId", permission.PermissionId);
}
if (permission.PermissionType != Permission.PermissionTypes.Connection)
{
command.AddParameter("@Schema", permission.Schema);
}
if (permission.PermissionType == Permission.PermissionTypes.Table)
{
command.AddParameter("@TableId", tmp_TableId);
}
command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
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 void Remove(string key)
{
AjaxService.ASPdatabaseService.GetSetVal();
try
{
string sql = String.Format("delete from [{0}].[ASPdb_Values] where [Key] = @Key",
Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@Key", key);
command.ExecuteNonQuery();
}
}
catch { }
}
//----------------------------------------------------------------------------------------------------
public static void PrimaryKey__Create(PrimaryKey primaryKey, bool seeIfIdentityChanged)
{
AjaxService.ASPdatabaseService.GetSetVal();
var allConstraints = Constraint__GetAll_InConnection(primaryKey.ConnectionId);
string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Create(primaryKey, true, allConstraints, true);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(primaryKey.ConnectionId, sql))
{
command.Command.ExecuteNonQuery();
}
if (seeIfIdentityChanged)
{
PrimaryKey__SeeIfIdentityChanged(primaryKey);
}
var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(primaryKey.TableId, false, true, true);
}
//----------------------------------------------------------------------------------------------------
public static void SavePassword(int userId, string newPassword)
{
ValidatePassword(newPassword);
try
{
string hash = BCrypt.Net.BCrypt.HashPassword(newPassword, BCrypt.Net.BCrypt.GenerateSalt(UsersLogic.WorkFactor));
newPassword = "";
string sql = @" update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] set [Password] = @Password where [UserId] = @UserId ";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@Password", hash);
command.AddParameter("@UserId", userId);
command.ExecuteNonQuery();
}
}
catch { throw new Exception("Could not save password."); }
}
//----------------------------------------------------------------------------------------------------
public static void DeleteGroup(int groupId)
{
try
{
string sql = @"
delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UsersToGroups] where [GroupId] = @GroupId;
delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions] where [GroupId] = @GroupId;
delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UserGroups] where [GroupId] = @GroupId;
";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@GroupId", groupId);
command.ExecuteNonQuery();
}
}
catch { throw new Exception("Group could not be deleted. \n\nMost likely this group's GroupId is associated in another application table."); }
}
//----------------------------------------------------------------------------------------------------
public static AppPropertiesInfo Save(int tableId, AppPropertiesInfo appPropertiesInfo)
{
string json = ASPdb.Ajax.AjaxHelper.New.ToJson(appPropertiesInfo);
string sql = String.Format(@"
update [{0}].[ASPdb_Tables]
set [AppProperties] = @AppProperties
where [TableId] = @TableId
", Config.SystemProperties.AppSchema);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@AppProperties", json);
command.AddParameter("@TableId", tableId);
command.ExecuteNonQuery();
}
return(Get(tableId, false));
}