form1.cs

用于SIEMENS的WINCC项目数据库读写

using System;
using System.IO;

//using System.Collections.Generic;
//using System.ComponentModel;
using System.Data;
using System.Data.OleDb;      //
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;



namespace appCopack
{
    public partial class Form1 : Form
    {
        // selection archive type; 1=Tag Logging, 2=Alarm Logging, 3 User Archives
        int iArchiveSel = 1;
        int iValueID;
        string szValueName;
        System.DateTime localDateTimeFrom;
        System.DateTime localDateTimeTo;
        System.DateTime univDateTimeFrom;
        System.DateTime univDateTimeTo;
        OleDbConnection myConnection;
        OleDbDataAdapter myAdapter;
        DataTable myTableAlarms;
        DataTable myTableAlarmsModify;
        DataTable myTableTags;
        DataTable myTableTagsModify;
        DataTable myTableProducts;
     

        public Form1()
        {
            InitializeComponent();
        }
        private void txtSource_TextChanged(object sender, EventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            ChangeComponent();
        }

        public void ChangeComponent()
        {
            //--------------------------------
            // presets for Test-Environment
            //--------------------------------
            // data source name
            //txtCatalog.Text = "CC_OS_1__07_10_07_23_49_08R"; //drama
            txtCatalog.Text = "CC_OS_1__07_10_10_13_38_39R";

            // name of WinCC-Server and instance of SQL-Server
            //txtSource.Text = "BATCH702\\WINCC";               //drama
            txtSource.Text = "ESJPG\\WINCC";

            // Provider
            txtProvider.Text = "WinCCOLEDBProvider.1";

            // data in between dtpFrom and dtpTo
            dtpFrom.Format = DateTimePickerFormat.Custom;
            dtpFrom.CustomFormat = "dd.MM.yyyy HH:mm:ss";
//            dtpFrom.Value = DateTime.Now.AddHours(-1);
            dtpFrom.Value = DateTime.Now.AddDays(-1);
            dtpTo.Format = DateTimePickerFormat.Custom;
            dtpTo.CustomFormat = "dd.MM.yyyy HH:mm:ss";
            dtpTo.Value = DateTime.Now;

            //--------------------------------
            //iArchiveSel
            //--------------------------------
            switch (iArchiveSel)            {
                case 1:  //Tag Logging
                    toolStripLblTagLogging.IsLink = true;
                    toolStripLblAlarmLogging.IsLink = false;
                    toolStripLblUserArchives.IsLink = false;
                    TLG_Archive();
                    txtExportPath.Text = String.Format("c:\\data\\tmp");
                    grpExport.Enabled = true;
           
                    break;
                case 2:  //Alarm Logging
                    toolStripLblTagLogging.IsLink = false;
                    toolStripLblAlarmLogging.IsLink = true;
                    toolStripLblUserArchives.IsLink = false;
                    grpDataSelection.Enabled = false;
                    grpExport.Enabled = false;
                    break;
                case 3:  //User Archives
                    toolStripLblTagLogging.IsLink = false;
                    toolStripLblAlarmLogging.IsLink = false;
                    toolStripLblUserArchives.IsLink = true;
                    grpExport.Enabled = false;
                    break;
                default:
                    toolStripLblTagLogging.IsLink = false;
                    toolStripLblAlarmLogging.IsLink = false;
                    toolStripLblUserArchives.IsLink = false;
                    grpExport.Enabled = false;
                    break;

            }//switch (iArchiveSel) 

            //
            // Tags: choisable Interval-Type
            dblArray[] interpolArray = new dblArray[] 
            {
                new dblArray("ohne Zeitintervall",0),
                new dblArray("FIRST",1), new dblArray("LAST",2),
                new dblArray("MIN",3), new dblArray("MAX",4),
                new dblArray("AVG",5), new dblArray("SUM",6),
                new dblArray("COUNT",7),
                new dblArray("FIRST_INTERPOLATED",257), new dblArray("LAST_INTERPOLATED",258),
                new dblArray("MIN_INTERPOLATED",259), new dblArray("MAX_INTERPOLATED",260),
                new dblArray("AVG_INTERPOLATED",261), new dblArray("SUM_INTERPOLATED",262),
                new dblArray("COUNT_INTERPOLATED",263)
            };
            cmbInterpol.DataSource = interpolArray;
            cmbInterpol.ValueMember = "No";
            cmbInterpol.DisplayMember = "Name";    
    
        }//ChangeComponent()

