博客

先定义两个类
 public class Grade
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }


    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set;}
        public string Sex { get; set; }
        public string Email { get; set; }

        public int GradeId { get; set; }
        public Grade Grade { get; set; }
    }
//用EF做
 public class SchoolDB:DbContext
    {
        public DbSet<Grade> Grades { get; set; }
        public DbSet<Student> Students { get; set; }
    }
    //创建一个日志类
      public class LogHelper
    {
        public static void WriteLog(string msg)
        {
            FileStream fs = new FileStream(@"C:\Log.data", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine(DateTime.Now.ToLongTimeString() + ":" + msg);
            sw.Flush();
            sw.Close();
            fs.Close();
        }
    }
   //创建一个EFhelper类
    public class EFHelper<T> where T : class, new()
    {
        SchoolDB db = new SchoolDB();
        public int Add(T t) 
        {
            db.Set<T>().Add(t);
            return db.SaveChanges();
        }

        public int Update(T t)
        {
            db.Set<T>().Attach(t);
            db.Entry<T>(t).State = System.Data.Entity.EntityState.Modified;
            return db.SaveChanges();
        }

        public int Delete(int Id)
        {
            T t = db.Set<T>().Find(Id);
            db.Set<T>().Remove(t);
            return db.SaveChanges();
         

        }

        public List<T> GetAll()
        {
            return db.Set<T>().ToList();
        }

        public T GetEntity(int Id)
        {
            return db.Set<T>().Find(Id);
        }
   //创建学生控制器
    EFHelper<Student> dal = new EFHelper<Student>();//实例化
        public int Post(Student stu)
        {
            LogHelper.WriteLog("添加学生");
            return dal.Add(stu);
        }

        public int Put(Student stu)
        {
            LogHelper.WriteLog("修改学生");
            return dal.Update(stu);
        }

        public int Delete(int Id)
        {
            LogHelper.WriteLog("删除学生");
            return dal.Delete(Id);
        }

        public List<Student> Get()
        {
            LogHelper.WriteLog("查询全部学生");
            return dal.GetAll();
        }

        public Student Get(int Id)
        {
            LogHelper.WriteLog("查询学生"+Id);
            return dal.GetEntity(Id);
        }
   //班级控制器
    EFHelper<Grade> dal = new EFHelper<Grade>();
        public List<Grade> Get()
        {
            return dal.GetAll();
        }
        //用Httpclienthelper连接Api
          public class HttpClientHelper
    {
        public HttpClientHelper(string baseAddr)
        {
            this.BaseAddr = baseAddr;
        }
        public  string BaseAddr { get; set; }

        public  string Get(string Url)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            //发送GET请求
            HttpResponseMessage msg =  client.GetAsync(Url).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }

        public  string Delete(string Url)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            //发送GET请求
            HttpResponseMessage msg = client.DeleteAsync(Url).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }

        public  string Post(string Url,string JsonData)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            //设置消息体
            HttpContent content = new StringContent(JsonData);
            content.Headers.ContentType = new MediaTypeHeaderValue("application/json");

            //发送Post请求
            HttpResponseMessage msg = client.PostAsync(Url,content).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }

        public  string Put(string Url, string JsonData)
        {
            HttpClient client = new HttpClient();
            //设置 API的 基地址
            client.BaseAddress = new Uri(BaseAddr);
            //设置 默认请求头ACCEPT
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            //设置消息体
            HttpContent content = new StringContent(JsonData);
            content.Headers.ContentType = new MediaTypeHeaderValue("application/json");

            //发送Post请求
            HttpResponseMessage msg = client.PutAsync(Url, content).Result;
            //判断结果是否成功
            if (msg.IsSuccessStatusCode)
            {
                //返回响应结果
                return msg.Content.ReadAsStringAsync().Result;
            }
            //返回空字符串，表示响应错误
            return "";
        }

        public T Request<T>(string type, string url, object data = null)
        {
            string json = JsonConvert.SerializeObject(data);
            string result = "";
            switch (type)
            {
                case "Get":
                    result = Get(url);
                    break;
                case "Post":
                    result = Post(url, json);
                    break;
                case "Put":
                    result = Put(url, json);
                    break;
                case "Delete":
                    result = Delete(url);
                    break;
            }
            return JsonConvert.DeserializeObject<T>(result);
        }
    }
    
    //创建班级dal
      public class GradeDal
    {
        HttpClientHelper helper = new HttpClientHelper("http://localhost:59120/");
        public List<Grade> Get()
        {
            string result = helper.Get("api/Grade");
            return JsonConvert.DeserializeObject<List<Grade>>(result);
        }
    }
    
