.NET 一次读取几百条数据优化，从原来30分钟优化到30秒

1、全部数据读取到内存，

不要使用string，而是使用stringbuilder，stringbuilder的效率非常高

2、添加到数据库

不要使用excute，而是使用事务，几百万条数据会请求数据库几百万次，而使用事务，只请求一次，速度提高几百万倍

 

  string file = fileDialog.FileName;//返回文件的完整路径    
                    SqliteHelper.ExecuteNonQuery("delete  from  BaseData");
                    StreamReader sr = new StreamReader(file, Encoding.Default);
                    string content;
                    StringBuilder sql = new StringBuilder();
                    int count = 0;
                    int num = 0;
                    DateTime starttime = DateTime.Now;
                    var a = sr.ReadToEnd();
                    List<string> resultList = a.Split('.').ToList();
                    List<BaseData> baseDatas = new List<BaseData>();  
                    List<string> sqlString = new List<string>();
                    Thread thread = new Thread(() =>
                    {
                        foreach (var item in resultList)
                        {
                            var c = item.Replace("\r\n", "");
                            if (string.IsNullOrEmpty(c))
                                continue;
                            List<string> str =c .Split(',').ToList();
             
                            if (str.Count() > 0)
                            {
                                //Console.WriteLine(content.ToString());
                               //  sql.AppendLine($"INSERT INTO BaseData (   ProductCode,XinHao,Sku) values ('{str[0]}','{str[1]}','{str[2]}');");

                                sqlString.Add($"INSERT INTO BaseData (   ProductCode,XinHao,Sku) values ('{str[0]}','{str[1]}','{str[2]}');");
                                num++;
                               // baseDatas.Add(new BaseData() {  ProductCode = str[0], XinHao = str[1], Sku = str[2]});
                                //count++;
                                //label1.Text = $"{count}条";

                  
                                //if (num % 5000 == 0)
                                //{
                                //    //count += DapperHelper<BaseData>.insertDbBatch2(baseDatas);
                                //    //count += DapperHelper<BaseData>.ExecuteTransaction(sqlString);
                                //    count += DapperHelper<BaseData>.insertDbBatch3(sqlString);
                                //    sqlString.Clear();
                                //    sql.Clear();
                                //    num = 0;
                                //    baseDatas.Clear();
                              
                                //}
                                DateTime endtime = DateTime.Now;
                                label2.Text = $"{(endtime - starttime).TotalSeconds}秒";

                            }
                        }


                        if (sqlString.Any())
                        {

                            // count += SqliteHelper.ExecuteNonQuery(sql.ToString());
                            //count += DapperHelper<BaseData>.insertDbBatch(sql.ToString());
                            //  count += DapperHelper<BaseData>.Execute(sql.ToString(),null); ;
                           count += DapperHelper<BaseData>.insertDbBatch3(sqlString);
                            DateTime endtime = DateTime.Now;
                            label2.Text = $"{(endtime - starttime).TotalSeconds}分";
                            if (count > 0)
                                MessageBox.Show("保存成功");
                            else
                                MessageBox.Show("保存失败");
                        }
                    });
                    thread.Start();


                }

  public static int insertDbBatch3(List<string> sqlList)
        {
            
            SQLiteConnection conn = new SQLiteConnection(connectionString);
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(conn);
            SQLiteTransaction trans = conn.BeginTransaction(); // <-------------------
            cmd.Transaction = trans;
            int cnt = 0;
            try
            {

                foreach (var sql in sqlList)
                {
                    cmd.CommandText = sql;
                    cnt += cmd.ExecuteNonQuery();
                }
               
                trans.Commit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }


            }
            return cnt;
        }