问题描述

c#向数据库添加大量数据为何我的这么慢

public void ShuaXin()
{

        SqlCommand com = new SqlCommand("delete from priceavg", DBHelper.con);
        DBHelper.con.Open();
        com.ExecuteNonQuery();
        DBHelper.con.Close();
        pros = new List<ProPrice>();
        com.CommandText = "select proid from proprice where newprice=1";
        DBHelper.con.Open();
        SqlDataReader re = com.ExecuteReader();
        ProPrice pp = null;
        while (re.Read())
        {
            pp = new ProPrice() { ProID = Convert.ToInt32(re["ProID"]) };
            pros.Add(pp);
        }
        re.Close();
        DBHelper.con.Close();
        //刷新上个月价格
        for (int i = 0; i < pros.Count; i++)
        {
            com.CommandText = string.Format("select ptype from product where id={0} ", pros[i].ProID);
            com.Connection = DBHelper.con;
            DBHelper.con.Open();
            if (com.ExecuteScalar() == null)
            {
                DBHelper.con.Close();
                continue;
            }
            int id = (int)com.ExecuteScalar();
            DBHelper.con.Close();
            DateTime beginTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01")).AddMonths(-1);  //获取本月的月份然后减去一个月
            DateTime finishTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01")).AddDays(-1);

            if (set.Tables["Avg1"] != null)
            {
                set.Tables.Remove("Avg1");
            }
            string sql = string.Format("select " + getSqlWhere(id) + " from proprice  where proid={0} and addtime between '{1}' and '{2}'", pros[i].ProID, beginTime, finishTime);
            adapter = new SqlDataAdapter(sql, DBHelper.con);
            adapter.Fill(set, "Avg1");
            DataTable dt = set.Tables["Avg1"];
            string[] strs = new string[dt.Columns.Count + 1];
            int count1 = 0;
            if (dt.Columns.Count == 1)
            {
                strs = new string[dt.Columns.Count + 1];
            }
            double avg = 0;
            for (int j = 0; j < dt.Columns.Count; j++)
            {

                double num = 0;
                int count = 0;
                for (int c = 0; c < dt.Rows.Count; c++)
                {
                    if (dt.Rows[c][j] is DBNull || dt.Rows[c][j] == null)
                    {
                        count++;
                        continue;
                    }

                    if ((dt.Rows[c][j]).ToString() == "非数字")
                    {
                        count++;
                        continue;
                    }

                    if (Convert.ToDouble(dt.Rows[c][j]) == 0)
                    {
                        count++;
                        continue;
                    }
                    num += Convert.ToDouble(dt.Rows[c][j]);

                }

                strs[j] = Math.Round(num / (dt.Rows.Count - count), 2).ToString();

                if (strs[j] == "非数字")
                {
                    count1++;
                }
                else
                {
                    avg += Convert.ToDouble(strs[j]);
                }

            }
            strs[strs.Length - 1] = Math.Round((avg / (strs.Length - 1 - count1)), 2).ToString();

            com.CommandText = string.Format("select count(*) from product where id={0} ", pros[i].ProID);
            DBHelper.con.Open();
            if ((int)com.ExecuteScalar() == 0)
            {
                DBHelper.con.Close();
                continue;
            }
            DBHelper.con.Close();

            com.CommandText = string.Format("select IsSeason,ProDescription,ID,ProductImage,Spec,ProAddress,MaoZhong,JingZhong from product where id={0}", Convert.ToInt32(pros[i].ProID));
            DBHelper.con.Open();
            re = com.ExecuteReader();
            Model.Product p1 = null;
            if (re.Read())
            {
                p1 = new Model.Product()
                {
                    IsSeason = Convert.ToInt32(re["IsSeason"]),
                    ProDescription = re["ProDescription"].ToString(),
                    ID = Convert.ToInt32(re["ID"]),
                    ProductImage = re["ProductImage"].ToString(),
                    Spec = re["Spec"].ToString(),
                    ProAddress = re["ProAddress"].ToString(),
                    MaoZhong = re["MaoZhong"].ToString(),
                    JingZhong = re["JingZhong"].ToString()
                };
            }
            re.Close();
            DBHelper.con.Close();
            string sqlstr = @"INSERT INTO priceAvg ([IsSeason],[ProDescription],[编号],[图片],[品名],[规格],[产地],[毛重],[净重],[isnoBiaoZhunJin],[isnoBiaoZhunJinls]," + insertSql(id) + ",timetype) values('" + p1.IsSeason + "','" + p1.ProDescription + "','" + p1.ID + "','" + p1.ProductImage + "','" + PinMing(Convert.ToInt32(pros[i].ProID)) + "','" + p1.Spec + "','" + p1.ProAddress + "','" + p1.MaoZhong + "','";
            sqlstr = sqlstr + p1.JingZhong + "','" + 0 + "','" + 0 + "','";
            if (insertSql(id).Trim() == "pavgprice")
            {
                strs = new string[] { strs[0] };
            }
            int nb = 0;
            for (int m = 0; m < strs.Length; m++)
            {
                if (strs[m] == "非数字")
                {
                    strs[m] = "0";
                }
                if (strs[m] == null)
                {
                    strs[m] = "0";
                }
                if (strs[m] == "0")
                {
                    nb++;
                }
                sqlstr = sqlstr + strs[m] + "','";
            }
            if (nb == strs.Length)
            {
                continue;
            }
            sqlstr = sqlstr + 1 + "')";
            com.CommandText = sqlstr;
            DBHelper.con.Open();
            com.ExecuteNonQuery();
            DBHelper.con.Close();
        }
    }

解决方案

用vs的性能分析,看看各行语句的运行时间

解决方案二：

C#中添加数据到数据库
c# 快速 将大量数据插入数据库
大量数据在数据库中的处理