每秒更新3万条记录

  #region Access遍历更新法，过于消耗CPU （15台设备，每设备4个IP，共发出LinkStatic信息共60个，更新数据库实测耗时69.984375s）
           //DateTime d = DateTime.Now;
           //for (int x = 0; x < 60; x++)
           //{
           //    if (conn.State == ConnectionState.Closed)
           //    {
           //        conn.Open();
           //    }
           //    DataSet ds = new DataSet();
           //    OleDbDataAdapter da = new OleDbDataAdapter("select * from LinkAlarm where IP='" + IP + "' order by LinkIndex", conn);
           //    da.Fill(ds);
           //    DataTable dt = ds.Tables[0];
           //    cmd = new OleDbCommand();
           //    cmd.Connection = conn;
           //    for (int p = 0; p < 256; p++)
           //    {
           //        mSU[p] = p;
           //    }
           //    for (int i = 0; i < dt.Rows.Count; i++)
           //    {
           //        cmd.CommandText = "update LinkAlarm set CreateTime='" + createTime + "',MSU=" + ((int)dt.Rows[i]["MSU"] + (int)mSU[i]) + " where IP='" + IP + "' and LinkIndex=" + (int)dt.Rows[i]["LinkIndex"];
           //        //cmd.CommandText = "insert into Info (IP,CreateTime,LinkIndex,MSU,CRC,LOSMSU) values('192.168.0.113','" + createTime + "'," + i + "," + i + ",0,0)";
           //        if (cmd.ExecuteNonQuery() < 0)
           //        {
           //            //hadUpdate = false;
           //        }
           //    }

           //    //if (hadUpdate)
           //    //{
           //    //    DALDispose();
           //    //    return true;
           //    //}
           //    //else
           //    //{
           //    //    DALDispose();
           //    //    return false;
           //    //}
           //}
           //TimeSpan ts = DateTime.Now.Subtract(d);

           //Console.WriteLine(" 用时 " + ts.TotalSeconds.ToString() + "秒 ");

           //return true;
           #endregion

           #region  Access datatable表更新法 （15台设备，每设备4个IP，共发出LinkStatic信息共60个，更新数据库实测耗时33.1875s）
           //DateTime d = DateTime.Now;
           //for (int x = 0; x < 60; x++)
           //{
           //    if (conn.State == ConnectionState.Closed)
           //    {
           //        conn.Open();
           //    }
           //    cmd = new OleDbCommand();
           //    DateTime time = Convert.ToDateTime(createTime);
           //    string currTime = time.ToString().Trim();
           //    DataSet ds = new DataSet();
           //    OleDbDataAdapter da = new OleDbDataAdapter("select * from LinkAlarm where IP='192.168.4.112'", conn);
           //    da.Fill(ds);
           //    for (int j = 0; j < 256; j++)
           //    {
           //        mSU[j] = j;
           //    }
           //    DataTable dt = ds.Tables[0];

           //    for (int i = 0; i < dt.Rows.Count; i++)
           //    {
           //        if (dt.Rows[i]["IP"].ToString().Trim() == "192.168.4.112")
           //        {
           //            int a = Convert.ToInt32(dt.Rows[i]["MSU"]);
           //            int b = Convert.ToInt32(mSU[Convert.ToInt32(dt.Rows[i]["LinkIndex"])]);
           //            int sum = a + b;
           //            //int sum = 0;
           //            dt.Rows[i][3] = createTime;
           //            dt.Rows[i]["MSU"] = sum;
           //        }
           //    }
           //    OleDbCommandBuilder ocb = new OleDbCommandBuilder(da);
           //    //da.UpdateBatchSize = 10;Access不支持SQL支持
           //    //int number = da.UpdateBatchSize;
           //    da.Update(dt);
           //}
           //DALDispose();
           //TimeSpan ts = DateTime.Now.Subtract(d);
           //Console.WriteLine(" 用时 " + ts.TotalSeconds.ToString() + "秒 ");
           //return true;
           #endregion

           #region SQL Server遍历更新法，过于消耗CPU （15台设备，每设备4个IP，共发出LinkStatic信息共60个，更新数据库实测耗时38.890625s）
           //cmd = new OleDbCommand(); //dispose方法用到
           //DateTime d = DateTime.Now;
           //for (int x = 0; x < 60; x++)
           //{
           //    SqlConnection co = new SqlConnection("Data Source=.;Initial Catalog=NMS;Integrated Security=True");
           //    if (co.State == ConnectionState.Closed)
           //    {
           //        co.Open();
           //    }
           //    //将数据库中相应记录保存到datatable
           //    DataSet ds = new DataSet();
           //    SqlDataAdapter da = new SqlDataAdapter("select * from A where IP='192.168.4.112' order by LinkIndex", co);
           //    da.Fill(ds);
           //    DataTable dt = ds.Tables[0];
           //    SqlCommand command = new SqlCommand();
           //    command.Connection = co;
           //    //bool hadUpdate = true;
           //    for (int p = 0; p < 256; p++)
           //    {
           //        mSU[p] = p;
           //    }
           //    //遍历该IP的每一行
           //    for (int i = 0; i < dt.Rows.Count; i++)
           //    {
           //        command.CommandText = "update A set CreateTime='" + createTime + "',MSU=" + ((int)dt.Rows[i]["MSU"] + (int)mSU[i]) + " where IP='192.168.4.112' and LinkIndex=" + (int)dt.Rows[i]["LinkIndex"];
           //        //cmd.CommandText = "insert into Info (IP,CreateTime,LinkIndex,MSU,CRC,LOSMSU) values('192.168.0.113','" + createTime + "'," + i + "," + i + ",0,0)";
           //        if (command.ExecuteNonQuery() < 0)
           //        {
           //            //hadUpdate = false;
           //        }
           //    }
           //    co.Close();
           //    command.Dispose();
           //    //if (hadUpdate)
           //    //{
           //    //    DALDispose();
           //    //    return true;
           //    //}
           //    //else
           //    //{
           //    //    DALDispose();
           //    //    return false;
           //    //}

           //}
           //TimeSpan ts = DateTime.Now.Subtract(d);
           //Console.WriteLine(" 用时 " + ts.TotalSeconds.ToString() + "秒 ");
           //return true;
           #endregion

 

           #region SQL Server datatable表更新方法 （15台设备，每设备4个IP，共发出LinkStatic信息共60个，更新数据库实测耗时6.65625s）
           //cmd = new OleDbCommand(); //dispose方法用到
           //DateTime d = DateTime.Now;
           //for (int x = 0; x < 60; x++)
           //{
           //    SqlConnection co = new SqlConnection("Data Source=.;Initial Catalog=NMS;Integrated Security=True");
           //    co.Open();
           //    DataSet ds = new DataSet();
           //    SqlDataAdapter da = new SqlDataAdapter("select * from Info where IP='192.168.4.112'", co);
           //    da.Fill(ds);
           //    for (int j = 0; j < 256; j++)
           //    {
           //        mSU[j] = j;
           //    }
           //    DataTable dt = ds.Tables[0];

           //    for (int i = 0; i < dt.Rows.Count; i++)
           //    {
           //        if (dt.Rows[i]["IP"].ToString().Trim() == "192.168.4.112")
           //        {
           //            int a = Convert.ToInt32(dt.Rows[i]["MSU"]);
           //            int b = Convert.ToInt32(mSU[Convert.ToInt32(dt.Rows[i]["LinkIndex"])]);
           //            int sum = a + b;
           //            //int sum = 0;
           //            dt.Rows[i][3] = createTime;
           //            dt.Rows[i]["MSU"] = sum;
           //            dt.Rows[i]["CreateTime"] = createTime;
           //        }
           //    }
           //    SqlCommandBuilder ocb = new SqlCommandBuilder(da);
           //    da.UpdateBatchSize = 256; //Access不支持SQL支持
           //    int number = da.UpdateBatchSize;
           //    da.Update(dt);
           //    dt.Dispose();
           //    co.Close();
           //    //SqlBulkCopy sbc = new SqlBulkCopy(conn);
           //    //sbc.BulkCopyTimeout=
           //    //sbc.WriteToServer(dt);
           //}
           //TimeSpan ts = DateTime.Now.Subtract(d);
           //Console.WriteLine(" 用时 " + ts.TotalSeconds.ToString() + "秒 ");
           //return true;
           #endregion

           #region SQL Server SqlBultCopy大批量迁移更新方法 （15台设备，每设备4个IP，共发出LinkStatic信息共60个，更新数据库实测耗时1.265625s）
           cmd = new OleDbCommand(); //dispose方法用到
           DateTime d = DateTime.Now;
           for (int x = 0; x < 60; x++)
           {
               SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=NMS;Integrated Security=True");
               if (conn.State == ConnectionState.Closed)
               {
                   conn.Open();
               }
               //将数据库中相应记录保存到datatable
               DataSet ds = new DataSet();
               SqlDataAdapter da = new SqlDataAdapter("select * from A where IP='192.168.4.112'", conn);
               da.Fill(ds);
               DataTable dt = ds.Tables[0];

               //if (conn.State == ConnectionState.Closed)
               //{
               //    conn.Open();
               //}
               //DataSet ds = new DataSet();
               //OleDbDataAdapter da = new OleDbDataAdapter("select * from LinkAlarm", conn);
               //da.Fill(ds);
               //DataTable dt = ds.Tables[0];
               for (int j = 0; j < 256; j++)
               {
                   mSU[j] = 1;
               }
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                   if (dt.Rows[i]["IP"].ToString().Trim() == "192.168.4.112")
                   {
                       int a = Convert.ToInt32(dt.Rows[i]["MSU"]);
                       int b = Convert.ToInt32(mSU[Convert.ToInt32(dt.Rows[i]["LinkIndex"])]);
                       int c = Convert.ToInt32(dt.Rows[i]["ID"]);
                       int sum = a + b;
                       //int sum = 0;
                       dt.Rows[i][3] = createTime;
                       dt.Rows[i]["MSU"] = sum;
                   }
               }

               string dbname = "A";
               string constr = "Data Source=.;Initial Catalog=NMS;Integrated Security=True";
               SqlConnection co = new SqlConnection(constr);
               SqlCommand cmd1 = new SqlCommand();
               cmd1.CommandText = "delete from A where IP='192.168.4.112'";
               cmd1.Connection = co;
               co.Open();
               cmd1.ExecuteNonQuery();
               SqlBulkCopy sbc = new SqlBulkCopy(constr);
               sbc.DestinationTableName = "[" + dbname + "]";
               sbc.WriteToServer(dt);
               //File.Delete(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());
               string totalitem = dt.Rows.Count.ToString();

               co.Close();
               cmd.Dispose();
               dt.Clear();
               dt.Dispose(); DALDispose();
           }
           TimeSpan ts = DateTime.Now.Subtract(d);

           Console.WriteLine(" 用时 " + ts.TotalSeconds.ToString() + "秒 ");
           return true;
           #endregion

           #region 模拟15台设备，60个IP，每个IP发送256个E1LinkAlarm，耗时14.234375s
           //cmd = new OleDbCommand(); //dispose方法用到
           //DateTime d = DateTime.Now;
           //for (int x = 0; x < 7560; x++)
           //{
           //    SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=NMS;Integrated Security=True");
           //    if (conn.State == ConnectionState.Closed)
           //    {
           //        conn.Open();
           //    }
           //    //将数据库中相应记录保存到datatable
             
              
           //    //string constr = "Data Source=.;Initial Catalog=NMS;Integrated Security=True";
           //    //SqlConnection co = new SqlConnection(constr);
           //    SqlCommand cmd1 = new SqlCommand();
              
           //    cmd1.Connection = conn;
           //    //co.Open();
             
           //    cmd1.CommandText = "update A set CreateTime='" + createTime + "',MSU=100 where IP='192.168.4.112' and LinkIndex=0";

           //    cmd1.ExecuteNonQuery();

           //    conn.Close();
           //    cmd.Dispose();
           //}
           //TimeSpan ts = DateTime.Now.Subtract(d);

           //Console.WriteLine(" 用时 " + ts.TotalSeconds.ToString() + "秒 ");
           //return true;
           #endregion