随手记录一个从Excel导入到DB的Example

 public class Program
    {
        public static string path = @"D:/ProjectFiles/ZJ/卫生部诊疗科目/康复医院科室代码及名称于卫生部的对照关系.xlsx";
        public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

        static void Main(string[] args)
        {
            //构建连接字符串
            OleDbConnection Conn = new OleDbConnection(connStr);
            Conn.Open();
            //填充数据
            string sql = string.Format("select * from [{0}$]", "诊疗单元与病房对应");
            OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
            DataSet ds = new DataSet();
            da.Fill(ds);
            Conn.Close();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                string nwCode = dr["病区编码"].ToString();
                string nwName = dr["病区名称"].ToString();

                string fgName = dr["核算小组编码"].ToString();
                Guid nwGUID = Guid.NewGuid();
                using (DataClasses1DataContext db = new DataClasses1DataContext())
                {

                    db.NursingWard.InsertOnSubmit(new NursingWard
                    {
                        ID = nwGUID,
                        Code = nwCode,
                        Name = nwName,
                        OpenBeds = 0,
                        CheckedBeds = 0,
                        NursingType = 0,
                        Status = 1
                    });

                    Financial_Group fgmodel = (from p in db.Financial_Group
                                               where p.Code == fgName
                                               select p).FirstOrDefault();
                    if (fgmodel != null)
                    {
                        FinancialGroup_NursingWard_Relation realtion = new FinancialGroup_NursingWard_Relation
                        {
                            FinancialGroup_ID = fgmodel.ID,
                            NursingWard_ID = nwGUID,
                            OpenBeds = 0,
                            CheckedBeds = 0,
                            Status = 1
                        };
                        db.FinancialGroup_NursingWard_Relation.InsertOnSubmit(realtion);
                    }
                    //缺省情况下，DataContext.SubmitChanges() 就会自动启动一个事务，确保所有操作在同一个事务下进行
                    db.SubmitChanges();
                }
            }// end of foreach

            Console.WriteLine("看你妹");
            Console.Read();
        }
    }

 

Excle数据