/// <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
}
}