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); }