        ///========================================== 
        /// TLG_Archive()
        ///========================================== 
        public int TLG_Archive(){
            tagInfos ti = new tagInfos("Provider=SQLOLEDB; Data Source =" + txtSource.Text + "; uid=" + txtUid.Text + "; pwd=" + txtPwd.Text + "; Initial Catalog = " + txtCatalog.Text + ";");

            cmbTags.DataSource = ti.getTaginfos();
            cmbTags.ValueMember = "ValueID";
            cmbTags.DisplayMember = "ValueName";
            return(0);
        }//TLG_Archive

        public int TLG_ArchiveGetID(String strValueName)
        {
            string myConnectionString = "Provider=SQLOLEDB; Data Source =" + txtSource.Text + "; uid=" + txtUid.Text + "; pwd=" + txtPwd.Text + "; Initial Catalog = " + txtCatalog.Text + ";";
            myTableTags = new DataTable();
            string mySelectQuery = "SELECT * FROM Archive";
            //------------
            try
            {
                using (OleDbCommand myCommand = new OleDbCommand(mySelectQuery))
                {
                    //  Connection Archive-Database
                    myConnection = new OleDbConnection(myConnectionString);
                    myCommand.Connection = myConnection;
                    myAdapter = new OleDbDataAdapter(myCommand);
                    //
                    //  Providing data for loop  throug data table, data grid
                    myTableTags.TableName = "myTableTags";
                    myAdapter.Fill(myTableTags);
                    myGrid.DataSource = myTableTags;
                    lblAnz.Text = myGrid.RowCount.ToString() + " Rows found";

                    myConnection.Close();
                    //
                    //Loop through DataTable by DataRow
                    //
                    foreach (DataRow row in myTableTags.Rows)
                    {
//                        strLine = String.Format("{0}; {1};  {2:F3};  {3}; {4}", row[0], row[1], row[2], row[3], row[4]);
//                        streamTagLogging.WriteLine(strLine);
                    }//DataRow
                }
                return (myGrid.RowCount);
            }
            catch (Exception ex)
            {
                MessageBox.Show("#E108: TLG_ArchiveGetID() -  Failed connect to the database.\n{0}", ex.Message);
                return (-1);
            }
        }//TLG_ArchiveGetID()

        ///========================================== 
        /// <Connect to Database / Tags>
        /// 
        /// <connecting to database by OleDB-Provider of WinCC>
        /// <reading data-rows by  Data-Adapter>
        /// <providing these rows in Data-Table for Datagrid>
        ///     <providing these rows in Data-Table for Report>
        /// <aktivating of Crystal-Report>
        /// 
        /// <param name="mySelectQuery"></param>
        /// <returns><Counter Rows/-1>
        ///========================================== 
        public int TLG_Connect(string mySelectQuery)
        {
            StreamWriter streamTagLogging = null;
            string strLine = "";
            string strExportFile = "";
            string myConnectionString = "Provider=" + txtProvider.Text + "; Data Source =" + txtSource.Text + "; Catalog = " + txtCatalog.Text + ";";
            myGrid.Name = "Tags";
            myTableTags = new DataTable();
            myTableTagsModify = new DataTable();
            System.DateTime localDateTime;
            System.DateTime univDateTime;

            //==============================================================
            //
            //Adding Columns and Rows to Data Table myTableTagsModify
            //
            //==============================================================
            //
            DataColumn newColumn = new DataColumn("localTimestamp", System.Type.GetType("System.String"));
            newColumn.Caption = "localTimestamp";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("RealValue", System.Type.GetType("System.String"));
            newColumn.Caption = "RealValue";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("Quality", System.Type.GetType("System.String"));
            newColumn.Caption = "Quality";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("Flags", System.Type.GetType("System.String"));
            newColumn.Caption = "Flags";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("ValueID", System.Type.GetType("System.String"));
            newColumn.AllowDBNull = false;
            newColumn.Caption = "ValueID";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("ValueName", System.Type.GetType("System.String"));
            newColumn.Caption = "ValueName";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("localDateTimeFrom", System.Type.GetType("System.String"));
            newColumn.Caption = "localDateTimeFrom";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("localDateTimeTo", System.Type.GetType("System.String"));
            newColumn.Caption = "localDateTimeTo";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("univDateTimeFrom", System.Type.GetType("System.String"));
            newColumn.Caption = "univDateTimeFrom";
            newColumn.DefaultValue = string.Empty;