//创建学生Dal
  public class StudentDal
    {
        HttpClientHelper helper = new HttpClientHelper("http://localhost:59120/");
        public int Post(Student stu)
        {
            string result = helper.Post("api/Student", JsonConvert.SerializeObject(stu));
            return int.Parse(result);
        }

        public int Put(Student stu)
        {
            string result = helper.Put("api/Student", JsonConvert.SerializeObject(stu));
            return int.Parse(result);
        }

        public int Delete(int Id)
        {
            string result = helper.Delete("api/Student/"+Id);
            return int.Parse(result);
        }

        public List<Student> Get()
        {
            string result = helper.Get("api/Student");
            return JsonConvert.DeserializeObject<List<Student>>(result);
        }

        public Student Get(int Id)
        {
            string result = helper.Get("api/Student/"+Id);
            return JsonConvert.DeserializeObject<Student>(result);
        }
    }
//拉一个导入导出类
 public class NPOIHelper
    {
        //将Excel中文件导入到C#项目
        /// <summary>
        /// 导入EXCEL文件
        /// </summary>
        /// <typeparam name="T">指定对应的实体类</typeparam>
        /// <param name="fileName">EXCEL文件名</param>
        /// <param name="dic">字段标题对应表</param>
        /// <returns>list集合</returns>
        public static List<T> Import<T>(string fileName,Dictionary<string ,string > dic) where T:new ()
        {
            List<T> list = new List<T>();
            Type tp = typeof(T);

            using (var fs = File.OpenRead(fileName))
            {
                //把xls文件中的数据写入workbook1中
                var workbook1 = new HSSFWorkbook(fs);
                //获取第一个Sheet
                var sheet = workbook1.GetSheetAt(0);
                //获取第一行 标题行
                var row = sheet.GetRow(0);
                //声明字段数组
                string[] fields = new string[row.LastCellNum];

                for (var i = 0; i < row.LastCellNum; i++)
                {
                    string title = row.GetCell(i).ToString();
                    fields[i] = dic[title];
                }
                for (var j = 1; j <= sheet.LastRowNum; j++)
                {
                    //读取当前行数据
                    var dataRow = sheet.GetRow(j);
                    // 创建对象实例
                    T t = new T();
                    if (dataRow != null)
                    {
                        for (var k = 0; k < row.LastCellNum; k++)
                        {   //当前表格 当前单元格 的值
                            var cell = dataRow.GetCell(k);
                            if (cell != null)
                            {
                                var p = tp.GetProperty(fields[k]);
                                p.SetValue(t, GetValue(cell.ToString(),p));
                            }
                        }
                    }
                    list.Add(t);
                }
            }
            return list;
        }

        private static object GetValue(string obj, PropertyInfo p)
        {
            object o = null;
            switch (p.PropertyType.Name)
            {
                case "Int16":
                    o= Int16.Parse(obj);
                    break;
                case "Int32":
                    o = Int32.Parse(obj);
                    break;
                case "Int64":
                    o = Int64.Parse(obj);
                    break;
                case "double":
                    o = double.Parse(obj);
                    break;
                case "float":
                    o = float.Parse(obj);
                    break;
                case "String":
                    o = obj.ToString();
                    break;
                case "bool":
                    o = bool.Parse(obj);
                    break;
                case "DateTime":
                    o = DateTime.Parse(obj);
                    break;
            }
            return o;
        }
        //导出内容到Excel文件


        public static void Export<T>(string fileName, List<T> list, Dictionary<string, string> dic)
        {
            Type tp = typeof(T); //获取类型
            var ps = tp.GetProperties(); //获取属性集合

            //创建工作薄
            var workbook = new HSSFWorkbook();
            //创建表
            var table = workbook.CreateSheet("sheet1");
            //创建表头
            var row = table.CreateRow(0);
            for (int i = 0; i < ps.Length; i++)
            {
                var cell = row.CreateCell(i);//创建单元格
                cell.SetCellValue(dic[ps[i].Name]);
            }

            //模拟20行20列数据
            for (var i = 1; i <= list.Count; i++)
            {
                //创建新行
                var dataRow = table.CreateRow(i);

                for (int j = 0; j < ps.Length ; j++)
                {
                    if (ps[j].PropertyType.IsValueType || (ps[j].PropertyType.Name.Equals("String")))
                    {
                        
                        var cell = dataRow.CreateCell(j);//创建单元格
                        cell.SetCellValue(ps[j].GetValue(list[i - 1]).ToString());
                    }
                    else
                    {
                        var cell = dataRow.CreateCell(j);//创建单元格
                        cell.SetCellValue("");

                    }
                }
            }
            //打开xls文件，如没有则创建，如存在则打开该文件
            using (var fs = File.OpenWrite(fileName))
            {
                workbook.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
                Console.WriteLine("生成成功");
            }
        }
    }
