程序目的：每60秒从数据库获取1000条数据上报接口
以下是具体代码：
public class Program
{
// 数据库连接信息
// public static string con = "Data source = ××××××××× ; User ID =××× ; Password =×××";
public static string con = "Data source = ××××××××× ; User ID = ××× ; Password = ×××";
// 查询数据库连接对象
public SqlConnection mycon1 = new SqlConnection (Program.con);
// 定时器对象
System.Timers.Timer aTimer;
// 临时存储小火车ID
ArrayList tempdata = new ArrayList();
// 建立对比的对象字典
Dictionary<string, Train> temp_obj = new Dictionary<string, Train>();
// 获取小火车数据并上报
public void getTainDate(int Num, String Key,SqlConnection mycon1)
{
int sdrIndex = 0; // 查询总数数据标记
int pushIndex = 0; // 上报总数数据标记
DataSet ds = new DataSet();
try
{
//SqlCommand cmd1 = new SqlCommand(string.Format(@"select top ({0})
// [ID],[AGVId],[StartTime],[CurrentStatus],[ChargingStatus],[PercentagePower]
// from ×××.dbo.temp order by {1} desc;", Num, Key), mycon1);
SqlCommand cmd1 = new SqlCommand(string.Format(@"select top ({0})
[ID],[AGVId],[StartTime],[CurrentStatus],[ChargingStatus],[PercentagePower]
from [×××].[dbo].[StatusTable] order by {1} desc;", Num, Key), mycon1);
//SqlDataReader sdr = cmd1.ExecuteReader();
SqlDataAdapter sda = new SqlDataAdapter(cmd1);
sda.Fill(ds);
// [ID],[AGVId],[StartTime],[CurrentStatus],[ChargingStatus],[PercentagePower]
ArrayList accord = new ArrayList();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
// 记录该批次的数据（记录索引）
sdrIndex += 1;
accord.Add(ds.Tables[0].Rows[i].ItemArray[0]);
// 检测小火车类别是否在字典对象中
if (!temp_obj.ContainsKey(Convert.ToString(ds.Tables[0].Rows[i].ItemArray[1])))
{
temp_obj.Add(Convert.ToString(ds.Tables[0].Rows[i].ItemArray[1]), new Train());
}
// 检测当前数据是否 不在临时数组中
if (tempdata.IndexOf(ds.Tables[0].Rows[i].ItemArray[0]) == -1)
{
// 若当前状态不是需求状态，则跳过
if (Line_Status(Convert.ToString(ds.Tables[0].Rows[i].ItemArray[4]), Convert.ToString(ds.Tables[0].Rows[i].ItemArray[3])) == "")
{
continue;
}
else
{
// 检查状态变化(更新)的数据
if (Convert.ToString(ds.Tables[0].Rows[i].ItemArray[3]) != temp_obj[Convert.ToString(ds.Tables[0].Rows[i].ItemArray[1])].LINE_STATUS)
{
Train train = new Train();
train.TASKID = getTask();
var id = "OAGV0" + Convert.ToString(ds.Tables[0].Rows[i].ItemArray[1]);
train.LINE_NO = train.EQP_NO = id;
train.LINE_STATUS = Line_Status(Convert.ToString(ds.Tables[0].Rows[i].ItemArray[4]), Convert.ToString(ds.Tables[0].Rows[i].ItemArray[3]));
train.DL = Convert.ToString(ds.Tables[0].Rows[i].ItemArray[5]);
train.CIM_TIME = Convert.ToString(ds.Tables[0].Rows[i].ItemArray[2]);
// 调用后端接口
LogHelper.CreateInstance().WriteLog("上报数据:", JsonConvert.SerializeObject(train));
ServiceReference1.CIM_WMSClient WCF = new ServiceReference1.CIM_WMSClient();
string result = WCF.CIM_CAR_INTERFACE(JsonConvert.SerializeObject(train));
// 打印返回结果
Console.WriteLine(result);
LogHelper.CreateInstance().WriteLog("返回结果:", result);
pushIndex += 1;
Console.WriteLine("上报标记：" + pushIndex);
// 更新临时标记的字典对象
temp_obj[Convert.ToString(ds.Tables[0].Rows[i].ItemArray[1])].LINE_STATUS = Convert.ToString(ds.Tables[0].Rows[i].ItemArray[3]);
}
}
}
}
tempdata = accord;
Console.WriteLine("当前批次小火车类别数：" + temp_obj.Count);
LogHelper.CreateInstance().WriteLog("当前批次小火车类别数:" + Convert.ToString(temp_obj.Count) + "," + "符合上报数:" + Convert.ToString(pushIndex) + "\n", "");
Console.WriteLine("当前批次遍历数据数：" + sdrIndex);
// LogHelper.CreateInstance().WriteLog("当前批次遍历数：", Convert.ToString(temp_obj.Count));
}
catch (Exception e)
{
Console.WriteLine("SqlcommedMessage：" + e.Message);
LogHelper.CreateInstance().WriteLogWarn("SqlcommedMessage", e.Message);
// Console.WriteLine("SqlcommedMessageStackTrace：" + e.StackTrace);
mycon1.Close();
}
//finally
//{
//}
}
// 小火车类
public class Train
{
public string TASKID { set; get; } // 任务ID
public string LINE_NO { set; get; } // 线别编码
public string EQP_NO { set; get; } // 设备编码
public string LINE_STATUS { set; get; } // 状态
public string DL { set; get; } // 电量
public string T_TYPE = "CAR"; // 写死的类型
public string CIM_TIME { set; get; } // CIM时间
}
// TaskID随机生成
public String getTask()
{
string TaskID = Guid.NewGuid().ToString("N");
return TaskID;
}
// 返回线别状态
public string Line_Status(string n1, string n) {
// 状态：1运行，2停止，3充电，4故障，5，离线
string status = "";
if (n1 == "1")
{
status = "3";
}
else
{
switch (n)
{
case "0": status = "2"; break; // 0 -未知
case "1": status = "1"; break; // 1-行驶中
case "2": break; // 2-故障已清
case "3": status = "4"; break; // 3-故障中
case "4": break; // 4-待机
case "5": break; // 5-排队中
case "6": status = "1"; break; // 6-手动运行中
case "7": status = "1"; break; // 7-矫正位姿中
case "8": break; // 8-准备
case "9": status = "2"; break; // 9-关机
case "10": status = "5"; break; // 10-暂停
case "11": break; // 11-开机
case "12": break; // 12-接收中
default: break;
}
}
return status;
}
// 删除数据库信息
public void Truncate_table(string table)
{
try
{
Console.WriteLine("查询次数到达限制次数，进行清表操作");
LogHelper.CreateInstance().WriteLog("查询次数到达限制次数，进行清表操作", "");
SqlCommand cmd2 = new SqlCommand(string.Format(@"truncate table {0}", table), mycon1);
Console.WriteLine("受影响的行数为：{0}", cmd2.ExecuteNonQuery());
LogHelper.CreateInstance().WriteLog("数据清除完成", "");
}
catch (Exception e)
{
Console.WriteLine("Truncate_table Error:"+e.Message);
LogHelper.CreateInstance().WriteLogWarn("Truncate_table_Error", e.Message);
}
}
// 开始查询的数量和排序的关键字
int Num = 1000; // 每批次查询数
string Key = "StartTime"; // 排序关键字
int count = 0; // 记录查询次数
string table2 = "AGVStatistics.dbo.StatusTable"; //表信息
// 执行定时器事件
public void TimeEvent(object scoure, ElapsedEventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
aTimer.Enabled = false;
getTainDate(Num, Key, mycon1);
aTimer.Enabled = true;
count += 1;
sw.Stop();
Console.WriteLine("当前查询次数：{0}，执行时间：{1}",count,sw.Elapsed);
if (count > 1440)
{
Truncate_table(table2);
count = 0;
Console.WriteLine("数据表清除，执行次数置0");
}
Console.WriteLine("");
}
public static readonly log4net.ILog loginfo = log4net.LogManager.GetLogger("loginfo");
static void Main()
{
Program pro = new Program();
// 先打开数据库
try
{
pro.mycon1.Open();
Console.WriteLine("数据库已打开！");
LogHelper.CreateInstance().WriteLog("数据库连接成功", con);
pro.aTimer = new System.Timers.Timer();
// 定时器间隔
pro.aTimer.Interval = 60000;
pro.aTimer.Enabled = true;
pro.aTimer.Elapsed += new ElapsedEventHandler(pro.TimeEvent);
Console.WriteLine("按回车键结束程序");
Console.WriteLine(" 等待程序的执行．．．．．．");
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine("Open Sql Error: {0}",e.Message);
pro.mycon1.Close();
}
}
}
8:45分内存占用为：9232k
9:33分的内存占用为：11052k
