form1.cs

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

            myTableTagsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("univDateTimeTo", System.Type.GetType("System.String"));
            newColumn.Caption = "univDateTimeTo";
            newColumn.DefaultValue = string.Empty;
            myTableTagsModify.Columns.Add(newColumn);
        
            //------------
            try
            {
                using (OleDbCommand myCommand = new OleDbCommand(mySelectQuery))
                {
                    //  Connection Archive-Database
                    myConnection = new OleDbConnection(myConnectionString);
                    myCommand.Connection = myConnection;
                    myAdapter = new OleDbDataAdapter(myCommand);
                    //
                    //  Providing data for Data-Grid
                    //
                    myTableTags.TableName = "myTableTags";
                    myTableTagsModify.TableName = "myTableTagsModify";
                    myAdapter.Fill(myTableTags);
                    //
                    //modify DataTable
                    //
                    myTableTagsModify.Clear();

                    foreach (DataRow row in myTableTags.Rows)
                    {
                        DataRow newRow = myTableTagsModify.NewRow();
                
                        //covert to local time
                        localDateTime = System.DateTime.Parse(row["Timestamp"].ToString());
                        localDateTime = localDateTime.ToLocalTime();
                        newRow["localTimestamp"] = localDateTime.ToString();
                        newRow["RealValue"] = (String.Format("{0:F3}", row["RealValue"])).PadLeft(20);
                        newRow["Quality"] = String.Format("0x{0:X}", row["Quality"]).PadLeft(10);
                        newRow["Flags"] = String.Format("0x{0:X}", row["Flags"]).PadLeft(10);
                        newRow["ValueID"] = row["ValueID"];
                        newRow["ValueName"] = szValueName;
                        newRow["localDateTimeFrom"] = localDateTimeFrom;
                        newRow["localDateTimeTo"] = localDateTimeTo;
                        newRow["univDateTimeFrom"] = univDateTimeFrom;
                        newRow["univDateTimeTo"] = univDateTimeTo;
                        myTableTagsModify.Rows.Add(newRow);
                    }//foreach(DataRow)
                
       
                    myGrid.DataSource = myTableTagsModify;
//                          myGrid.DataSource = myTableTags;
                    
                    //
                    lblAnz.Text = myGrid.RowCount.ToString() + " Rows found";
                    myConnection.Close();
                    //
                    //   Activating Crystal-Report
                    CRTagLogging myDataReportTags = new CRTagLogging();
                    myDataReportTags.SetDataSource(myTableTagsModify);
                                      
                    crystalReportViewer1.ReportSource = myDataReportTags;

                    //
                    //Loop through DataTable by DataRow
                    //
                    //text file open
                    strExportFile = String.Format("{0}\\{1}", txtExportPath.Text, txtExportFile.Text);
                    streamTagLogging = File.CreateText(strExportFile);
                    strLine = "strExportFile=" + txtExportFile.Text;
                    streamTagLogging.WriteLine(strLine);
                    streamTagLogging.WriteLine(myConnectionString);
                    strLine = String.Format("mySelectQuery=\"{0}\"", mySelectQuery);
                    streamTagLogging.WriteLine(strLine);
                    strLine = "localTimestamp; RealValue; Quality; Flags";
                    streamTagLogging.WriteLine(strLine);

                    foreach (DataRow row in myTableTagsModify.Rows)
                    {
                        strLine = String.Format("{0};  {1}; {2}; {3}",
                            row["localTimestamp"], row["RealValue"], row["Quality"], row["Flags"]);
                       streamTagLogging.WriteLine(strLine); 
                   }//DataRow
                    if (streamTagLogging != null)streamTagLogging.Close();
                }
                return (myGrid.RowCount);
            }
            catch (Exception ex)
            {
                MessageBox.Show("#E110: TLG_Connect() -  Failed connect to the database.\n{0}", ex.Message);
                return (-1);
            }
        }//TLG_Connect()


        public int UA_Connect(string mySelectQuery)
        {
            string myConnectionString = "Provider=SQLOLEDB; Data Source =" + txtSource.Text + "; uid=" + txtUid.Text + "; pwd=" + txtPwd.Text + "; Initial Catalog = " + txtCatalog.Text + ";";
            myTableProducts = new DataTable();
            //------------
            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
                    myTableProducts.TableName = "myTableProducts";
                    myAdapter.Fill(myTableProducts);
                    myGrid.DataSource = myTableProducts;
                    lblAnz.Text = (myGrid.RowCount-1).ToString() + " Rows found";
                    //
                    //   Activating Crystal-Report
                    CRProducts myDataProducts = new CRProducts();
                    myDataProducts.SetDataSource(myTableProducts);
                    crystalReportViewer1.ReportSource = myDataProducts;
                    myConnection.Close();
                }
                return (myGrid.RowCount);
            }
            catch (Exception ex)
            {
                MessageBox.Show("#E310: UA_Connect() -  Failed connect to the database.\n{0}", ex.Message);
                return (-1);
            }
        }//UA_Connect() 


        ///========================================== 
        /// <summary>
        /// Connect to Database / Alarms>
        /// 
        /// 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

        /// </summary>
        /// <param name="mySelectQuery"></param>
        /// <returns></returns>
        ///========================================== 
        public int ALG_Connect(string mySelectQuery)
        {
            string myConnectionString = "Provider=" + txtProvider.Text
                                        + "; Data Source =" + txtSource.Text
                                        + "; Catalog = " + txtCatalog.Text + ";";
            myGrid.Name = "Alarms";
            myTableAlarms = new DataTable();
            myTableAlarmsModify = new DataTable();
            System.DateTime localDateTime;
            System.DateTime univDateTime;

            //==============================================================
            //
            //Adding Columns and Rows to Data Table myTableAlarmsModify
            //
            //==============================================================
            //

            DataColumn newColumn = new DataColumn("localDateTime", System.Type.GetType("System.String"));
            newColumn.Caption = "localDateTime";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            
            newColumn = new DataColumn("MsgNr", System.Type.GetType("System.String"));
            newColumn.Caption = "MsgNr";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("State", System.Type.GetType("System.String"));
            newColumn.Caption = "State";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("Text1", System.Type.GetType("System.String"));
            newColumn.Caption = "Text1";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("Text3", System.Type.GetType("System.String"));
            newColumn.AllowDBNull = false;
            newColumn.Caption = "Text3";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("localDateTimeFrom", System.Type.GetType("System.String"));
            newColumn.Caption = "localDateTimeFrom";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("localDateTimeTo", System.Type.GetType("System.String"));
            newColumn.Caption = "localDateTimeTo";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("univDateTimeFrom", System.Type.GetType("System.String"));
            newColumn.Caption = "univDateTimeFrom";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("univDateTimeTo", System.Type.GetType("System.String"));
            newColumn.Caption = "univDateTimeTo";
            newColumn.DefaultValue = string.Empty;
            myTableAlarmsModify.Columns.Add(newColumn);
            
            //------------
            try
            {
                using (OleDbCommand myCommand = new OleDbCommand(mySelectQuery))
                {
                    //  Connection Archive-Database
                    myConnection = new OleDbConnection(myConnectionString);
                    myCommand.Connection = myConnection;
                    myAdapter = new OleDbDataAdapter(myCommand);

                    //  Providing data for Data-Grid
                    myTableAlarms.TableName = "myTableAlarms";
                    myTableAlarmsModify.TableName = "myTableAlarmsModify";
                    myAdapter.Fill(myTableAlarms);
//                    myAdapter.Fill(myTableAlarmsModify);
                    myTableAlarmsModify.Clear();
           
                    //
                    //modify DataTable
                    //
//               myTableAlarmsModify = myTableAlarmsModify.select(SortOrder localDateTime);
                    foreach (DataRow row in myTableAlarms.Rows)
                    {
                        string szState = "";
                        short iState;
                        DataRow newRow = myTableAlarmsModify.NewRow();
                        //covert to local time
                        
                        localDateTime = System.DateTime.Parse(row["DateTime"].ToString());
                        localDateTime = localDateTime.ToLocalTime();
                        newRow["localDateTime"] = localDateTime.ToString();
                        
                        newRow["MsgNr"] = (String.Format("{0}", row["MsgNr"])).PadLeft(10);
                        
                        //szState = String.Format("0x{0:X}", row["State"]).PadLeft(10);
                        iState = (short)(row["State"]);
                        switch (iState){
                            case 1:
                                szState = row["TxtCame"].ToString();
                                break;
                            case 2:
                                szState = row["TxtWent"].ToString();
                                break;
                            case 3:
                                szState = row["TxtAck"].ToString();
                                break;
                            case 16://0x10 (Quit System)