//Form1 页面
  public partial class Form1 : Form
    {
        StudentDal dal = new StudentDal();

        public Form1()
        {
            InitializeComponent();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            FrmAdd frm = new FrmAdd();
            frm.ShowDialog();
            this.dataGridView1.DataSource = dal.Get();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
            this.dataGridView1.DataSource = dal.Get();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.SelectedRows.Count > 0)
            {
                int Id = (int)this.dataGridView1.SelectedRows[0].Cells[0].Value;
                FrmEdit frm = new FrmEdit();
                frm.Id = Id;
                frm.ShowDialog();

                this.dataGridView1.DataSource = dal.Get();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.SelectedRows.Count > 0)
            {
                int Id = (int)this.dataGridView1.SelectedRows[0].Cells[0].Value;
                int result = dal.Delete(Id);
                if (result > 0)
                    MessageBox.Show("删除成功");
                this.dataGridView1.DataSource = dal.Get();
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
           
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string fileName = this.openFileDialog1.FileName;
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("学号", "Id");
                dic.Add("姓名", "Name");
                dic.Add("年龄", "Age");
                dic.Add("性别", "Sex");
                dic.Add("邮箱", "Email");
                dic.Add("年级", "GradeId");
               

                List<Student> stus = NPOIHelper.NPOIHelper.Import<Student>(fileName, dic);
                foreach (var s in stus)
                {
                    dal.Post(s);
                }
            }
            this.dataGridView1.DataSource = dal.Get();

        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            if (this.saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string fileName = this.saveFileDialog1.FileName;
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add( "Id","学号");
                dic.Add( "Name","姓名");
                dic.Add( "Age","年龄");
                dic.Add( "Sex", "性别");
                dic.Add( "Email", "邮箱");
                dic.Add( "GradeId", "年级");
                dic.Add( "Grade", "年级");

                List<Student> stus = dal.Get();

                NPOIHelper.NPOIHelper.Export<Student>(fileName, stus , dic);
               
            }
        }
    }
//Form2页面 添加
 public partial class FrmAdd : Form
    {
        GradeDal gdal = new GradeDal();
        StudentDal sdal = new StudentDal();

        public FrmAdd()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Student stu = new Student();
            stu.Name = this.textBox1.Text;
            stu.Age = int.Parse(this.textBox2.Text);
            stu.Email = this.textBox3.Text;
            stu.Sex = this.radioButton1.Checked ? "男" : "女";

            stu.GradeId = (int)this.comboBox1.SelectedValue;

            int result = sdal.Post(stu);
            
        }

        private void FrmAdd_Load(object sender, EventArgs e)
        {
            this.comboBox1.DataSource = gdal.Get();
            this.comboBox1.DisplayMember = "Name";
            this.comboBox1.ValueMember = "Id";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            
        }
    }
    //Form3页面 修改
     public partial class FrmEdit : Form
    {
        public int Id { get; set; }

        GradeDal gdal = new GradeDal();
        StudentDal sdal = new StudentDal();

        public FrmEdit()
        {
            InitializeComponent();
        }

        private void FrmEdit_Load(object sender, EventArgs e)
        {
            this.comboBox1.DataSource = gdal.Get();
            this.comboBox1.DisplayMember = "Name";
            this.comboBox1.ValueMember = "Id";


            Student s = sdal.Get(Id);
            this.textBox1.Text = s.Name;
            this.textBox2.Text = s.Age.ToString();
            this.textBox3.Text = s.Email;
            this.radioButton1.Checked = s.Sex == "男";
            this.radioButton2.Checked = s.Sex == "女";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Student stu = new Student();
            stu.Id = Id;
            stu.Name = this.textBox1.Text;
            stu.Age = int.Parse(this.textBox2.Text);
            stu.Email = this.textBox3.Text;
            stu.Sex = this.radioButton1.Checked ? "男" : "女";

            stu.GradeId = (int)this.comboBox1.SelectedValue;

            sdal.Put(stu);

            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }