使用Sqlite+sqlsugar+NPOI对Excel增删改查,一对多查询_罗迪尼亚的熔岩_sqlite sqlsugar

网络 02-07 2308

Excel, ppt, Word 都属于微软的产品, 大多用在Windows操作系统. 对Excel的高级操作, 特别是连表操作, 大多数人会用VBA. 然而VBA是20年前的产物, VB语言也在十几年前停止了维护, 连他的儿子VB.Net 也在去年停止了维护, VB可以说已经寿终正寝了. 对于VBA, 微软也有以C# 为开发语言的VSTO作为替代.

微软主推的编程语言是C# . C# 是完全的面向对象的语言, 也可以与C++联合编程, 也可以启动Unsafe模式操作指针, 和Java有90%的语法相似度. C# 不仅可以做Web, 硬件, 桌面, 做很多小工具也效果拔群, 今天我就来教大家如何用Windows窗体做一个 Excel 一对多, 联表查询的案例.

1, 需求:

现在有2个表

表1:

表2:

现在有6名选手参加了电子竞技比赛, 有人夺冠, 有人落榜. 现在要根据表2中是否夺冠, 筛选出表1中的夺冠选手的信息.

如果用VBA, 势必要写Sql语句, 各种内连接, 外连接, 说实话复杂的sql语句我也不会. 但是C# 下有很多非常好用的ORM, 比如sqlsugar, 就是一款轻量级的ORM, 只需要linq, 按几下tab 就出来查询结果了.

在这里我用Windows窗体做了一个小程序, 我会在稍后放在某度网盘分享给大家.

2, 思路: 使用NPOI将 Excel以DataTable的格式读入winform 使用sqlsugar 使用codeFirst生成数据库(sqlite是轻量级数据库,几Kb且无需安装) 使用sqlsugar将Excel的数据存入sqlite. 使用hasmap/dictionary生成外键使用sqlsugar进行一对多查询使用NPOI将查询的结果导出到excel

关键Nuget包: System.Sqlite, sqlsugar, NPOI, Newtonsoft.Json

过程:

选择Excel的Sheet数量(我是2个Sheet), 点击生成数据库

点击读取Excel(此时需要关闭你需要导入的Excel)

选中, 提示导入成功

点击插入数据库

点击生成外键: 可以看到外键生成成功了

点击一对多查询, 右边的DateGridView已经显示出筛选结果了, 获奖的选手只有4位.

导出到Excel

