c#2010编写通过 Kepserver v4读写AB 、西门子PLC数据汇总到ORACLE

最新推荐文章于 2024-05-30 14:38:02 发布

闲看月色落花香

最新推荐文章于 2024-05-30 14:38:02 发布

阅读量1.1k

点赞数 16

文章标签： c# oracle 开发语言

本文链接：https://blog.csdn.net/weixin_68220207/article/details/134956318

版权

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

//需要引入该dll
using OPCAutomation;
using System.Net.NetworkInformation;

namespace DCSCollect
{
public partial class FrmMain : Form
{
DataBase wl = new DataBase();
DataBase dcs = new DataBase("server=dcs;uid=wlgz;pwd=wlgz");
OPCServer MyServer;
OPCGroups MyGroups;
OPCGroup MyGroup;
OPCGroup MyGroupxr;
int numplc = 1;
int numwl = 1;

List<string> ItemPlcName = new List<string>();
List<string> ItemPlcAddress = new List<string>();
string[] ItemPlcValue=new string[105] ;

List<string> ItemWlName = new List<string>();
string[] ItemWlValue = new string[10];

string[] klw = { "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0" };

public FrmMain()
{
InitializeComponent();
}

private void btntest_Click(object sender, EventArgs e)
{
GetWlinfo();
SaveDb();
SavePlc();
}

private void FrmMain_Load(object sender, EventArgs e)
{
Ping p1 = new Ping();
MyServer = new OPCServer();
MyServer.Connect("KEPware.KEPServerEx.V4", "192.168.1.29");
//server.Connect(remoteServerName, remoteServerIP);//连接本地服务器：服务器名+主机名或IP

//if (MyServer.ServerState == (int)OPCServerState.OPCRunning)
//{
// Console.WriteLine("已连接到：{0}", MyServer.ServerName);
//}
//else
//{
// //这里你可以根据返回的状态来自定义显示信息，请查看自动化接口API文档
// Console.WriteLine("状态：{0}", MyServer.ServerState.ToString());
//}
MyServer.ServerShutDown += ServerShutDown;//服务器断开事件

MyGroups = MyServer.OPCGroups;

//以下设置组属性

MyServer.OPCGroups.DefaultGroupIsActive = true;//激活组。
MyServer.OPCGroups.DefaultGroupDeadband = 0;// 死区值，设为0时，服务器端该组内任何数据变化都通知组。
MyServer.OPCGroups.DefaultGroupUpdateRate = 200;//默认组群的刷新频率为200ms

MyGroup = MyServer.OPCGroups.Add("测试");//添加组
MyGroup.UpdateRate = 1000;//刷新频率为1秒。
MyGroup.IsSubscribed = true;//使用订阅功能，即可以异步，默认false

MyGroupxr = MyServer.OPCGroups.Add("写入");//添加组
MyGroupxr.UpdateRate = 1000;//刷新频率为1秒。
MyGroupxr.IsSubscribed = true;//使用订阅功能，即可以异步，默认false

for (int i = 0; i < 10; i++)
{
ItemWlValue[i] = "0";
}

if (p1.Send("172.24.24.7").Status == IPStatus.Success)
{
lbldcs.Text = "DCS服务器联通";
DataTable dt = dcs.GetDT("select name,address from dcsitemplc order by id ");

numplc = dt.Rows.Count;

for (int i = 0; i < dt.Rows.Count; i++)
{
ItemPlcValue[i] = "0";

OPCItem myItem = MyGroup.OPCItems.AddItem(ItemPlcAddress[i], i);
}

DataTable dtwl = dcs.GetDT("select name from dcsitemwl order by id ");

numwl = dtwl.Rows.Count;

for (int i = 0; i < dtwl.Rows.Count; i++)
{
ItemWlValue[i] = "0";
ItemWlName.Add(dtwl.Rows[i][0].ToString());
}

MyGroup.DataChange += new DIOPCGroupEvent_DataChangeEventHandler(GroupDataChange);
}
else
{
lbldcs.Text = "DCS服务器不通";
dcs.ExeSql("insert into ERRORINFO(name,INFO) values('FrmMain_Load','DCS服务器不通')");
}
}
private void ServerShutDown(string Reason)//服务器先行断开
{
Console.WriteLine("服务器已经先行断开！");
}
private void GroupDataChange(int TransactionID, int NumItems, ref Array ClientHandles, ref Array ItemValues, ref Array Qualities, ref Array TimeStamps)
{

//此处注意i是从1开始，因为getvalue没有0.下面输出分别为数据的ClientHandles(就是上文AddItem方法的第二个参数。)，
//Itemvalue就是值，Qualities为质量，timestamps为时间。
try
{

for (int i = 1; i < NumItems + 1; i++)
{

lblerror.Text = ClientHandles.GetValue(i) + " " + ItemValues.GetValue(i).ToString();

ItemPlcValue[Convert.ToInt16(ClientHandles.GetValue(i))] = ItemValues.GetValue(i).ToString();
}
}
catch (Exception ex)
{
label1.Text = " group " + ex.Message;

//throw ex;
}
}

private void GetWlinfo()
{
for (int i = 0; i < 10; i++)
{
ItemWlValue[i] = "0";
}

Ping p1 = new Ping();
if (p1.Send("172.24.24.1").Status == IPStatus.Success)
{
lblwl.Text = "物流服务器联通";
DataTable dtzj = wl.GetDT("select biaoji, a6,a26,a14,a17 from banpiplc where biaoji='BANPI' or biaoji='FANGPI'");
//铸机状态重量、状态，分别对应ItemValue的100-104项
ItemWlValue[0] = dtzj.Rows[0][1].ToString();
ItemWlValue[1] = dtzj.Rows[0][2].ToString();

ItemWlValue[2] = dtzj.Rows[1][3].ToString();
ItemWlValue[3] = dtzj.Rows[1][4].ToString();

if (ItemWlValue[1] == "空闲")
ItemWlValue[1] = "0";
else
ItemWlValue[1] = "1";

if (ItemWlValue[3] == "结束")
ItemWlValue[3] = "0";
else
ItemWlValue[3] = "1";

ItemWlValue[4] = wl.GetString("select DIANLUCHUIYANGLIANG from 现场数据查询 where id=1");
//获取转炉信息
DataTable dtzl = wl.GetDT("Select 炉号,废钢量,自产铁水, 钢水量,出渣量,加废钢时刻 From "
+ "( select * from 转炉_统计 Order By 1 Desc )Where Rownum=1");
if (dtzl != null && dtzl.Rows.Count > 0)
{

for (int i = 0; i < 5; i++)
{
ItemWlValue[i + 5] = dtzl.Rows[0][i + 1].ToString();
}

}
}
else
{
lblwl.Text = "物流服务器不通";
dcs.ExeSql("insert into ERRORINFO(name,INFO) values('GetWlinfo','物流服务器不通')");
}

}
private void SaveDb()
{
try
{
int totalnum = numwl + numplc;
string[] sqlreal = new string[totalnum + 1];
string sql="";

Ping p1 = new Ping();

if (p1.Send("172.24.24.7").Status == IPStatus.Success)
{
lbldcs.Text = "DCS服务器联通";
//string sql = "insert into dcsvalue(";
//string sqlbk

sqlreal[totalnum] = "update dcscurent set ";

for (int i = 0; i < numplc; i++)
{
if (ItemPlcValue[i].Trim() == "")
ItemPlcValue[i] = "0";

sql = sql + ItemPlcName[i] + ",";
sqlreal[i] = "update dcsitem set value='" + ItemPlcValue[i] + "',cj_time=sysdate where name='" + ItemPlcName[i] + "'";
sqlreal[totalnum] = sqlreal[totalnum] + ItemPlcName[i] + "='" + ItemPlcValue[i] + "',";
}

for (int i = 0; i < numwl; i++)
{
if (ItemWlValue[i].Trim() == "")
ItemWlValue[i] = "0";

sql = sql + ItemWlName[i] + ",";
sqlreal[i + numplc] = "update dcsitem set value='" + ItemWlValue[i] + "',cj_time=sysdate where name='" + ItemWlName[i] + "'";
sqlreal[totalnum] = sqlreal[totalnum] + ItemWlName[i] + "='" + ItemWlValue[i] + "',";
}

sql = sql.Remove(sql.Length - 1, 1) + ") values(";
for (int i = 0; i < numplc; i++)
{
sql = sql + "'" + ItemPlcValue[i] + "',";
}
for (int i = 0; i < numwl; i++)
{
sql = sql + "'" + ItemWlValue[i] + "',";
}
sql = sql.Remove(sql.Length - 1, 1) + ")";

sqlreal[totalnum] = sqlreal[totalnum] + "insert_time=sysdate where id=1";

//dcs.ExeSql("insert into dcsvalue(" + sql);
dcs.ExeSql("insert into dcsvalue"+DateTime.Now.ToString("yyyyMM") + "(" + sql);
dcs.ExeArr(sqlreal);

string rq = DateTime.Now.ToString("yyyy-MM-dd HH");
string[] xm = { "YLZ_1#_DLXT_DLCCCEMS_KLW", "YLZ_1#_DLXT_HTLCCCEMS_KLW",
"YLZ_1#_ZLXT_ZLJLCCCEMS_KLW", "YLZ_1#_ZLXT_ECCCEMS_KLW",
"YLZ_1#_JRL_GXJELCEMS_KLW", "YLZ_1#_JRL_GXJELCEMS_Nox",
"YLZ_1#_JRL_GXJELCEMS_SO2", "YLZ_1#_DLXT_DLSCCCEMS_KLW",
"YLZ_1#_ZLXT_ZLSCCCEMS_KLW", "YLZ_1#_DLXT_LGQSCCCCEMS_KLW" ,
"YLZ_1#_ZLXT_ZLFGL", "YLZ_1#_ZLXT_ZLTSL", "YLZ_1#_ZLXT_ZLCGL",
"YLZ_1#_ZLXT_ZLczl","YLZ_1#_ZLXT_ZLJLTIME"
};

sql = "Select round(Avg(YLZ_1#_DLXT_DLCCCEMS_KLW),3),round(Avg(YLZ_1#_DLXT_HTLCCCEMS_KLW),3),"
+ "round(Avg(YLZ_1#_ZLXT_ZLJLCCCEMS_KLW),3),round(Avg(YLZ_1#_ZLXT_ECCCEMS_KLW),3),"
+ "round(Avg(YLZ_1#_JRL_GXJELCEMS_KLW),3),round(Avg(YLZ_1#_JRL_GXJELCEMS_Nox),3),"
+ "round(Avg(YLZ_1#_JRL_GXJELCEMS_SO2),3),round(Avg(YLZ_1#_DLXT_DLSCCCEMS_KLW),3), "
+ "round(Avg(YLZ_1#_ZLXT_ZLSCCCEMS_KLW),3),round(Avg(YLZ_1#_DLXT_LGQSCCCCEMS_KLW),3) "
+ "from dcsvalue" + DateTime.Now.ToString("yyyyMM") + " Where to_char(insert_time,'yyyy-MM-dd hh24')='" + rq + "'";

DataTable dtklw = dcs.GetDT(sql);

for (int i = 0; i < dtklw.Columns.Count; i++)
{
if (dtklw.Rows[0][i].ToString().Trim() != "")
klw[i] = dtklw.Rows[0][i].ToString().Trim();
}

//DataTable dtzl = dcs.GetDT("select YLZ_1#_ZLXT_ZLFGL,YLZ_1#_ZLXT_ZLTSL,YLZ_1#_ZLXT_ZLCGL,YLZ_1#_ZLXT_ZLczl"
// + " from dcszlxplc where YLZ_1#_ZLXT_ZLJLTIME='1' ");
//if (dtzl != null && dtzl.Rows.Count > 0)
//{
for (int i = 0; i < 4; i++)
{
if (ItemWlValue[i + 5].Trim() != "")
klw[i + 10] = ItemWlValue[i + 5];
}
klw[14] = "1";

if (dcs.HasRecord("select * from dcsklw Where to_char(insert_time,'yyyy-MM-dd hh24')='" + rq + "'"))
{
sql = "update dcsklw set ";
for (int i = 0; i < xm.Length; i++)
{
sql = sql + xm[i] + "=" + klw[i] + ",";
}
sql = sql.Remove(sql.Length - 1) + " Where to_char(insert_time,'yyyy-MM-dd hh24')='" + rq + "'";

}
else
{
sql = "insert into dcsklw(insert_time) values(to_date('" + rq + "','yyyy-MM-dd hh24'))";
}

dcs.ExeSql(sql);

}
else
{
lbldcs.Text = "DCS服务器不通";
dcs.ExeSql("insert into ERRORINFO(name,INFO) values('SaveDb','DCS服务器不通')");
}
}
catch (Exception ex)
{
lblerror.Text = "savedb " + ex.Message;
dcs.ExeSql("insert into ERRORINFO(name,INFO) values('SaveDb','"+ ex.Message+"')");
//continue;
//throw;
}

}
private void SavePlc()
{
try
{

Ping p1 = new Ping();

if (p1.Send("192.168.11.5").Status == IPStatus.Success)
{
lblplc.Text = "高线plc联通";
string[] writeaddress = { "SMZ.GAOXIAN.KLWXR_YLZ_1#_DLXT_DLCCCEMS", "SMZ.GAOXIAN.KLWXR_YLZ_1#_DLXT_HTLCCCEMS",
"SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ZLJLCCCEMS", "SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ECCCEMS",
"SMZ.GAOXIAN.KLWXR_YLZ_1#_JRL_GXJELCEMS", "SMZ.GAOXIAN.KLWXR_YLZ_1#_JRL_GXJELCEMS_Nox",
"SMZ.GAOXIAN.KLWXR_YLZ_1#_JRL_GXJELCEMS_SO2","SMZ.GAOXIAN.KLWXR_YLZ_1#_DLXT_DLSCCCEMS",
"SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ZLSCCCEMS","SMZ.GAOXIAN.KLWXR_YLZ_1#_DLXT_LGQSCCCCEMS",
"SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ZLFGL","SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ZLTSL",
"SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ZLCGL","SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ZLczl",
"SMZ.GAOXIAN.KLWXR_YLZ_1#_ZLXT_ZLJTIME"
};

OPCItem opcklw;
for (int i = 0; i < writeaddress.Length; i++)
{
//MyGroup.OPCItems.AddItem(writeaddress[i], 100 + i).Write(klw[i]);

opcklw = MyGroupxr.OPCItems.AddItem(writeaddress[i], i);
opcklw.Write(klw[i]);
}
//OPCItem opcklw=MyGroup.OPCItems.AddItem("KLWXR_YLZ_1#_DLXT_DLCCCEMS",99);
//opcklw.Write();
}
else
{
lblplc.Text = "高线plc不通";
dcs.ExeSql("insert into ERRORINFO(name,INFO) values('SavePlc','高线plc不通')");
}
}
catch (Exception ex)
{
lblerror.Text = "saveplc " + ex.Message;
dcs.ExeSql("insert into ERRORINFO(name,INFO) values('SavePlc','" + ex.Message + "')");
//throw;
}
}

private void timer1_Tick(object sender, EventArgs e)
{
//try
//{

//NetConn();

timer1.Enabled = false;
GetWlinfo();
SaveDb();
SavePlc();

lblcj.Text = DateTime.Now.ToShortTimeString();
timer1.Enabled = true;
//}
//catch (Exception ex)
//{
// //lblcj.Text = "采集不正常";
// lblerror.Text = "timer 采集不正常 " + ex.Message;
// throw;
//}
}

}
}