C# DataTable数据遍历优化

欢迎使用Markdown编辑器写博客

我们在进行开发时，会经常使用DataTable来存储和操作数据，我发现在遍历DataTable并对数据进行删除和添加操作时速度非常慢，查阅相关资料并测试在添加主键后可以使遍历和操作速度提高很多：
测试代码，测试的是我们向取出来数据满足Flag！=1条件的所有数据的后面添加一条数据(因为这条数据的一些字段值是根据前面的几条满足条件[“AccID=’” + accID + “’ AND Y=’” + year + “’ AND AbsID <= ” + absID;]数据的值累加得到的)所以需要进行整个DataTable的遍历来计算添加：

public static void Test2()
{
    Stopwatch watch = new Stopwatch();

    using (DbConnection conn = SqlHelper.GetConnection("ConnectionString"))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            watch.Start();
            cmd.CommandText = string.Format(@"
select  ROW_NUMBER() OVER (Order by S.AccID,S.CurrID,S.AbsID,S.Flag)AS RowNum,S.* from Test S 
");
            cmd.Connection = conn as SqlConnection;
            cmd.CommandTimeout = 60000;
            conn.Open();
            DataTable table = ExecuteDataTable(cmd);
            watch.Stop();
            Console.WriteLine("从数据库取出数据{0}条", table.Rows.Count);
            Stopwatch watch2 = new Stopwatch();
            watch2.Start();
            DataTable newTable = HandleAccYear(table,true);
            watch2.Stop();
            Console.WriteLine("数据{0},遍历操作时间：毫秒：{1}，秒：{2}", newTable.Rows.Count, watch2.ElapsedMilliseconds, watch2.ElapsedMilliseconds / 1000);
        }
        conn.Close();
    }
}

填充数据到DataTable的方法

public static DataTable ExecuteDataTable(SqlCommand cmd)
{
      DataTable table = new DataTable();
      SqlDataAdapter adaper = new SqlDataAdapter(cmd);
      adaper.Fill(table);
      return table;
}

 private static DataTable HandleAccYear(DataTable dt, bool isCurrency)
{
     DataTable newdt = dt.Clone();
     //不使用主键
     //dt.PrimaryKey = new DataColumn[] {
     //    dt.Columns["AccID"],
     //    dt.Columns["Flag"],
     //    dt.Columns["AbsID"],
     //    dt.Columns["RowNum"],
     //};
     if (dt.Rows.Count > 0)
     {
         object flag = null;
         foreach (DataRow row in dt.Rows)
         {
             flag = row["Flag"];
             if (flag != null && !Helper.AreEqual(flag.ToString(), "1"))
             {

                 DataRow newRow = newdt.NewRow();
                 DataRow sourceRow = newdt.NewRow();
                 sourceRow.ItemArray = row.ItemArray;
                 newRow.ItemArray = row.ItemArray;
                 string accID = row["AccID"].ToString(),
                     year = row["Y"].ToString(),
                     absID = row["AbsID"].ToString();
                 newRow["Flag"] = "5";
                 newRow["SumInfo"] = "测试数据";
                 string filter = "AccID='" + accID + "' AND Y='" + year + "' AND AbsID <= " + absID;
                 if (!isCurrency)
                 {
                     filter = "AccID='" + accID + "'AND CurrID='" + row["CurrID"] + "' AND Y='" + year + "' AND AbsID <= " + absID;
                 }
                 DataRow[] selectRow = dt.Select(filter);
                 double debitLC = 0, debitQty = 0, creditLC = 0, creditQty = 0, debitFC = 0, creditFC = 0;
                 foreach (DataRow item in selectRow)
                 {
                     debitLC += ToDouble(item["YearDebitLC"]);
                     debitQty += ToDouble(item["YearDebitQty"]);
                     creditLC +=ToDouble(item["YearCreditLC"]);
                     creditQty += ToDouble(item["YearCreditQty"]);
                     if (!isCurrency)
                     {
                         debitFC += ToDouble(item["YearDebitFC"]);
                         creditFC += ToDouble(item["YearCreditFC"]);
                     }
                 }
                 newRow["CurDebitLC"] = debitLC;
                 newRow["CurDebitQty"] = debitQty;
                 newRow["CurCreditLC"] = creditLC;
                 newRow["CurCreditQty"] = creditQty;
                 //newRow["CurDebitLC"] = dt.Compute("Sum(YearDebitLC)", filter);
                 //newRow["CurDebitQty"] = dt.Compute("Sum(YearDebitQty)", filter);
                 //newRow["CurCreditLC"] = dt.Compute("Sum(YearCreditLC)", filter);
                 //newRow["CurCreditQty"] = dt.Compute("Sum(YearCreditQty)", filter);
                 if (!isCurrency)
                 {
                     //newRow["CurCreditFC"] = dt.Compute("Sum(YearCreditFC)", filter);
                     //newRow["CurDebitFC"] = dt.Compute("Sum(YearDebitFC)", filter);
                     newRow["CurCreditFC"] = creditFC;
                     newRow["CurDebitFC"] = debitFC;
                 }
                 newdt.Rows.Add(sourceRow);
                 newdt.Rows.Add(newRow);
             }
             else
             {
                 DataRow sourceRow = newdt.NewRow();
                 sourceRow.ItemArray = row.ItemArray;
                 newdt.Rows.Add(sourceRow);
             }
         }
     }
     return newdt;
 }

当不使用主键进行遍历计算插入相应的值时所用时间竟然是这么多：

当我使用同样的方法，同样的数据添加主键(即把HandleAccYear方法中不使用主键下面的注释去掉后).进行遍历计算等操作，得出的结果竟然有这么大的差别：