Linq杂谈之 — Linq导入Excel

     今天接到一个需求，实现一个小功能—导入Excel，想想诸位活跃于.NET平台上的兄弟们，其中应该有相当一部分是从事如信息系统类开发的，所以小弟在这里姑且臭屁一下导入Excel的几种实现方法，如有错误之处，烦请大虾指正。

      第一步呢当然是将Excel中的数据导入进DataTable里面，这一步是很简单的，贴下Code。之后开始讨论如何将DataTable中的这些数据放进数据库里面:

// 读取Excel
    public static DataTable GetExcelFileData(string filePath)
    {
        OleDbDataAdapter oleAdp = new OleDbDataAdapter();
        OleDbConnection oleCon = new OleDbConnection();
        string strCon = "Provider=Microsoft.Jet.oleDb.4.0;data source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        try
        {
            DataTable dt = new DataTable();
            oleCon.ConnectionString = strCon;
            oleCon.Open();
            DataTable table = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = table.Rows[0][2].ToString();
            string sqlStr = "Select * From [" + sheetName + "]";
            oleAdp = new OleDbDataAdapter(sqlStr, oleCon);
            oleAdp.Fill(dt);
            oleCon.Close();
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            oleAdp = null;
            oleCon = null;
        }
    }

 

      将DataTable中的数据放到到数据库大概有如下几种实现方法：

1、 Excel导入 — 循环执行插入操作

      这种方法问题颇多，而且效率很低，需要一次次在循环里面执行插入操作，我以往做法是先定义一个Insert语句，然后在循环里面不断的改变Insert 语句中参数的值，执行插入操作，当然这里是使用了Microsoft 提供的SqLHelper的ExecuteNonQuery执行Insert语句。这样做的问题是每一个Insert语句都要执行一次数据库的连接，而这种连接系统维护了一个对象池，每一个连接都先从对象池中取出，如果对象池中没有将创建新对象，并加入对象池。而对象池中的无用对象需要一定的时间后才能释放。所以如果你在短时间内使用的连接很多的话，就有可能造成对象池满员。你再需要连接的话就需要等待释放，而你等待的时间远比释放的时间短，这时系统就会告诉你连接超时，系统抛出异常了，但是即便不抛出异常，这样反复的打开关闭数据的连接，也极大的影响了系统的性能。

      当然也有很多朋友没有使用SqLHelper 去处理这样的数据导入操作，而是将数据库连接放在了循环的方面，等循环里面的Insert语句执行完了，再释放连接，这样做的效率虽比上面的方法效率高些，但同样的问题就是为插入的每个记录执行一次与数据库服务器的往返，如果数据量大的话，对系统的性能同样是一个挑战。

 

2、 Excel导入 — 使用SqlBulkCopy的WriteToServer方法，批量导入数据

      这种做法的缺点是要求EXCEL的格式必须与要导入的Table完全一致而且还要删除多余的Sheet，但是他在性能上优于上面的方法，它可以通过让DataSet或是DataReader中大量的数据通过数据流直接进行装载，然后可以将这些记录添加到指定的数据表中。

      使用方法：

 　　   WriteToServer(DataTable)写入数据表

　　    WriteToServer(DataRow)批次写入数据行

　　    WriteToServer(DataTable,DataRowState)按行状态写入数据库表

　　    WriteToServer(DataReader)写入DataReader对象

     同时SqlBulkCopy对象有个BatchSize这两个属性，BatchSize属性是非常重要的，程序性能如何主要就依靠着它。 BatchSize的意思就是同每一批次中的行数，在每一批次结束时，就将该批次中的行发送到数据库。譬如将BatchSize属性设置成了500，其意思就是每读出500行数据就将他们发送到数据库从而执行批量复制的操作。 BatchSize的默认值是“1”，其意思就是把每一行作为一个批次发送到数据库

     这种做法，也是网上大多数朋友所推荐的的数据移植方法。

3、 Excel导入 — 使用Linq提供的InsertAllOnSubmit方法（这里将详细介绍这种方法）

      InsertAllOnSubmit方法是将一个实体集合添加到datacontext对象中,并在SubmitChange()的时候执行更改，这里我还没来的及做性能测试，有空一定补上，先贴上Code：

public static void excelInsertDataForInsert(DataTable dtExcel,string UserName)
    {
        using (MAUCPODataContext db = new MAUCPODataContext())
        {
            Dictionary<string, string> listPN_Lavel=getPN_Lave();
            var query = from q in dtExcel.AsEnumerable()
                        where !string.IsNullOrEmpty(q["POPGI_T"].ToString().Trim()) &&
                              !string.IsNullOrEmpty(q["PONo"].ToString().Trim()) &&
                              !string.IsNullOrEmpty(q["PN"].ToString().Trim()) &&
                              !string.IsNullOrEmpty(q["QTY"].ToString().Trim()) &&
                              !string.IsNullOrEmpty(q["Price"].ToString().Trim())
                        select new
                        {
                            POPGI_T = q["POPGI_T"].ToString().Trim(),
                            PONo = q["PONo"].ToString().Trim(),
                            PN = q["PN"].ToString().Trim(),
                            QTY = q["QTY"].ToString().Trim(),
                            Price = q["Price"].ToString().Trim()
                        };
            List<MAUCPO> listEntity = new List<MAUCPO>();
            foreach (var q in query)
            {
                MAUCPO Entity = new MAUCPO();
                Entity.CREATE_t = DateTime.Now;
                Entity.CreateBy = UserName;
                Entity.PN = q.PN;
                Entity.PONo = q.PONo;
                Entity.POPGI_T = DateTime.Parse(q.POPGI_T);
                Entity.Price = decimal.Parse(q.Price);
                Entity.Priority = int.Parse(listPN_Lavel[q.PN]);
                Entity.ProcessFlag = 'N';
                Entity.QTY = int.Parse(q.QTY);
                listEntity.Add(Entity);
            }
            db.MAUCPO.InsertAllOnSubmit(listEntity);
            db.SubmitChanges();
        }
    }

 

      首先需要将你传入的DataTable转换成Enumerable类型，这样才能执行Linq查询，这里where的条件是为了过滤掉DataTable中的空行，select中的内容是取出DataTable中对应列的数据，最后将查询出来的数据转换成泛型类型listEntity，然后调用InsertAllOnSubmit(listEntity)方法，执行批量插入操作。

       至于InsertAllOnSubmit的效率问题，网上也没有搜到太详细的介绍，有时间一定用要测下大数据量下的InsertAllOnSubmit方法效率如何。