private void simpleButton8_Click(object sender, EventArgs e) //一键修改
{
string liestr = dateTimePicker3.Value.ToString("yyyyMMdd");
string lie = dateTimePicker3.Value.ToString("yyyyMM");
string nian = dateTimePicker3.Value.ToString("yyyy");
string sql = "USE KJ90Detail IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'UPKJ90Detail') DROP PROCEDURE UPKJ90Detail ";
ds = SqlHelper.ExecuteDataset(micai, CommandType.Text, sql);
int g = gridView1.SelectedRowsCount; //选取行总数
string ztz = listView1.FocusedItem.SubItems[0].Text; //获取状态数值
if (radioButton3.Checked == true) //固定值
{
string path = @"SQL";
string Update = "USE KJ90Detail";
Update = ReadXmlStringFromFile("UPDATEKJ90Detail1.txt", "", path);
Update = string.Format(Update, liestr);
ds = SqlHelper.ExecuteDataset(micai, CommandType.Text, Update);
for (int i = 0; i < this.gridView1.RowCount; i++)
{
double zhi = Convert.ToDouble(textEdit17.Text.Trim());
byte[] bytes = BitConverter.GetBytes(zhi);
string shuzhi = bytes[0].ToString("X2") + bytes[1].ToString("X2") + bytes[2].ToString("X2") + bytes[3].ToString("X2") + bytes[4].ToString("X2") + bytes[5].ToString("X2") + bytes[6].ToString("X2") + bytes[7].ToString("X2"); //转换为加密数
string UniqueID = this.gridView1.GetDataRow(i)["UniqueID"].ToString();//取选中行的ID字段的值
string iotime = this.gridView1.GetDataRow(i)["iotime"].ToString();//取选中行的ID字段的值
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@UniqueID",UniqueID),
new SqlParameter("@iotime",iotime),
new SqlParameter("@StatusID ",ztz),
new SqlParameter("@DataFlag ",shuzhi)
};
SqlHelper.ExecuteNonQuery(micai, CommandType.StoredProcedure, "UPKJ90Detail", param);
string Analogrun = "UPDATE AnalogRunRecord"+liestr+" SET StatusID="+ztz+",SValue="+zhi+" WHERE UniqueID="+ UniqueID + " AND cast(STime as datetime)='" + iotime + "'";
SqlHelper.ExecuteDataset(connstr, CommandType.Text, Analogrun);
string run = "UPDATE RunRecord"+lie+" SET StatusID="+ztz+" ,SValue="+zhi+" WHERE UniqueID="+UniqueID+ " AND cast(STime as datetime)='" + iotime + "'";
SqlHelper.ExecuteDataset(connstr, CommandType.Text, run);
string AStatusRecord = "DELETE AStatusRecord"+lie+" WHERE UniqueID="+UniqueID+ " AND cast(BTime as datetime)='" + iotime + "'";
SqlHelper.ExecuteDataset(connstr, CommandType.Text, AStatusRecord);
string RunRecord = "DELETE RunRecord"+lie+" WHERE UniqueID="+UniqueID+ " AND cast(STime as datetime)='" + iotime + "'";
SqlHelper.ExecuteDataset(connstr, CommandType.Text, RunRecord);
double MaxValue;
double MinValue;
double AvgValue;
MaxValue = zhi;
MinValue = MaxValue / 2;
AvgValue = MaxValue + MinValue / 2;
string staone = "UPDATE StaOneMinute"+liestr+" SET MaxValue="+zhi+ " ,MinValue="+MinValue+",AvgValue="+AvgValue+" ,StatID="+ztz+" WHERE UniqueID="+UniqueID+ " AND cast(MaxVTime as datetime)='" + iotime + "'";
SqlHelper.ExecuteDataset(connstr, CommandType.Text, staone);
string staday = "UPDATE StaOneHour" + nian+" SET MaxValue="+zhi+" ,MinValue="+MinValue+",AvgValue="+AvgValue+" WHERE UniqueID="+UniqueID+ " AND cast(MaxVTime as datetime)='" + iotime + "'";
SqlHelper.ExecuteDataset(connstr, CommandType.Text, staday);
}
}
else if (radioButton2.Checked == true)
{
string path = @"SQL";
string Update = "USE KJ90Detail";
Update = ReadXmlStringFromFile("UPDATEKJ90Detail2.txt", "", path);
Update = string.Format(Update, liestr);
ds = SqlHelper.ExecuteDataset(micai, CommandType.Text, Update);
for (int i = 0; i < g; i++)
{
string UniqueID = this.gridView1.GetDataRow(i)["UniqueID"].ToString();//取选中行的ID字段的值
string iotime = this.gridView1.GetDataRow(i)["iotime"].ToString();//取选中行的ID字段的值
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@UniqueID",UniqueID),
new SqlParameter("@iotime",iotime),
new SqlParameter("@StatusID ",ztz),
};
SqlHelper.ExecuteNonQuery(micai, CommandType.StoredProcedure, "UPKJ90Detail", param);
}
}
else if (radioButton1.Checked==true)
{
}
表1中(上百条)的 UniqueID(非主键) 和iotime 执行更新后向其余同UniqueID 和iotime 内容更新或者删除,其余表每个数据量不同几十条数据,或者只要一条需要删除,每次循环都和其他表进行匹配一次,相同的就执行。执行非常慢怎样优化更快你呢?