//----------------------------------------------------------------------------------------------------
public static DatabaseConnectionResponse Save(ASPdb_Connection connectionInfo)
{
AjaxService.ASPdatabaseService.GetSetVal();
connectionInfo.ConnectionName = connectionInfo.ConnectionName.Trim();
SQLObjectsCRUD.ASPdb_Connection__Save(connectionInfo);
return new DatabaseConnectionResponse() { ResponseType = DatabaseConnectionResponse.ResponseTypesEnum.Save, SaveSuccess = true };
}
//----------------------------------------------------------------------------------------------------
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);
}
//----------------------------------------------------------------------------------------------------
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);
}
//----------------------------------------------------------------------------------------------------
private static string ASPdb_Connection__GetEncryptedPassword_OrNull(ASPdb_Connection connectionInfo)
{
AjaxService.ASPdatabaseService.GetSetVal();
try
{
string p = connectionInfo.Param_P;
string p2 = p.Replace("#", "").Trim();
if (p != null && p.Length > 0 && p2.Length > 0)
{
return(ASPdb.Security.MachineEncryption.Protect(p, ASPdb_Connection__Purpose));
}
}
catch { }
return(null);
}
//----------------------------------------------------------------------------------------------------
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);
}
//----------------------------------------------------------------------------------------------------
private static void ASPdb_Connection__SaveUpdate(ASPdb_Connection connectionInfo)
{
ASPdb_Connection__CheckForDuplicateConnectionName(connectionInfo);
string encrypted_Password = ASPdb_Connection__GetEncryptedPassword_OrNull(connectionInfo);
string sql_PasswordStatement = "";
if (encrypted_Password != null)
{
sql_PasswordStatement = " [Param_P] = @Param_P, ";
}
string sql = @"
update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections]
set
[ConnectionName] = @ConnectionName,
[ConnectionType] = @ConnectionType,
[ParametersType] = @ParametersType,
[Active] = @Active,
[Param_ServerAddress] = @Param_ServerAddress,
[Param_DatabaseName] = @Param_DatabaseName,
[Param_U] = @Param_U,
" + sql_PasswordStatement + @"
[Param_ConnectionString] = @Param_ConnectionString
where [ConnectionId] = @ConnectionId";
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
{
command.AddParameter("@ConnectionId", connectionInfo.ConnectionId);
command.AddParameter("@ConnectionName", connectionInfo.ConnectionName);
command.AddParameter("@ConnectionType", connectionInfo.ConnectionType);
command.AddParameter("@ParametersType", connectionInfo.ParametersType);
command.AddParameter("@Active", connectionInfo.Active);
command.AddParameter("@Param_ServerAddress", connectionInfo.Param_ServerAddress);
command.AddParameter("@Param_DatabaseName", connectionInfo.Param_DatabaseName);
command.AddParameter("@Param_U", connectionInfo.Param_U);
if (encrypted_Password != null)
{
command.AddParameter("@Param_P", encrypted_Password);
}
command.AddParameter("@Param_ConnectionString", connectionInfo.Param_ConnectionString);
command.Command.ExecuteNonQuery();
}
}
//----------------------------------------------------------------------------------------------------
public static void ASPdb_Connection__Save(ASPdb_Connection connectionInfo)
{
AjaxService.ASPdatabaseService.GetSetVal();
if (connectionInfo == null)
{
throw new Exception("Cannot save -- connectionInfo is null.");
}
if (connectionInfo.ConnectionId < 0)
{
ASPdb_Connection__SaveInsert(connectionInfo);
}
else
{
ASPdb_Connection__SaveUpdate(connectionInfo);
}
ASPdatabaseNET.Memory.AppCache.Reset();
}
//----------------------------------------------------------------------------------------------------
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()));
}
}
//----------------------------------------------------------------------------------------------------
public static DatabaseConnectionResponse Test(ASPdb_Connection aspdb_Connection, bool saveOnSuccess)
{
AjaxService.ASPdatabaseService.GetSetVal();
var rtn = new DatabaseConnectionResponse() { ResponseType = DatabaseConnectionResponse.ResponseTypesEnum.Test };
rtn.SaveOnSuccess = saveOnSuccess;
rtn.TestConnection_Passed = false;
try
{
string validationString = aspdb_Connection.GetValidationMessage();
if (validationString != "")
throw new Exception(validationString);
string connectionString = aspdb_Connection.GetConnectionString();
var tableItems = SQLObjectsCRUD.ASPdb_Table__GetRawList_NoCache(aspdb_Connection);
string s = "";
s += String.Format("{0} Tables Found<br /><br />", tableItems.Count);
if(tableItems.Count == 1)
s = String.Format("{0} Table Found<br /><br />", tableItems.Count);
foreach (var item in tableItems)
s += String.Format("<div class='TableItem1'><span>{0}.</span>{1}</div>", item.SchemaName, item.TableName);
rtn.TestConnection_Passed = true;
rtn.TestConnection_Message = s;
}
catch (Exception exc)
{
rtn.TestConnection_Passed = false;
rtn.TestConnection_Message += exc.Message; // +"<hr />" + exc.StackTrace;
}
return rtn;
}
