public void CalculateWeight(string connectionstring, string criteria, Interval interval, string excel) { DBengine.DBengine dbeng = new DBengine.DBengine(); try { dbeng.DBConnect(connectionstring, DataBaseType.sqlserver); if (!string.IsNullOrEmpty(criteria)) criteria = " where " + criteria; DateTime startTime = Convert.ToDateTime(dbeng.ExecuteScalar("select min(convert(datetime,FirstCaptureTime)) from StoredProcedure "+ criteria)); DateTime endTime = Convert.ToDateTime(dbeng.ExecuteScalar("select max(convert(datetime,FirstCaptureTime)) from StoredProcedure " + criteria)); TimeSpan step = new TimeSpan(1, 0, 0); switch (interval) { case Interval.Hour: step = new TimeSpan(1, 0, 0); break; case Interval.Day: step = new TimeSpan(24, 0, 0); break; case Interval.Week: step = new TimeSpan(24 * 7, 0, 0); break; } using (ExcelHelper.ExcelHelper excelHelper = new ExcelHelper.ExcelHelper(excel)) { for (DateTime timerange = startTime; timerange < endTime; timerange = timerange.Add(step)) { DataTable dt = dbeng.QuerySQL("SELECT [dbo].[Operation].[Object]" + " ,[dbo].[Operation].[Table]" + " ,[dbo].[Operation].[Operation]" + " ,CONVERT(decimal, Sum([dbo].[Operation].[Count]))/Sum([dbo].[StoredProcedure].[Count]) as Count" + " FROM [dbo].[Operation] inner join [dbo].[StoredProcedure] on [dbo].[Operation].[Object]=[dbo].[StoredProcedure].[Object] and [dbo].[Operation].[FirstCaptureTime]=[dbo].[StoredProcedure].[FirstCaptureTime] " + " where convert(datetime,[dbo].[Operation].[FirstCaptureTime])>='" + timerange.ToString("yyyy/MM/dd HH:mm:ss") + "' and " + " convert(datetime,[dbo].[Operation].[FirstCaptureTime])<'" + timerange.Add(step).ToString("yyyy/MM/dd HH:mm:ss") + "'" + " group by [dbo].[Operation].[Object],[dbo].[Operation].[Table],[dbo].[Operation].[Operation]"); Dictionary<string, decimal> result = CalculateWeight(dt.Select()); excelHelper.Append("Date" + timerange.ToString("yyyy/MM/dd HH:mm:ss"), result); } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine(ex.StackTrace); System.Diagnostics.Trace.WriteLine(ex.Message); } finally { dbeng.Close(); } }
public void CalculateWeight(List<string> SourceFiles, Interval interval, string excel) { DataTable tableOperation = new DataTable("Operation"); tableOperation.Columns.Add("Object"); tableOperation.Columns.Add("CaptureTime"); tableOperation.Columns.Add("Table"); tableOperation.Columns.Add("Operation"); tableOperation.Columns.Add("Count",typeof(decimal)); DateTime startTime = DateTime.MaxValue; DateTime endTime = DateTime.MinValue; foreach (string file in SourceFiles) { DataSet ds = new DataSet(); ds.ReadXml(file); foreach (DataRow dr in ds.Tables[0].Rows) { string captureTime = dr["FirstCaptureTime"].ToString(); if (startTime >Convert.ToDateTime( captureTime)) startTime = Convert.ToDateTime(captureTime); if (endTime < Convert.ToDateTime(captureTime)) endTime = Convert.ToDateTime(captureTime); string objectName = dr["Object"].ToString(); int count = Convert.ToInt32(dr["Count"].ToString()); foreach (DataRow opdr in ds.Tables[1].Select(" Object='"+objectName+"'")) { decimal avgcount = Convert.ToDecimal(opdr["Count"].ToString())/count; tableOperation.Rows.Add(new object[] { objectName, captureTime, opdr["Table"].ToString(), opdr["Operation"].ToString(),avgcount }); } } } TimeSpan step= new TimeSpan(1,0,0); switch (interval) { case Interval.Hour: step = new TimeSpan(1, 0, 0); break; case Interval.Day: step = new TimeSpan(24, 0, 0); break; case Interval.Week: step = new TimeSpan(24*7, 0, 0); break; } using (ExcelHelper.ExcelHelper excelHelper = new ExcelHelper.ExcelHelper(excel)) { for (DateTime timerange = startTime; timerange < endTime; timerange = timerange.Add(step)) { var drarray = from dr in tableOperation.Select() where Convert.ToDateTime(dr["CaptureTime"].ToString()) >= timerange && Convert.ToDateTime(dr["CaptureTime"].ToString()) < timerange.Add(step) select dr; Dictionary<string, decimal> result = CalculateWeight(drarray.ToArray<DataRow>()); excelHelper.Append("Date" + timerange.ToString("yyyy/MM/dd HH:mm:ss"), result); } } }
void br_ResultUpdated(Dictionary<string, decimal> result) { using (ExcelHelper.ExcelHelper excelHelper = new ExcelHelper.ExcelHelper(excelname)) { excelHelper.Append(label, result); } br_ProgressUpdated(0, 10); }