Dapper和EF学习
//Dapper方法
private static readonly string con = ""; /// <summary> /// 添加 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int Insert(UserInfo user) { using (IDbConnection conn = new SqlConnection(con)) { return conn.Execute("insert into UserInfo(User_Name,User_Sex,User_Phone,User_Hobby) value(@User_Name,@User_Sex,@User_Phone,@User_Hobby)", user); } } //查询 public static List<UserInfo> Query() { using (IDbConnection conn = new SqlConnection(con)) { return conn.Query<UserInfo>("select * from UserInfo").ToList(); } } //查询个数 public static int GetTotal() { using (IDbConnection conn = new SqlConnection(con)) { return (int)conn.Execute("select count(*) from UserInfo"); } } /// <summary> /// 带条件查询 /// </summary> /// <param name="user"></param> /// <returns></returns> public static UserInfo Query(UserInfo user) { using (IDbConnection conn = new SqlConnection(con)) { return conn.Query<UserInfo>("select * from UserInfo where User_Name=@User_Name",user).SingleOrDefault(); } } //删除 public static int Delete(UserInfo user) { using (IDbConnection connection = new SqlConnection(con)) { return connection.Execute("delete from UserInfo where Id=@Id", user); } } public static int Delete(List<UserInfo> user) { using (IDbConnection connection = new SqlConnection(con)) { return connection.Execute("delete from UserInfo where Id=@Id", user); } } public static int Update(UserInfo user) { using (IDbConnection connection = new SqlConnection(con)) { return connection.Execute("update UserInfo set User_Name=@User_Name where Id=@Id", user); } } //修改 public static int Update(List<UserInfo> user) { using (IDbConnection connection = new SqlConnection(con)) { return connection.Execute("update UserInfo set User_Name=@User_Name where Id=@Id", user); } }
//导出到execle
/// 导出数据到excel /// </summary> /// <param name="dataTable"></param> /// <param name="fileName"></param> /// <param name="sheetName"></param> public int DataTableExcel(DataTable dataTable, string fileName, string sheetName) { //创建EXCEL工作薄 IWorkbook workBook = new XSSFWorkbook(); //创建sheet文件表 ISheet sheet = workBook.CreateSheet(sheetName); #region 创建Excel表头 //创建表头 IRow header = sheet.CreateRow(0); for (int i = 0; i < dataTable.Columns.Count; i++) { //给表创建列单元格并填充列名称，此处只做首行列Columns创建和填充 ICell cell = header.CreateCell(i); cell.SetCellValue(SetColumnName(dataTable.Columns[i].ColumnName)); } #endregion #region 填充Excel单元格中的数据 //给工作薄中非表头填充数据，遍历行数据并进行创建和填充表格 for (int i = 0; i < dataTable.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1);//表示从整张数据表的第二行开始创建并填充数据，第一行已经创建。 for (int j = 0; j < dataTable.Columns.Count; j++)//遍历并创建每个单元格cell，将行数据填充在创建的单元格中。 { //将数据读到cell单元格中 ICell cell = row.CreateCell(j); cell.SetCellValue(dataTable.Rows[i][j].ToString());//对数据为null的情况进行处理 } } #endregion #region 工作流创建Excel文件 //工作流写入，通过流的方式进行创建生成文件 MemoryStream stream = new MemoryStream(); workBook.Write(stream); byte[] buffer = stream.ToArray(); using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write)) { try { fs.Write(buffer, 0, buffer.Length); fs.Flush(); } catch { //异常不做任何处理，好处是让客户感觉没有问题，缺点是不利于查找程序的问题，需要日志文件跟踪。 } finally { fs.Dispose();//出现异常时，手动释放fs写对象 stream.Dispose();//出现异常时，手动释放stream流对象，防止卡死的现象 } } return 1; #endregion } /// <summary> /// 辅助方法拼接列名，当然这个列名称可以直接在sql语句中采用column as "中文列名称"即可 /// </summary> /// <param name="name"></param> /// <returns></returns> private static string SetColumnName(string name) { string columnName = string.Empty; #region 匹配列名称 switch (name) { case "User_Name": columnName = "姓名"; break; case "User_Sex": columnName = "性别"; break; case "User_Phone": columnName = "手机号"; break; case "User_Hobby": columnName = "爱好"; break; } #endregion return columnName; }
//linq分页
var query = from UserInfo in user select UserInfo; var list = query.Take(pageindex * pagesize).Skip(pagesize * (pageindex - 1)).ToList();
将list转换为datatable类型
//list类型转换为datatable类型 public static DataTable ToDataTable<T>(this IList<T> data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable dt = new DataTable(); for (int i = 0; i < properties.Count; i++) { PropertyDescriptor property = properties[i]; dt.Columns.Add(property.Name, property.PropertyType); } object[] values = new object[properties.Count]; foreach (T item in data) { for (int i = 0; i < values.Length; i++) { values[i] = properties[i].GetValue(item); } dt.Rows.Add(values); } return dt; }
//EF操作
//查询 List<RedPacketViewModel> list = new List<RedPacketViewModel>(); using (var ef = new Model1()) { list = (from s in ef.RedPacket select new RedPacketViewModel { ID = s.ID, ActivityName = s.ActivityName, Balance = s.Balance, BalanceNum = s.BalanceNum, Common = s.Common, CreateTime = s.CreateTime, Name = s.Name, Num = s.Num, Number = s.Number, Remark = s.Remark, State = s.State, Type = s.Type }).ToList(); //添加 int flag = 0; using (var ef = new Model1()) { RedPacket red = new RedPacket { ActivityName = m.ActivityName, Balance = m.Balance, BalanceNum = m.BalanceNum, Common = m.Common, CreateTime = DateTime.Now, Name = m.Name, Num = m.Num, Number = m.Number, Remark = m.Remark, State = m.State, Type = m.Type }; ef.Entry(red).State = System.Data.Entity.EntityState.Added; flag = ef.SaveChanges(); } return flag; //删除 int falg = 0; using (var ef = new Model1()) { var list = (from s in ef.RedPacket where s.ID == m.ID select s).FirstOrDefault(); ef.Entry(list).State = System.Data.Entity.EntityState.Deleted; falg = ef.SaveChanges(); } return falg; //修改 int falg = 0; using (var ef = new Model1()) { var list = (from s in ef.RedPacket where s.ID == m.ID select s).FirstOrDefault(); list.Name = m.Name; list.Num = m.Num; list.Number = m.Number; list.Remark = m.Remark; list.ActivityName = m.ActivityName; list.Balance = m.Balance; list.BalanceNum = m.BalanceNum; list.Common = m.Common; list.State = m.State; list.Type = m.Type; ef.Entry(list).State = System.Data.Entity.EntityState.Modified; falg = ef.SaveChanges(); } return falg;
