private static void CreateAnonDbInfosIfNoExists()
{
OleDbDataBaseReader reader = new OleDbDataBaseReader(ConfigurationManager.ConnectionStrings["IdentityDbOleDb"].ConnectionString);
try
{
reader.LoadTables(AnonDbInfosTableName);
}
// if table {AnonDbInfosTableName} does not exist
catch (OleDbException)
{
using (
SqlServerExecutor executor =
new SqlServerExecutor(ConfigurationManager.ConnectionStrings["IdentityDb"].ConnectionString))
{
string query = $"USE {DbName} CREATE TABLE {AnonDbInfosTableName} ("
+ "ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, "
+ "NAME NVARCHAR(200) NOT NULL, "
+ "DATEOFCREATING DATETIME NOT NULL, "
+ "DATEOFDELETING DATETIME NOT NULL, "
+ "CONNECTIONSTRING NVARCHAR(500) NOT NULL, "
+ "DBMSTYPE NVARCHAR(128), "
+ ")";
executor.ExecuteQueryAsString(query);
}
}
}
public static void DropOutdatedDbs()
{
try
{
OleDbDataBaseReader reader = new OleDbDataBaseReader(ConfigurationManager.ConnectionStrings["IdentityDbOleDb"].ConnectionString);
DataSet set = reader.LoadTables(AnonDbInfosTableName);
var result = from dbRecord in set.Tables[AnonDbInfosTableName].AsEnumerable()
let dateOfDeleting = (DateTime)dbRecord["DATEOFDELETING"]
where dateOfDeleting < DateTime.Now
select new DataBaseInfo
{
Name = dbRecord.Field <string>("NAME"),
Id = dbRecord.Field <int>("ID"),
DateOfDeleting = (DateTime)dbRecord["DATEOFDELETING"]
};
//TODO: add additional servers
IHelper helper = new SqlServerHelper();
foreach (DataBaseInfo dataBaseInfo in result)
{
RemoveAnonymousDbInfo(dataBaseInfo);
helper.DropDataBase(dataBaseInfo.Name);
}
}
catch
{
// ignored
}
}
public ActionResult DeleteTable()
{
TableVm vm = new TableVm();
OleDbDataBaseReader reader = new OleDbDataBaseReader(DataBaseInfo.ConnectionString);
var tables = reader.GetTableNames();
var items = tables.Select(tableName => new SelectListItem {
Text = tableName, Value = tableName
}).ToArray();
vm.AvailableColumns.AddRange(items);
return(PartialView(vm));
}
public static List <DataBaseInfo> GetAnonymousDbInfos()
{
OleDbDataBaseReader reader =
new OleDbDataBaseReader(ConfigurationManager.ConnectionStrings["IdentityDbOleDb"].ConnectionString);
DataSet set = reader.LoadTables(AnonDbInfosTableName);
//Selects DBInfos, that belongs to user
var result = (from dbInfo in set.Tables[DbInfosTableName].AsEnumerable()
select new DataBaseInfo
{
Id = dbInfo.Field <int>("Id"),
Name = dbInfo.Field <string>("NAME"),
ConnectionString = dbInfo.Field <string>("CONNECTIONSTRING"),
DateOfCreating = (DateTime)dbInfo["DATEOFCREATING"],
DateOfDeleting = (DateTime)dbInfo["DATEOFDELETING"],
IsAnonymous = true,
DbmsType = (DbmsType)Enum.Parse(typeof(DbmsType), dbInfo.Field <string>("DBMSTYPE"))
}).ToList();
return(result);
}
/// <summary>
/// Returns list of user`s database, orienting his ID.
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static List <DataBaseInfo> GetDbInfos(string userId)
{
//TODO Add public, add anonymous
OleDbDataBaseReader reader =
new OleDbDataBaseReader(ConfigurationManager.ConnectionStrings["IdentityDbOleDb"].ConnectionString);
DataSet set = reader.LoadTables(DbInfosTableName, AspNetUserTable);
//Selects DBInfos, that belongs to user
var result = (from dbInfo in set.Tables[DbInfosTableName].AsEnumerable()
where dbInfo.Field <string>("USERKEY") == userId
select new DataBaseInfo
{
Id = dbInfo.Field <int>("Id"),
Name = dbInfo.Field <string>("NAME"),
ConnectionString = dbInfo.Field <string>("CONNECTIONSTRING"),
DateOfCreating = (DateTime)dbInfo["DATEOFCREATING"],
IsAnonymous = false,
DbmsType = (DbmsType)Enum.Parse(typeof(DbmsType), dbInfo.Field <string>("DBMSTYPE")),
ForeignKey = dbInfo.Field <string>("USERKEY")
}).ToList();
return(result);
}
