<NME> MsSQLStorage.cs
<BEF> ADDFILE
<MSG> MsSQLStorage - forgett to comit

<DFF> @@ -0,0 +1,426 @@
+﻿using System;
+using System.Collections.Generic;
+using System.Data;
+using System.Data.Common;
+using System.Data.SqlClient;
+using System.Linq;
+using System.Text;
+using System.Threading;
+using DotNetSimaticDatabaseProtokollerLibrary.Common;
+using DotNetSimaticDatabaseProtokollerLibrary.Databases.Interfaces;
+using DotNetSimaticDatabaseProtokollerLibrary.SettingsClasses.Datasets;
+using DotNetSimaticDatabaseProtokollerLibrary.SettingsClasses.Storage;
+
+namespace DotNetSimaticDatabaseProtokollerLibrary.Databases.MsSQL
+{
+    public class MsSQLStorage : IDBInterface, IDBViewable, IDBViewableSQL
+    {
+        private MsSQLConfig myConfig;
+        private IEnumerable<DatasetConfigRow> fieldList;
+        private string dataTable;
+        private string insertCommand = "";
+
+        private DbConnection myDBConn;
+        private DbCommand myCmd = new SqlCommand();
+        private DbDataReader myReader;
+
+        private static Dictionary<string, object> FileNameLockObjects = new Dictionary<string, object>();
+
+        public void Close()
+        {
+            if (myThread != null)
+                myThread.Abort();
+            if (myDBConn != null)
+                myDBConn.Close();
+        }
+
+        public event ThreadExceptionEventHandler ThreadExceptionOccured;
+
+        private string ConnectionString
+        {
+            get { return string.Format("Data Source={0},{1};Network Library=DBMSSOCN;Initial Catalog={2};User ID={3};Password={4};", myConfig.Server, myConfig.Port, myConfig.Database,  myConfig.Username, myConfig.Password); }
+        }
+
+        public void Connect_To_Database(StorageConfig config)
+        {
+
+            myConfig = config as MsSQLConfig;
+            if (myConfig == null)
+                throw new Exception("Database Config is NULL");
+
+            try
+            {
+                myDBConn = new SqlConnection(ConnectionString);
+                myDBConn.Open();
+                if (myDBConn.State != System.Data.ConnectionState.Open)
+                    throw new Exception("Unable to Open Database. Storage:" + config.Name);
+            }
+            catch (SqlException ex)
+            {
+                throw ex;
+            }
+            catch (Exception ex)
+            {
+                throw ex;
+            }
+        }
+
+        private string dateFieldName;
+
+        private DatasetConfig datasetConfig;
+
+        public void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
+        {
+            this.datasetConfig = datasetConfig;
+            this.dataTable = dataTable;
+            this.fieldList = datasetConfig.DatasetConfigRows;
+
+            List<DatasetConfigRow> createFieldList;
+
+            //Look if Table exists, when not, create it!
+            try
+            {
+                string sql = "SELECT * FROM " + dataTable + ";";
+                myCmd.Connection = myDBConn;
+                myCmd.CommandText = sql;
+                myReader = myCmd.ExecuteReader();
+
+            }
+            catch (SqlException ex)
+            {
+                if (ex.Number == 208)
+                {
+                    try
+                    {
+                        string sql = "CREATE TABLE " + dataTable + " (id int IDENTITY(1,1)PRIMARY KEY CLUSTERED); ";
+
+                        myCmd.CommandText = sql;
+                        myCmd.ExecuteNonQuery();
+
+                        sql = "SELECT * FROM " + dataTable + ";";
+                        myCmd.CommandText = sql;
+                        myReader = myCmd.ExecuteReader();
+                    }
+                    catch (SqlException ex_ex)
+                    {
+                        throw ex_ex;
+                    }
+                }
+                else
+                {
+                    throw ex;
+                }
+            }
+
+            //Look for the Fields, create or alter them!
+            List<String> existDBFelderliste = new List<string>();
+
+            for (int n = 0; n < myReader.FieldCount; n++)
+            {
+                existDBFelderliste.Add(myReader.GetName(n));
+            }
+            myReader.Close();
+
+
+            //Wenn Date Time Feld gesetzt...
+            dateFieldName = datasetConfig.DateTimeDatabaseField;
+            createFieldList = new List<DatasetConfigRow>(fieldList);
+            if (!string.IsNullOrEmpty(datasetConfig.DateTimeDatabaseField))
+                createFieldList.Add(new DatasetConfigRow() {DatabaseField = dateFieldName, DatabaseFieldType = "datetime"});
+
+
+            foreach (DatasetConfigRow myFeld in createFieldList)
+            {
+                foreach (string existMyFeld in existDBFelderliste)
+                {
+                    if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower())
+                    {
+                        goto nextFeld;
+                    }
+                }
+
+                //Feld existiert nicht -> erzeugen
+
+                string sql = "ALTER TABLE " + dataTable + " ADD " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType;
+
+                try
+                {
+                    myCmd.Connection = myDBConn;
+                    myCmd.CommandText = sql;
+                    myCmd.ExecuteNonQuery();
+
+                }
+                catch (SqlException ex)
+                {
+                    throw ex;
+                }
+
+                nextFeld:
+                //Irgendeine anweisung, da sonst der Sprung nicht geht...
+                {
+                }
+            }
+
+
+            //Create Insert Command
+            string wertliste = "", felderliste = "";
+            foreach (DatasetConfigRow myFeld in createFieldList)
+            {
+                if (wertliste != "")
+                {
+                    wertliste += ",";
+                    felderliste += ",";
+                }
+
+                felderliste += myFeld.DatabaseField;
+                wertliste += "@" + myFeld.DatabaseField;
+            }
+            insertCommand = "INSERT INTO " + dataTable + "(" + felderliste + ") values(" + wertliste + ")";
+        }
+
+
+
+        private Thread myThread;
+
+        private List<IEnumerable<object>> _intValueList = new List<IEnumerable<Object>>();
+        private int _maxAdd = 0;
+
+
+        /// <summary>
+        /// The write is added to a List and then put into an extra Thread, so that the PLC gets it's quitt imidiatly
+        /// </summary>
+        /// <param name="values"></param>
+        public void Write(IEnumerable<object> values)
+        {
+            lock (_intValueList)
+                _intValueList.Add(values);
+
+            if (myThread == null)
+            {
+                myThread = new Thread(new ThreadStart(ThreadProc));
+                myThread.Name = "Thread from Storage: " + myConfig.Name + " for Table: " + dataTable;
+                myThread.Start();
+            }
+        }
+
+        private void ThreadProc()
+        {
+            try
+            {
+                while (true)
+                {
+
+
+                    if (_intValueList.Count > 0)
+                    {
+                        lock (_intValueList)
+                            _maxAdd = _intValueList.Count;
+
+                        try
+                        {
+                            _internal_Write();
+                        }
+                        catch (ThreadAbortException)
+                        {
+                            throw;
+                        }
+                        catch (Exception ex)
+                        {
+                            if (ThreadExceptionOccured != null)
+                                ThreadExceptionOccured.Invoke(this, new ThreadExceptionEventArgs(ex));
+                            else
+                                Logging.LogText("Exception: ", ex, Logging.LogLevel.Error);
+                        }
+
+                        _intValueList.RemoveRange(0, _maxAdd);
+                    }
+                    else
+                        Thread.Sleep(20);
+                }
+            }
+            catch (ThreadAbortException)
+            {
+                return;
+            }
+        }
+
+
+
+        public bool _internal_Write()
+        {
+            //Look if the Connection is still open..
+            try
+            {
+                string sql = "SELECT id FROM " + dataTable + " WHERE id = 0";
+                myCmd.Connection = myDBConn;
+                myCmd.CommandText = sql;
+                myCmd.ExecuteNonQuery();
+            }
+
+            catch (Exception ex)
+            {
+                myDBConn.Close(); //Verbindung schließen!
+                if (myDBConn.State != System.Data.ConnectionState.Open)
+                {
+                    myDBConn.Open();
+                    if (myDBConn.State != System.Data.ConnectionState.Open)
+                    {
+                        return false;
+                    }
+                }
+                else
+                {
+                    return false;
+                }
+            }
+
+            //Add the Fields to the Database
+            myCmd.Connection = myDBConn;
+            myCmd.CommandText = insertCommand;
+
+            int tryCounter = 0;
+            nomol:
+            try
+            {
+
+                using (DbTransaction dbTrans = myDBConn.BeginTransaction())
+                {
+                    using (DbCommand cmd = myDBConn.CreateCommand())
+                    {
+                        cmd.CommandText = insertCommand;
+                        cmd.Transaction = dbTrans;
+                        for (int n = 0; n < _maxAdd; n++)
+                            //foreach (IEnumerable<object> values in _intValueList)
+                        {
+                            IEnumerable<object> values = _intValueList[n];
+
+                            if (!string.IsNullOrEmpty(dateFieldName))
+                                cmd.Parameters.Add(new SqlParameter("@" + dateFieldName, System.Data.DbType.String) {Value = DateTime.Now});
+
+                            using (IEnumerator<DatasetConfigRow> e1 = fieldList.GetEnumerator())
+                            using (IEnumerator<object> e2 = values.GetEnumerator())
+                            {
+                                while (e1.MoveNext() && e2.MoveNext())
+                                {
+                                    //foreach (DatasetConfigRow field in fieldList)
+                                    //{
+                                    DatasetConfigRow field = e1.Current;
+                                    Object value = e2.Current; //values[fnr++];
+
+                                    cmd.Parameters.Add(new SqlParameter() {ParameterName = "@" + field.DatabaseField, Value = value.ToString()});
+                                }
+                            }
+                            cmd.ExecuteNonQuery();
+                        }
+
+                        //Ringpufferarchiv...
+                        if (datasetConfig.MaxDatasets > 0)
+                        {
+                            string delstr = "DELETE FROM " + dataTable + " WHERE id <= (SELECT max(id) FROM " + dataTable + ") - (" + datasetConfig.MaxDatasets.ToString() + ")";
+                            cmd.CommandText = delstr;
+                            cmd.ExecuteNonQuery();
+                        }
+                    }
+
+                    dbTrans.Commit();
+                }
+            }            
+            catch (Exception ex)
+            {
+                Console.WriteLine(ex.Message);
+                throw ex;
+            }
+
+            return true;
+        }
+
+        public void Dispose()
+        {
+            if (myThread != null)
+                myThread.Abort();
+            if (myDBConn != null)
+                myDBConn.Dispose();
+            if (readDBConn != null)
+                readDBConn.Dispose();
+        }
+
+        #region IDBViewable
+        private DbConnection readDBConn;
+        private DbCommand readCmd = new SqlCommand();
+
+        private void CheckAndEstablishReadConnection()
+        {
+            if (readDBConn == null)
+            {
+                readDBConn = new SqlConnection(ConnectionString);
+                readDBConn.Open();
+            }
+        }
+
+        public DataTable ReadData(DatasetConfig datasetConfig, long Start, int Count)
+        {
+            //try
+            //{
+            CheckAndEstablishReadConnection();
+
+            readCmd.Connection = readDBConn;
+            readCmd.CommandText = "SELECT * FROM " + datasetConfig.Name + " ORDER BY id DESC LIMIT " + Count.ToString() + " OFFSET " + Start.ToString();
+            DbDataReader akReader = readCmd.ExecuteReader();
+
+            DataTable myTbl = new DataTable();
+            myTbl.Load(akReader);
+            akReader.Close();
+
+            return myTbl;
+            //}
+            //catch (Exception ex)
+            //{ }
+            //return null;
+        }
+
+        public DataTable ReadData(DatasetConfig datasetConfig, string sql, int Count)
+        {
+            //try
+            {
+                CheckAndEstablishReadConnection();
+
+                readCmd.Connection = readDBConn;
+                readCmd.CommandText = sql.Trim();
+                if (readCmd.CommandText.EndsWith(";"))
+                    readCmd.CommandText = readCmd.CommandText.Substring(0, readCmd.CommandText.Length - 1);
+                if (!readCmd.CommandText.Contains("ORDER BY"))
+                    readCmd.CommandText += " ORDER BY id DESC";
+                if (!readCmd.CommandText.Contains("LIMIT") && !readCmd.CommandText.Contains("OFFSET"))
+                    readCmd.CommandText += " LIMIT " + Count.ToString();
+                DbDataReader akReader = readCmd.ExecuteReader();
+
+                DataTable myTbl = new DataTable();
+                myTbl.Load(akReader);
+                akReader.Close();
+
+                return myTbl;
+            }
+            //catch (Exception ex)
+            { }
+            return null;
+        }
+
+        public Int64 ReadCount(DatasetConfig datasetConfig)
+        {
+            try
+            {
+                CheckAndEstablishReadConnection();
+
+                readCmd.Connection = readDBConn;
+                readCmd.CommandText = "SELECT COUNT(*) FROM " + datasetConfig.Name;
+
+                return Convert.ToInt64(readCmd.ExecuteScalar());
+            }
+            catch (Exception ex)
+            { }
+            return 0;
+        }
+
+        #endregion
+    }
+}

MsSQLStorage - forgett to comit