/// <summary> /// 添加数据:单行与多行 /// </summary> /// <param name="args"></param> static void Main0(string[] args) { using var context = new DemoContext(); var serieA = new League { Country = "Italy", Name = "Serie A" }; var serieB = new League { Country = "Italy", Name = "Serie B" }; var serieC = new League { Country = "Italy", Name = "Serie C" }; var milan = new Club { Name = "AC Milan", City = "Milan", DateOfEstableishment = new DateTime(1899, 12, 16), League = serieA }; // 在同一张表中添加单条数据 //context.Leagues.Add(serieA); // 在不同表中同时添加多笔数据:直接使用 context,不使用 DbSet,context 可以识别出来每个数据是哪个类型(表)的 context.AddRange(serieB, serieC, milan); // 在一张表中同时添加多笔数据 //context.Leagues.AddRange(serieB,serieC); //context.Leagues.AddRange(new List<League> { serieB,serieC}); var count = context.SaveChanges(); Console.WriteLine(count); }
static void Main(string[] args) { using var context = new DemoContext(null); #region 1 Insert One Record //var serieA = new League //{ // Country = "Italy", // Name = "Serie A" //}; //context.Leagues.Add(serieA); #endregion #region 2 Search One Record and Inset Multi-Records var serieA = context.Leagues.Single(x => x.Name == "Serie A"); var serieB = new League { Country = "Italy", Name = "Serie B" }; var serieC = new League { Country = "Italy", Name = "Serie C" }; var Manlian = new Club { Name = "AC Manlian", City = "Milan", DataOfEstablishment = DateTime.Now, League = serieA }; context.AddRange(serieB, serieC, Manlian); #endregion #region Search //var italy = "Italy"; //var leagues = context.Leagues // .Where(x => x.Country == italy) // .ToList(); //var leaguesContains = context.Leagues // .Where(x => x.Country.Contains("I")) // .ToList(); //var leaguesLike = context.Leagues // .Where(x => // EF.Functions.Like(x.Country, "%I%")) // .ToList(); //var leaguesFirst = context.Leagues // .FirstOrDefault(x => // EF.Functions.Like(x.Country, "%I%")); #endregion #region Delete Record //var manlian = context.Clubs.Single(x => x.Name == "AC Manlian"); //Way#1 //context.Clubs.Remove(manlian); //Way#2 //context.Remove(manlian); //Way#3 //context.Clubs.RemoveRange(manlian, manlian); //Way#4 //context.RemoveRange(manlian, manlian); #endregion #region Modify Record //Way#1 //var leagueRecordWithTracking = context.Leagues.First(); //leagueRecordWithTracking.Name = "~"; //Way#2 //var league = context.Leagues.AsNoTracking().First(); //league.Name += "New"; //context.Leagues.Update(league); #endregion #region Add Relationship //Add Club/Player to SerieA //var serieA = context.Leagues.SingleOrDefault(x => x.Name == "Serie A"); //var juventus = new Club //{ // League = serieA, // Name = "Juventus", // City = "Torino", // DataOfEstablishment = DateTime.Now, // Players = new List<Player> // { // new Player // { // Name = "C. Ronaldo", // DateOfBirth = new DateTime(1985,1,1) // } // } //}; //Add Player to Club //var juventus = context.Clubs.SingleOrDefault(x => x.Name == "Juventus"); //juventus.Players.Add(new Player //{ // Name = "Gon", // DateOfBirth = new DateTime(1987,1,2) //}); //Add Resume directly, specify PlayerId directly //var resume = new Resume //{ // PlayerId = 1, // Description = "C.Ronaldo's Resume" //}; //context.Resumes.Add(resume); #endregion #region Load Relationship //Load All data from database //var clubs = context.Clubs // .Where(x => x.Name == "A") // .Include(x => x.League) // .Include(x => x.Players) // .ThenInclude(y => y.Resume) // .Include(x => x.Players) // .ThenInclude(y => y.GamePlayers) // .ThenInclude(z => z.Game) // .ToList(); //var club1 = context.Clubs.Find(1); //Load All data from database //var info = context.Clubs // .Where(x => x.Id > 0) // .Select(x => new // { // x.Id, // LeagueName = x.League.Name, // x.Name, // Players = x.Players // .Where(p => // p.DateOfBirth >= new DateTime(1990, 1, 1)) // }).ToList(); //Get a record first, then try to load its related model data //var info = context.Clubs.First(); //context.Entry(info) // .Collection(x => x.Players) // Load collection // .Query() // .Where(x=>x.DateOfBirth>new DateTime(1998,2,4)) // .Load(); //context.Entry(info) // .Reference(x => x.League) //Load single reference // .Load(); //LazyLoading //Load with multi conditions //var data = context.Clubs // .Where(x => x.League.Name.Contains("e")) // .ToList(); //Player -- GamePlayer -- Game //var gamePlayers = context.Set<GamePlayer>() // .Where(x => x.Player.Id > 0) // .ToList(); #endregion #region Modify Relevent Relationship Data //Get Club , but update League //var club = context.Clubs // .Include(x => x.League) // .First(); //club.League.Name += "#"; //Only update itsself, not update relevent table data //var game = context.Games // .Include(x => x.GamePlayers) // .ThenInclude(x => x.Player) // .First(); //var firstPlayer = game.GamePlayers[0].Player;//In real biz case, the data may from JSON //firstPlayer.Name += "#"; //{ // using var newContext = new DemoContext(); // //newContext.Players.Update(firstPlayer);//Will update all relevent data // //suggest the below one, it will only update Player. // newContext.Entry(firstPlayer).State = EntityState.Modified; // newContext.SaveChanges(); //} //Update intermiddeate table //var gamePlayer = new GamePlayer //{ // GameId = 1, // PlayerId = 3 //}; //context.Add(gamePlayer); //var game = context.Games.First(); //game.GamePlayers.Add(new GamePlayer //{ // PlayerId = 4 //}); //Remove a record //var gamePlayer = new GamePlayer //{ // GameId = 1, // PlayerId = 3 //}; //context.Remove(gamePlayer); //Update Player's Resume //var player = context.Players // .Include(x => x.Resume) //We should call Include, otherwise, EF will try to insert a new Resume rather update the exising resume // .OrderBy(x => x.Id) // .Last(); //player.Resume = new Resume //{ // Description = "New resume" //}; #endregion var count = context.SaveChanges(); Console.WriteLine(count); }
public async Task OnPostADCommitAsync() { string filePath = SaveAndGetFilePath(); if (!Message.Contains("Error!")) { using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { using (var ep = new ExcelPackage(stream)) { // get the first worksheet ExcelWorkbook wb = ep.Workbook; int iSheetsCount = ep.Workbook.Worksheets.Count; var ws = wb.Worksheets["Users"]; // initialize the record counters var nAD = 0; #region Import all CMDB entries // create a list containing all the AD entries already existing // into the Database (it should be empty on first run). var lstAD_Computers = _context.AD_Computers.ToList(); var lstAD_Users = _context.AD_Users.ToList(); // iterates through all rows, skipping the first one for (int nRow = 2; nRow <= ws.Dimension.Rows; nRow++) { var row = ws.Cells[nRow, 1, nRow, ws.Dimension.End.Column]; lstAD_Users.Add(new AD_User { ProgramOffice = row[nRow, 1].GetValue <string>(), CACExemptionReason = row[nRow, 2].GetValue <string>(), SmartCardRequired = row[nRow, 3].GetValue <bool>(), UserEmailAddress = row[nRow, 4].GetValue <string>(), EmployeeType = row[nRow, 5].GetValue <string>(), SAMAccountName = row[nRow, 6].GetValue <string>(), Description = row[nRow, 7].GetValue <string>(), UserPrincipalName = row[nRow, 8].GetValue <string>(), AccountDisabled = row[nRow, 9].GetValue <bool>(), PasswordDoesNotExpire = row[nRow, 10].GetValue <bool>(), PasswordCannotChange = row[nRow, 11].GetValue <bool>(), PasswordExpired = row[nRow, 12].GetValue <bool>(), AccountLockedOut = row[nRow, 13].GetValue <bool>(), CACExtendedInfo = row[nRow, 14].GetValue <string>(), UAC = row[nRow, 15].GetValue <int>(), UserName = row[nRow, 16].GetValue <string>(), DN = row[nRow, 17].GetValue <string>(), Created = row[nRow, 18].GetValue <DateTime>(), Changed = row[nRow, 19].GetValue <DateTime>() }); _context.AD_Users.AddRange(lstAD_Users); nAD++; } ws = ep.Workbook.Worksheets["Computers"]; // iterates through all rows, skipping the first one for (int nRow = 2; nRow <= ws.Dimension.End.Row; nRow++) { var row = ws.Cells[nRow, 1, nRow, ws.Dimension.End.Column]; lstAD_Computers.Add(new AD_Computer { ADComputerName = row[nRow, 2].GetValue <string>(), ProgramOffice = row[nRow, 1].GetValue <string>(), OSType = row[nRow, 3].GetValue <string>(), OSVersion = row[nRow, 4].GetValue <string>(), ServicePack = row[nRow, 5].GetValue <string>(), Created = row[nRow, 6].GetValue <DateTime>(), Changed = row[nRow, 7].GetValue <DateTime>(), UAC = row[nRow, 8].GetValue <int>(), AccountDisabled = row[nRow, 9].GetValue <bool>(), SmartCardRequired = row[nRow, 10].GetValue <bool>(), Description = row[nRow, 11].GetValue <string>(), DN = row[nRow, 12].GetValue <string>(), Win7StatusExtendedinfo = row[nRow, 13].GetValue <string>() }); _context.AddRange(lstAD_Computers); nAD++; } await _context.SaveChangesAsync(); Message = "AD Committed to Database. " + nAD + " entries Added."; } } #endregion } }