using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOIDemon { public class SqlSugarHelper { public static string ConnectionString = string.Empty; //必填, 数据库连接字符串 public static SqlSugarClient db { get => new SqlSugarClient(new ConnectionConfig() { ConnectionString = ConnectionString, DbType = DbType.Sqlite, //必填, 数据库类型 IsAutoCloseConnection = true, //默认false, 时候知道关闭数据库连接, 设置为true无需使用using或者Close操作 InitKeyType = InitKeyType.Attribute //默认SystemTable, codefist需要使用Attribute }); } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOIDemon { public class SqlSugarService { /// <summary> /// 设置连接字符串 /// </summary> /// <param name="ConnectionStr"></param> public static void SetConnectionStr(string ConnectionStr) { SqlSugarHelper.ConnectionString = ConnectionStr; } } } using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOIDemon { public class ExcelHelper { /// <summary> /// 从Excel读取数据 /// 只支持单表 /// </summary> /// <param name="FilePath">文件路径</param> public DataTable ReadFromExcel(string FilePath) { DataTable result = null; IWorkbook wk = null; string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名 try { using (FileStream fs = File.OpenRead(FilePath)) { if (extension.Equals(".xls")) //2003 { wk = new HSSFWorkbook(fs); } else //2007以上 { wk = new XSSFWorkbook(fs); } } //读取当前表数据 ISheet sheet = wk.GetSheetAt(0); //构建DataTable IRow row = sheet.GetRow(0); result = BuildDataTable(row); if (result != null) { if (sheet.LastRowNum >= 1) { for (int i = 1; i < sheet.LastRowNum + 1; i++) { IRow temp_row = sheet.GetRow(i); if (temp_row == null) { continue; }//2019-01-14 修复行为空时会出错 List<object> itemArray = new List<object>(); for (int j = 0; j < result.Columns.Count; j++)//解决Excel超出DataTable列问题 lqwvje20181027 { //itemArray.Add(temp_row.GetCell(j) == null ? string.Empty : temp_row.GetCell(j).ToString()); itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决导入Excel 时间格式问题 lqwvje 20180904 } result.Rows.Add(itemArray.ToArray()); } } } return result; } catch (Exception ex) { return null; } } public DataTable ReadFromExcelBySheetNum(string FilePath,int num) { DataTable result = null; IWorkbook wk = null; string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名 try { using (FileStream fs = File.OpenRead(FilePath)) { if (extension.Equals(".xls")) //2003 { wk = new HSSFWorkbook(fs); } else //2007以上 { wk = new XSSFWorkbook(fs); } } //读取当前表数据 ISheet sheet = wk.GetSheetAt(num); //构建DataTable IRow row = sheet.GetRow(0); result = BuildDataTable(row); if (result != null) { if (sheet.LastRowNum >= 1) { for (int i = 1; i < sheet.LastRowNum + 1; i++) { IRow temp_row = sheet.GetRow(i); if (temp_row == null) { continue; }//2019-01-14 修复行为空时会出错 List<object> itemArray = new List<object>(); for (int j = 0; j < result.Columns.Count; j++)//解决Excel超出DataTable列问题 lqwvje20181027 { //itemArray.Add(temp_row.GetCell(j) == null ? string.Empty : temp_row.GetCell(j).ToString()); itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决导入Excel 时间格式问题 lqwvje 20180904 } result.Rows.Add(itemArray.ToArray()); } } } return result; } catch (Exception ex) { return null; } } /// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="sheetName">要导入的excel的sheet的名称</param> /// <returns>导入数据行数(包含列名那一行)</returns> public int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten) { int i = 0; int j = 0; int count = 0; ISheet sheet = null; IWorkbook workbook = null; using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); try { if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return -1; } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } workbook.Write(fs); //写入到excel return count; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return -1; } } } private static DataTable BuildDataTable(IRow Row) { DataTable result = null; if (Row.Cells.Count > 0) { result = new DataTable(); for (int i = 0; i < Row.LastCellNum; i++) { if (Row.GetCell(i) != null) { result.Columns.Add(Row.GetCell(i).ToString()); } } } return result; } /// <summary> /// 获取单元格类型 /// </summary> /// <param name="cell"></param> /// <returns></returns> private static object GetValueType(ICell cell) { if (cell == null) return null; switch (cell.CellType) { case CellType.Blank: //BLANK: return null; case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case CellType.Numeric: //NUMERIC: if (DateUtil.IsCellDateFormatted(cell)) { return cell.DateCellValue; } return cell.NumericCellValue; case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: cell.SetCellType(CellType.String); return cell.StringCellValue; default: return "=" + cell.CellFormula; } } } } using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOIDemon { public class Champion { [SugarColumn(IsPrimaryKey = true, IsIdentity = true, ColumnName = "序号")] public int Id { get; set; } [SugarColumn(ColumnName = "夺冠项目")] public string Game { get; set; } [SugarColumn(ColumnName = "姓名")]//数据库与实体不一样设置列名 public string Name { get; set; } [SugarColumn(ColumnName = "外键")] public int PlayerId { get; set; } [SugarColumn(IsIgnore = true)] public Player Player { get; set; } } } using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOIDemon { public class Player { [SugarColumn(IsPrimaryKey = true, IsIdentity = true, ColumnName = "序号")] public int Id { get; set; } [SugarColumn(ColumnName = "姓名")]//数据库与实体不一样设置列名 public string Name { get; set; } [SugarColumn(ColumnName = "学号")] public string Identity { get; set; } [SugarColumn(ColumnName = "家庭住址")] public string Adress { get; set; } [SugarColumn(IsIgnore = true)] public List<Champion> Champions { get; set; } = new List<Champion>(); } } using SqlSugar; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; using System.Windows.Forms; namespace NPOIDemon { //sqlsugar文档 //https://·/home/Doc?typeId=1226 public partial class FrmMain : Form { public FrmMain() { InitializeComponent(); this.Load += FrmMain_Load; } private void FrmMain_Load(object sender, EventArgs e) { SqlSugarService.SetConnectionStr("Data Source="+Application.StartupPath + str2); for (int i = 1; i < 11; i++) { cmb1.Items.Add(i); } } private string str2 = "\\Test.db;Pooling=true;FailIfMissing=false"; private List<DataTable> dt =new List<DataTable>(); private int num; private DataTable dtout; private void btn1_Click(object sender, EventArgs e) { ExcelHelper helper = new ExcelHelper(); OpenFileDialog ofd = new OpenFileDialog(); num = int.Parse(cmb1.Text); if (ofd.ShowDialog() == DialogResult.OK) { for (int i = 0; i < num; i++) { string filePath = ofd.FileName; //dt = helper.ReadFromExcel(filePath); DataTable dt1 = helper.ReadFromExcelBySheetNum(filePath, i); dt.Add(dt1); } } if (dt.Count==num) { MessageBox.Show("导入成功！"); } else { MessageBox.Show("导入失败！"); } } //Excel插入数据库 private void btnInsert_Click(object sender, EventArgs e) { SqlSugarClient Db = SqlSugarHelper.db; List<Dictionary<string, object>> dc1 = Db.Utilities.DataTableToDictionaryList(dt[0]);//0,1,2依次往后排 Db.Insertable(dc1).AS("Player").ExecuteReturnIdentity(); List<Dictionary<string, object>> dc2 = Db.Utilities.DataTableToDictionaryList(dt[1]);//5.0.23版本支持 Db.Insertable(dc2).AS("Champion").ExecuteReturnIdentity(); //Db.Fastest<DataTable>().AS("Package").BulkCopy("Package", dt); //Db.Insertable(dc).AS("Package").ExecuteReturnIdentity(); MessageBox.Show("插入成功!"); } private void Create_Click(object sender, EventArgs e) { CodeFist SqlSugarClient Db = SqlSugarHelper.db; Db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Player)); Db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Champion)); bool t = File.Exists(Application.StartupPath + @"/Test.db"); if (t) //File.Exists, 文件， Directory.Exists 文件夹 { MessageBox.Show("数据库生成成功！"); } else { MessageBox.Show("数据库生成失败！"); } } //一对多查询 private void ManyToOne_Click(object sender, EventArgs e) { SqlSugarClient Db = SqlSugarHelper.db; //多表查询 //需要注意的是，这两个是固定写法。 //子对象，主表关联字段，从表关联字段,有外键关联可以省略主表关联字段 //Expression<Func<T, TObject>> mapperObject, Expression<Func<T, object>> mainField, Expression<Func<T, object>> childField //对子对象过滤 //var list = Db.Queryable<Player>() // .Mapper(p => p.Champions, p => p.Name, p => p.Champions.First().Name) // .Mapper(p => // { // p.Champions = p.Champions.Where(c => c.Name == "黄慧明").ToList(); // }).ToList(); dtout = Db.Queryable<Player>() .Mapper(p => p.Champions, p => p.Champions.First().PlayerId) .Where(p => p.Champions.Count()>0) .ToDataTable(); //或者ToList //var list = Db.Queryable<Player>().Where(x => x.Id == 1).ToList(); dataGridView1.DataSource = dtout; } //生成外键 private void btnGKey_Click(object sender, EventArgs e) { SqlSugarClient Db = SqlSugarHelper.db; List<Player> players = Db.Queryable<Player>().ToList(); List<Champion> champions = Db.Queryable<Champion>().ToList(); Dictionary<string, int> t1 = new Dictionary<string, int>(); foreach (var item in players) { t1.Add(item.Name, item.Id); } foreach (var item in champions) { if (t1.ContainsKey(item.Name)) { item.PlayerId = t1[item.Name]; } } Db.Updateable(champions).ExecuteCommand(); MessageBox.Show("更新外键成功!"); } private void export_Click(object sender, EventArgs e) { FolderBrowserDialog folder = new FolderBrowserDialog(); if (folder.ShowDialog() == DialogResult.OK) { string dir = folder.SelectedPath + "\\数据导出" + DateTime.Now.ToString("yyyy-mm-dd-hh-mm") + ".xlsx"; ExcelHelper helper = new ExcelHelper(); int i = helper.DataTableToExcel(dtout, "测试", dir, true); MessageBox.Show(i.ToString()); } } } }

SQLsugar新语法:

using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOIDemon { public class Champion { [SugarColumn(IsPrimaryKey = true, IsIdentity = true, ColumnName = "序号")] public int Id { get; set; } [SugarColumn(ColumnName = "夺冠项目")] public string 夺冠项目 { get; set; } [SugarColumn(ColumnName = "姓名")]//数据库与实体不一样设置列名 public string Name { get; set; } [SugarColumn(ColumnName = "外键")] public int PlayerId { get; set; } //[SugarColumn(IsIgnore = true)] //public Player Player { get; set; } } } using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOIDemon { public class Player { [SugarColumn(IsPrimaryKey = true, IsIdentity = true, ColumnName = "序号")] public int Id { get; set; } [SugarColumn(ColumnName = "姓名")]//数据库与实体不一样设置列名 public string Name { get; set; } [SugarColumn(ColumnName = "学号")] public string Identity { get; set; } [SugarColumn(ColumnName = "家庭住址")] public string Adress { get; set; } //[SugarColumn(IsIgnore = true)] [Navigate(NavigateType.OneToMany, nameof(Champion.PlayerId))] public List<Champion> Champions { get; set; }= new List<Champion>(); } } using SqlSugar; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; using System.Windows.Forms; namespace NPOIDemon { //sqlsugar文档 //https://·/home/Doc?typeId=1226 public partial class FrmMain : Form { public FrmMain() { InitializeComponent(); this.Load += FrmMain_Load; } private void FrmMain_Load(object sender, EventArgs e) { SqlSugarService.SetConnectionStr("Data Source="+Application.StartupPath + str2); for (int i = 1; i < 11; i++) { cmb1.Items.Add(i); } } private string str2 = "\\Test.db;Pooling=true;FailIfMissing=false"; private List<DataTable> dt =new List<DataTable>(); private int num; private DataTable dtout; private void btn1_Click(object sender, EventArgs e) { ExcelHelper helper = new ExcelHelper(); OpenFileDialog ofd = new OpenFileDialog(); num = int.Parse(cmb1.Text); if (ofd.ShowDialog() == DialogResult.OK) { for (int i = 0; i < num; i++) { string filePath = ofd.FileName; //dt = helper.ReadFromExcel(filePath); DataTable dt1 = helper.ReadFromExcelBySheetNum(filePath, i); dt.Add(dt1); } } if (dt.Count==num) { MessageBox.Show("导入成功！"); } else { MessageBox.Show("导入失败！"); } } //Excel插入数据库 private void btnInsert_Click(object sender, EventArgs e) { SqlSugarClient Db = SqlSugarHelper.db; List<Dictionary<string, object>> dc1 = Db.Utilities.DataTableToDictionaryList(dt[0]);//0,1,2依次往后排 Db.Insertable(dc1).AS("Player").ExecuteReturnIdentity(); List<Dictionary<string, object>> dc2 = Db.Utilities.DataTableToDictionaryList(dt[1]);//5.0.23版本支持 Db.Insertable(dc2).AS("Champion").ExecuteReturnIdentity(); //Db.Fastest<DataTable>().AS("Package").BulkCopy("Package", dt); //Db.Insertable(dc).AS("Package").ExecuteReturnIdentity(); MessageBox.Show("插入成功!"); } private void Create_Click(object sender, EventArgs e) { CodeFist SqlSugarClient Db = SqlSugarHelper.db; Db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Player)); Db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Champion)); bool t = File.Exists(Application.StartupPath + @"/Test.db"); if (t) //File.Exists, 文件， Directory.Exists 文件夹 { MessageBox.Show("数据库生成成功！"); } else { MessageBox.Show("数据库生成失败！"); } } //一对多查询 private void ManyToOne_Click(object sender, EventArgs e) { SqlSugarClient Db = SqlSugarHelper.db; //多表查询 //需要注意的是，这两个是固定写法。 //子对象，主表关联字段，从表关联字段,有外键关联可以省略主表关联字段 //Expression<Func<T, TObject>> mapperObject, Expression<Func<T, object>> mainField, Expression<Func<T, object>> childField //对子对象过滤 //var list = Db.Queryable<Player>() // .Mapper(p => p.Champions, p => p.Name, p => p.Champions.First().Name) // .Mapper(p => // { // p.Champions = p.Champions.Where(c => c.Name == "黄慧明").ToList(); // }).ToList(); //dtout = Db.Queryable<Player>() // .Mapper(p => p.Champions, p => p.Champions.First().PlayerId) // .Mapper(p => { p.Champions = p.Champions.Where(p=>p.Game.Contains("魔兽争霸")).ToList(}) // .Where(p => p.Champions.Count()>1) // .ToDataTable(); //或者ToList dtout = Db.Queryable<Player>() .Includes(p => p.Champions) .Where(p=>p.Champions.Count()>1) .Where(p => p.Champions.Any(z => z.夺冠项目.Contains("魔兽"))) .ToDataTable(); //var list = Db.Queryable<Player>().Where(x => x.Id == 1).ToList(); dataGridView1.DataSource = dtout; } //生成外键 private void btnGKey_Click(object sender, EventArgs e) { SqlSugarClient Db = SqlSugarHelper.db; List<Player> players = Db.Queryable<Player>().ToList(); List<Champion> champions = Db.Queryable<Champion>().ToList(); Dictionary<string, int> t1 = new Dictionary<string, int>(); foreach (var item in players) { t1.Add(item.Name, item.Id); } foreach (var item in champions) { if (t1.ContainsKey(item.Name)) { item.PlayerId = t1[item.Name]; } } Db.Updateable(champions).ExecuteCommand(); MessageBox.Show("更新外键成功!"); } private void export_Click(object sender, EventArgs e) { FolderBrowserDialog folder = new FolderBrowserDialog(); if (folder.ShowDialog() == DialogResult.OK) { string dir = folder.SelectedPath + "\\数据导出" + DateTime.Now.ToString("yyyy-mm-dd-hh-mm") + ".xlsx"; ExcelHelper helper = new ExcelHelper(); int i = helper.DataTableToExcel(dtout, "测试", dir, true); MessageBox.Show(i.ToString()); } } } }

多级查询

using SqlSugar; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; using System.Windows.Forms; namespace NPOIDemon { //sqlsugar文档 //https://·/home/Doc?typeId=1226 public partial class FrmMain : Form { public FrmMain() { InitializeComponent(); this.Load += FrmMain_Load; } private void FrmMain_Load(object sender, EventArgs e) { SqlSugarService.SetConnectionStr("Data Source="+Application.StartupPath + str2); for (int i = 1; i < 11; i++) { cmb1.Items.Add(i); } } private string str2 = "\\Test.db;Pooling=true;FailIfMissing=false"; private List<DataTable> dt =new List<DataTable>(); private int num; private DataTable dtout; private void btn1_Click(object sender, EventArgs e) { ExcelHelper helper = new ExcelHelper(); OpenFileDialog ofd = new OpenFileDialog(); num = int.Parse(cmb1.Text); if (ofd.ShowDialog() == DialogResult.OK) { for (int i = 0; i < num; i++) { string filePath = ofd.FileName; //dt = helper.ReadFromExcel(filePath); DataTable dt1 = helper.ReadFromExcelBySheetNum(filePath, i); dt.Add(dt1); } } if (dt.Count==num) { MessageBox.Show("导入成功！"); } else { MessageBox.Show("导入失败！"); } } //Excel插入数据库 private void btnInsert_Click(object sender, EventArgs e) { SqlSugarClient db = SqlSugarHelper.db; db.Insertable(new RoomA() { RoomId = 1, RoomName = "北大001室", SchoolId = 1 }).ExecuteCommand(); db.Insertable(new RoomA() { RoomId = 2, RoomName = "北大002室", SchoolId = 1 }).ExecuteCommand(); db.Insertable(new RoomA() { RoomId = 3, RoomName = "北大003室", SchoolId = 1 }).ExecuteCommand(); db.Insertable(new RoomA() { RoomId = 4, RoomName = "清华001厅", SchoolId = 2 }).ExecuteCommand(); db.Insertable(new RoomA() { RoomId = 5, RoomName = "清华002厅", SchoolId = 2 }).ExecuteCommand(); db.Insertable(new RoomA() { RoomId = 6, RoomName = "清华003厅", SchoolId = 2 }).ExecuteCommand(); db.Insertable(new SchoolA() { SchoolId = 1, SchoolName = "北大" }).ExecuteCommand(); db.Insertable(new SchoolA() { SchoolId = 2, SchoolName = "清华" }).ExecuteCommand(); db.Insertable(new StudentA() { StudentId = 1, SchoolId = 1, Name = "北大jack" }).ExecuteCommand(); db.Insertable(new StudentA() { StudentId = 2, SchoolId = 1, Name = "北大tom" }).ExecuteCommand(); db.Insertable(new StudentA() { StudentId = 3, SchoolId = 2, Name = "清华jack" }).ExecuteCommand(); db.Insertable(new StudentA() { StudentId = 4, SchoolId = 2, Name = "清华tom" }).ExecuteCommand(); db.Insertable(new TeacherA() { SchoolId = 1, Id = 1, Name = "北大老师01" }).ExecuteCommand(); db.Insertable(new TeacherA() { SchoolId = 1, Id = 2, Name = "北大老师02" }).ExecuteCommand(); db.Insertable(new TeacherA() { SchoolId = 2, Id = 3, Name = "清华老师01" }).ExecuteCommand(); db.Insertable(new TeacherA() { SchoolId = 2, Id = 4, Name = "清华老师02" }).ExecuteCommand(); MessageBox.Show("插入成功!"); } private void Create_Click(object sender, EventArgs e) { CodeFist SqlSugarClient db = SqlSugarHelper.db; db.CodeFirst.InitTables<StudentA, RoomA, SchoolA, TeacherA>(); db.DbMaintenance.TruncateTable<StudentA>(); db.DbMaintenance.TruncateTable<RoomA>(); db.DbMaintenance.TruncateTable<SchoolA>(); db.DbMaintenance.TruncateTable<TeacherA>(); bool t = File.Exists(Application.StartupPath + @"/Test.db"); if (t) //File.Exists, 文件， Directory.Exists 文件夹 { MessageBox.Show("数据库生成成功！"); } else { MessageBox.Show("数据库生成失败！"); } } //一对多查询 private void ManyToOne_Click(object sender, EventArgs e) { SqlSugarClient db = SqlSugarHelper.db; //var list = db.Queryable<StudentA>().ToList(); //db.ThenMapper(list, stu => //{ // //如果加Where不能带有stu参数，stu参数写到 SetContext // //School表中的主键, sdent表中的外键,student表 // //Expression<Func<T, object>> // //thisFiled, Expression<Func<object>> mappingFiled, ParameterT parameter // stu.SchoolA = db.Queryable<SchoolA>().SetContext(scl => scl.SchoolId, () => stu.SchoolId, stu).FirstOrDefault(); // //stu.SchoolA = db.Queryable<SchoolA>().SetContext(scl => scl.SchoolId, () => stu.SchoolId, stu).FirstOrDefault(); //}); var treeRoot = db.Queryable<Tree>().Where(it => it.Id == 1).ToList(); //第一层 db.ThenMapper(treeRoot, item => { item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList(); }); //第二层 db.ThenMapper(treeRoot.SelectMany(it => it.Child), it => { it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList(); }); //第三层 db.ThenMapper(treeRoot.SelectMany(it => it.Child).SelectMany(it => it.Child), it => { it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList(); }); //var list = Db.Queryable<Player>().Where(x => x.Id == 1).ToList(); dataGridView1.DataSource = treeRoot; } //生成外键 private void btnGKey_Click(object sender, EventArgs e) { SqlSugarClient Db = SqlSugarHelper.db; List<Player> players = Db.Queryable<Player>().ToList(); List<Champion> champions = Db.Queryable<Champion>().ToList(); Dictionary<string, int> t1 = new Dictionary<string, int>(); foreach (var item in players) { t1.Add(item.Name, item.Id); } foreach (var item in champions) { if (t1.ContainsKey(item.Name)) { item.PlayerId = t1[item.Name]; } } Db.Updateable(champions).ExecuteCommand(); MessageBox.Show("更新外键成功!"); } private void export_Click(object sender, EventArgs e) { FolderBrowserDialog folder = new FolderBrowserDialog(); if (folder.ShowDialog() == DialogResult.OK) { string dir = folder.SelectedPath + "\\数据导出" + DateTime.Now.ToString("yyyy-mm-dd-hh-mm") + ".xlsx"; ExcelHelper helper = new ExcelHelper(); int i = helper.DataTableToExcel(dtout, "测试", dir, true); MessageBox.Show(i.ToString()); } } } }

链接:https://pan.baidu.com/s/1K79FpFUQ6WlQnLBNuK4exA? 提取码:9t8t