public static DataTable CreateTableWeight(string connectioninfo)
{
DataTable tableWeight = CreateTableWeight();
DBengine.DBengine dbeng = new DBengine.DBengine();
try
{
dbeng.DBConnect(connectioninfo, DataBaseType.sqlserver);
foreach (string tablename in dbeng.GetTableName())
{
int count = (int)dbeng.ExecuteScalar("select count(*) from " + tablename);
tableWeight.Rows.Add(new object[] { tablename, count });
}
return tableWeight;
}
catch (Exception)
{
}
finally
{
dbeng.Close();
}
return tableWeight;
}
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();
}
}