public void ShuaXin()
{
SqlCommand com = new SqlCommand("delete from priceavg", DBHelper.con);
DBHelper.con.Open();
com.ExecuteNonQuery();
adapter = new SqlDataAdapter("select * from priceavg", DBHelper.con);
adapter.Fill(set, "avgs");
DataTable t = set.Tables["avgs"];
pros = new List<ProPrice>();
com.CommandText = "select proid from proprice where newprice=1";
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 < 1000; i++)
{
DBHelper.con.Open();
com.CommandText = string.Format("select ptype from product where id={0} ", pros[i].ProID);
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();
DBHelper.con.Open();
com.CommandText = string.Format("select count(*) from product where id={0} ", pros[i].ProID);
if ((int)com.ExecuteScalar() == 0)
{
DBHelper.con.Close();
continue;
}
DBHelper.con.Close();
DBHelper.con.Open();
com.CommandText = string.Format("select IsSeason,ProDescription,ID,ProductImage,Spec,ProAddress,MaoZhong,JingZhong from product where id={0}", Convert.ToInt32(pros[i].ProID));
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[] ss = insertSqls(id);
DataRow row = t.NewRow();
row["IsSeason"] = p1.IsSeason;
row["ProDescription"] = p1.ProDescription;
row["编号"] = p1.ID;
row["图片"] = p1.ProductImage;
row["品名"] = PinMing(Convert.ToInt32(pros[i].ProID));
row["规格"] = p1.Spec;
row["产地"] = p1.ProAddress;
row["毛重"] = p1.MaoZhong;
row["净重"] = p1.JingZhong;
row["isnoBiaoZhunJin"] = 0;
row["isnoBiaoZhunJinls"] = 0;
int nb = 0;
if (insertSql(id).Trim() == "pavgprice")
{
strs = new string[] { strs[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++;
}
row[ss[m].Trim()] = strs[m];
}
row["timetype"] = 1;
t.Rows.Add(row);
}
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(DBHelper.con.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = "priceAvg";
for (int i = 0; i < t.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(t.Columns[i].ColumnName, t.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(t);
}
又关又开的
DBHelper.con.Close();
DBHelper.con.Open();
这个是否会影响呢？如果异常就直接break跳出再关闭，正常情况下，for里面一直保持着open，不需关闭。 没数据库经验，纯属个人见解
又关又开的
DBHelper.con.Close();
DBHelper.con.Open();
这个是否会影响呢？如果异常就直接break跳出再关闭，正常情况下，for里面一直保持着open，不需关闭。 没数据库经验，纯属个人见解
连接数据库是要时间的，你这个循环了那么多次，连接了那么多次当然慢，
你可以考虑用 insert into select from 批量插入，
也可以考虑用 写一个存储过程 ，把循环写到存储过程，那么就只连接一次，其他的逻辑都交给数据库里面处理，想必也快很多