最便捷的上传文件到数据库执行增删改查

注意：本文章使用linq语句，文件流两个技术。

直接用一个功能来概述吧，这也是第四十五届世界技能大赛的赛题，领导发了一个文档给我，里面有某些字段让我实现通过上传这个文件然后执行数据库，说不清楚上文件，上面Edit、Add这是他要执行的操作。

设计的界面：Succesfual changes Applied：成功执行的条数
Duplicate records discarded：重复丢弃的条数
Record with missing fields discarded：缺少字段不执行的条数
下面代码的s1 s2 s3的代表就是三个。

代码：

 private void button3_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "*.csv|*.CSV";
            if (openFileDialog.ShowDialog()==DialogResult.OK)
            {
                textBox1.Text = openFileDialog.FileName;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Trim()!="")
            {
                using (Session3Entities1 db = new Session3Entities1())
                {
                    int s1=0,  s2=0, s3 = 0;
                    try
                    {
                        FileStream fs = new FileStream(textBox1.Text.Trim(),FileMode.Open);
                        StreamReader reader = new StreamReader(fs);
                        string line = "";
                        while ((line=reader.ReadLine())!=null)
                        {
                            try
                            {
                                string[] datas = line.Split(',');
                                if (datas[0]=="ADD")
                                {
                                    string fn = datas[3];
                                    Schedules schedules = db.Schedules.FirstOrDefault(s=>s.FlightNumber==fn);
                                    if (schedules!=null)
                                    {
                                        s2++;
                                        continue;
                                    }
                                    string ia1 = datas[4], ia2 = datas[5];
                                    db.Schedules.Add(new Schedules { 
                                        Date=Convert.ToDateTime(datas[1]),
                                        Time=TimeSpan.Parse(datas[2]),
                                        FlightNumber=datas[3],
                                        RouteID=db.Routes.FirstOrDefault(r=>r.Airports.IATACode==ia1 && r.Airports1.IATACode==ia2).ID,
                                        AircraftID=Convert.ToInt32(datas[6]),
                                        EconomyPrice=Convert.ToDecimal(datas[7]),
                                        Confirmed=datas[8]=="OK" ? true :false
                                    });
                                }

                                if (datas[0]=="EDIT")
                                {
                                    string fn = datas[3];
                                    Schedules schedules = db.Schedules.FirstOrDefault(s => s.FlightNumber == fn);
                                    if (schedules!=null)
                                    {
                                        s2++;
                                        continue;
                                    }
                                    string ia1 = datas[4], ia2 = datas[5];
                                    schedules.Date = Convert.ToDateTime(datas[1]);
                                    schedules.Time = TimeSpan.Parse(datas[2]);
                                    schedules.RouteID = db.Routes.FirstOrDefault(r=>r.Airports.IATACode==ia1 && r.Airports1.IATACode==ia2).ID;
                                    schedules.AircraftID = Convert.ToInt32(datas[6]);
                                    schedules.EconomyPrice = Convert.ToDecimal(datas[7]);
                                    schedules.Confirmed = datas[8] == "OK" ? true : false;
                                }
                                db.SaveChanges();
                                s1++;

                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex.Message);
                                s3++;
                            }
                        }
                        reader.Close();
                        fs.Close();
                        label5.Text = "[" + s1 + "]";
                        label6.Text = "[" + s2 + "]";
                        label7.Text = "[" + s3 + "]";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        throw;
                    }

                }
            }
        }

这是我见过最方便的，来源于不知道比赛时哪个大哥的，要来看的，顺便做一个笔记，感谢感谢。