Exemple #1
0
        /// <summary>
        /// Read timers from database
        /// TODO: catch exceptions
        /// </summary>
        public void readTimersfromSQL()
        {
            lock (Timers)
            {
                SqlWrapper ms = new SqlWrapper();
                TimeSpan ts;
                DateTime n = DateTime.Now;
                AOTimers m_timer;
                byte[] blob = new byte[10240];
                Timers.Clear();

                ms.SqlRead("SELECT * FROM " + getSQLTablefromDynelType() + "timers WHERE ID=" + ID.ToString() + ";");
                DataTable dt = ms.ReadDT("SELECT * FROM " + getSQLTablefromDynelType() + "timers WHERE ID=" + ID.ToString() + ";");
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        ts = TimeSpan.FromSeconds((Int32)row["timespan"]);
                        m_timer = new AOTimers();
                        m_timer.Timestamp = DateTime.Now + ts;
                        m_timer.Strain = (Int32)row["strain"];
                        m_timer.Function = new AOFunctions();
                        blob = (byte[])row[3];
                        m_timer.Function.ReadFunctionfromBlob(ref blob, 0);
                    }
                }
            }
        }
Exemple #2
0
        /// <summary>
        /// Read timers from database
        /// TODO: catch exceptions
        /// </summary>
        public void ReadTimersFromSql()
        {
            lock (this.timers)
            {
                SqlWrapper ms = new SqlWrapper();
                TimeSpan timeSpan;
                DateTime now = DateTime.Now;
                byte[] blob = new byte[10240];
                this.timers.Clear();

                ms.SqlRead(
                    "SELECT * FROM " + this.GetSqlTablefromDynelType() + "timers WHERE ID=" + this.Id.ToString() + ";");
                DataTable dt =
                    ms.ReadDatatable(
                        "SELECT * FROM " + this.GetSqlTablefromDynelType() + "timers WHERE ID=" + this.Id.ToString()
                        + ";");
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        timeSpan = TimeSpan.FromSeconds((Int32)row["timespan"]);
                        AOTimers aoTimer = new AOTimers
                            {
                                Timestamp = DateTime.Now + timeSpan,
                                Strain = (Int32)row["strain"],
                                Function = new AOFunctions()
                            };
                        MemoryStream memstream = new MemoryStream((byte[])row[3]);
                        BinaryFormatter bin = new BinaryFormatter();

                        aoTimer.Function = (AOFunctions)bin.Deserialize(memstream);
                    }
                }
            }
        }
        public static void lintels()
        {
            SqlWrapper ms = new SqlWrapper();
            List<lintel> doors = new List<lintel>();
            lintel ll;
            ms.SqlRead("SELECT * FROM doors");

            #region MySql
            if (ms.ismysql)
            {
                while (ms.myreader.Read())
                {
                    ll = new lintel();
                    ll.ID = ms.myreader.GetInt32("ID");
                    ll.X = ms.myreader.GetFloat("X");
                    ll.Y = ms.myreader.GetFloat("Y");
                    ll.Z = ms.myreader.GetFloat("Z");
                    ll.HZ = ms.myreader.GetFloat("HZ");
                    ll.frompf = ms.myreader.GetInt32("playfield");
                    ll.topf = ms.myreader.GetInt32("toplayfield");
                    ll.toid = ms.myreader.GetInt32("toid");
                    ll.proxy = ms.myreader.GetInt32("proxy");
                    doors.Add(ll);
                }
                ms.myreader.Close();
                ms.mcc.Close();
                ms.mcc.Dispose();
            }
            #endregion

            #region MsSql
            if (ms.ismssql)
            {
                while (ms.sqlreader.Read())
                {
                    ll = new lintel();
                    ll.ID = ms.sqlreader.GetInt32(0);
                    ll.X = ms.sqlreader.GetFloat(1);
                    ll.Y = ms.sqlreader.GetFloat(2);
                    ll.Z = ms.sqlreader.GetFloat(3);
                    ll.HZ = ms.sqlreader.GetFloat(4);
                    ll.frompf = ms.sqlreader.GetInt32(5);
                    ll.topf = ms.sqlreader.GetInt32(6);
                    ll.toid = ms.sqlreader.GetInt32(7);
                    ll.proxy = ms.sqlreader.GetInt32(8);
                    doors.Add(ll);
                }
                ms.sqlreader.Close();
                ms.sqlcc.Close();
                ms.sqlcc.Dispose();
            }
            #endregion

            #region PostgreSql
            if (ms.isnpgsql)
            {
                while (ms.npgreader.Read())
                {
                    ll = new lintel();
                    ll.ID = ms.npgreader.GetInt32(0);
                    ll.X = ms.npgreader.GetFloat(1);
                    ll.Y = ms.npgreader.GetFloat(2);
                    ll.Z = ms.npgreader.GetFloat(3);
                    ll.HZ = ms.npgreader.GetFloat(4);
                    ll.frompf = ms.npgreader.GetInt32(5);
                    ll.topf = ms.npgreader.GetInt32(6);
                    ll.toid = ms.npgreader.GetInt32(7);
                    ll.proxy = ms.npgreader.GetInt32(8);
                    doors.Add(ll);
                }
                ms.npgreader.Close();
                ms.npgcc.Close();
                ms.npgcc.Dispose();
            }
            #endregion

            bool found;
            foreach (lintel l1 in doors)
            {
                found = false;
                foreach (lintel l2 in doors)
                {
                    if (l1.ID != l2.ID)
                    {
                        if (l1.topf != 0)
                        {
                            if ((l1.frompf == l2.topf) && (l1.topf == l2.frompf))
                            {
                                found = true;
                                l1.toid = l2.ID;
                                ms.SqlUpdate(
                                    "UPDATE doors set toid=" + l2.ID.ToString() + " where id=" + l1.ID.ToString());
                                Console.WriteLine(l1.ID.ToString());
                            }
                            else if ((l1.topf == l2.frompf) && (l2.topf == 0))
                            {
                                l1.toid = l2.ID;
                                ms.SqlUpdate(
                                    "UPDATE doors set toid=" + l2.ID.ToString() + " where id=" + l1.ID.ToString());
                                Console.WriteLine(l1.ID.ToString());
                            }
                        }
                    }
                }
                if (!found)
                {
                    ms.SqlUpdate("UPDATE doors SET proxy=1 where ID=" + l1.ID.ToString());
                }
            }
        }
Exemple #4
0
        /// <summary>
        /// Check our tables and create/fill them if they don't exist
        /// </summary>
        public void CheckDBs()
        {
            SqlWrapper ms = new SqlWrapper();
            List<string> tablelist = new List<string>();
            List<string> tabletodo = new List<string>();
            bool allok = true;

            // ToDo: check if database exists and create it if not (parsing the connection string)
            if (this.ismssql)
            {
                ms.SqlRead("SELECT table_name FROM INFORMATION_SCHEMA.TABLES;");
            }
            else if (this.isnpgsql)
            {
                ms.SqlRead("SELECT table_name FROM information_schema.tables;");
            }
            else if (this.ismysql)
            {
                ms.SqlRead("show Tables");
            }

            if (ms.myreader.HasRows)
            {
                while (ms.myreader.Read())
                {
                    tablelist.Add(ms.myreader.GetString(0));
                }
            }
            else
            {
                allok = false;
            }

            ms.sqlclose();

            string[] sqlfiles = Directory.GetFiles("SQLTables");
            bool isin;
            foreach (string s in sqlfiles)
            {
                isin = false;
                foreach (string table in tablelist)
                {
                    if (s.ToLower() == Path.Combine("SQLTables", table + ".sql").ToLower())
                    {
                        isin = true;
                        break;
                    }
                }

                if (!isin)
                {
                    tabletodo.Add(s);
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("Table " + s + " doesn't exist.");
                    allok = false;
                }
            }

            if (!allok)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.Write("SQL Tables are not complete. Should they be created? (Y/N) ");
                string answer = Console.ReadLine();
                string sqlquery;
                if (answer.ToLower() == "y")
                {
                    foreach (string todo in tabletodo)
                    {
                        long filesize = new FileInfo(todo).Length;
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.Write("Table " + todo.PadRight(67) + "[  0%]");

                        if (filesize > 10000)
                        {
                            string[] queries = File.ReadAllLines(todo);
                            int c = 0;
                            sqlquery = string.Empty;
                            string lastpercent = "0";
                            while (c < queries.Length)
                            {
                                if (queries[c].IndexOf("INSERT INTO") == -1)
                                {
                                    sqlquery += queries[c] + "\n";
                                }
                                else
                                {
                                    c--;
                                    break;
                                }

                                c++;
                            }

                            ms.SqlInsert(sqlquery);
                            c++;
                            string buf1 = string.Empty;
                            while (c < queries.Length)
                            {
                                if (queries[c].ToLower().Substring(0, 11) == "insert into")
                                {
                                    break;
                                }

                                c++;
                            }

                            if (c < queries.Length)
                            {
                                buf1 = queries[c].Substring(0, queries[c].ToLower().IndexOf("values"));
                                buf1 = buf1 + "VALUES ";
                                StringBuilder Buffer = new StringBuilder(0, 1 * 1024 * 1024);
                                while (c < queries.Length)
                                {
                                    if (Buffer.Length == 0)
                                    {
                                        Buffer.Append(buf1);
                                    }

                                    string part = string.Empty;
                                    while (c < queries.Length)
                                    {
                                        if (queries[c].Trim() != string.Empty)
                                        {
                                            part = queries[c].Substring(queries[c].ToLower().IndexOf("values"));
                                            part = part.Substring(part.IndexOf("(")); // from '(' to end
                                            part = part.Substring(0, part.Length - 1); // Remove ';'
                                            if (Buffer.Length + 1 + part.Length > 1024 * 1000)
                                            {
                                                Buffer.Remove(Buffer.Length - 2, 2);
                                                Buffer.Append(";");
                                                ms.SqlInsert(Buffer.ToString());
                                                Buffer.Clear();
                                                Buffer.Append(buf1);
                                                string lp2 =
                                                    Convert.ToInt32(Math.Floor((double)c / queries.Length * 100))
                                                           .ToString();
                                                if (lp2 != lastpercent)
                                                {
                                                    Console.Write(
                                                        "\rTable " + todo.PadRight(67) + "[" + lp2.PadLeft(3) + "%]");
                                                    lastpercent = lp2;
                                                }
                                            }

                                            Buffer.Append(part + ", ");
                                        }

                                        c++;
                                    }

                                    Buffer.Remove(Buffer.Length - 2, 2);
                                    Buffer.Append(";");
                                    ms.SqlInsert(Buffer.ToString());
                                    Buffer.Clear();
                                    string lp = Convert.ToInt32(Math.Floor((double)c / queries.Length * 100)).ToString();
                                    if (lp != lastpercent)
                                    {
                                        Console.Write("\rTable " + todo.PadRight(67) + "[" + lp.PadLeft(3) + "%]");
                                        lastpercent = lp;
                                    }
                                }
                            }
                            else
                            {
                                Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
                            }
                        }
                        else
                        {
                            sqlquery = File.ReadAllText(todo);
                            ms.SqlInsert(sqlquery);
                            Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
                        }

                        Console.WriteLine();
                    }
                }
            }

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("Database is fine.");
        }
Exemple #5
0
        /// <summary>
        /// Check our tables and create/fill them if they don't exist
        /// </summary>
        public void CheckDBs()
        {
            SqlWrapper    ms        = new SqlWrapper();
            List <string> tablelist = new List <string>();
            List <string> tabletodo = new List <string>();
            bool          allok     = true;

            // ToDo: check if database exists and create it if not (parsing the connection string)
            if (this.ismssql)
            {
                ms.SqlRead("SELECT table_name FROM INFORMATION_SCHEMA.TABLES;");
            }
            else if (this.isnpgsql)
            {
                ms.SqlRead("SELECT table_name FROM information_schema.tables;");
            }
            else if (this.ismysql)
            {
                ms.SqlRead("show Tables");
            }

            if (ms.myreader.HasRows)
            {
                while (ms.myreader.Read())
                {
                    tablelist.Add(ms.myreader.GetString(0));
                }
            }
            else
            {
                allok = false;
            }

            ms.sqlclose();

            string[] sqlfiles = Directory.GetFiles("SQLTables");
            bool     isin;

            foreach (string s in sqlfiles)
            {
                isin = false;
                foreach (string table in tablelist)
                {
                    if (s.ToLower() == Path.Combine("SQLTables", table + ".sql").ToLower())
                    {
                        isin = true;
                        break;
                    }
                }

                if (!isin)
                {
                    tabletodo.Add(s);
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("Table " + s + " doesn't exist.");
                    allok = false;
                }
            }

            if (!allok)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.Write("SQL Tables are not complete. Should they be created? (Y/N) ");
                string answer = Console.ReadLine();
                string sqlquery;
                if (answer.ToLower() == "y")
                {
                    foreach (string todo in tabletodo)
                    {
                        long filesize = new FileInfo(todo).Length;
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.Write("Table " + todo.PadRight(67) + "[  0%]");

                        if (filesize > 10000)
                        {
                            string[] queries = File.ReadAllLines(todo);
                            int      c       = 0;
                            sqlquery = string.Empty;
                            string lastpercent = "0";
                            while (c < queries.Length)
                            {
                                if (queries[c].IndexOf("INSERT INTO") == -1)
                                {
                                    sqlquery += queries[c] + "\n";
                                }
                                else
                                {
                                    c--;
                                    break;
                                }

                                c++;
                            }

                            ms.SqlInsert(sqlquery);
                            c++;
                            string buf1 = string.Empty;
                            while (c < queries.Length)
                            {
                                if (queries[c].ToLower().Substring(0, 11) == "insert into")
                                {
                                    break;
                                }

                                c++;
                            }

                            if (c < queries.Length)
                            {
                                buf1 = queries[c].Substring(0, queries[c].ToLower().IndexOf("values"));
                                buf1 = buf1 + "VALUES ";
                                StringBuilder Buffer = new StringBuilder(0, 1 * 1024 * 1024);
                                while (c < queries.Length)
                                {
                                    if (Buffer.Length == 0)
                                    {
                                        Buffer.Append(buf1);
                                    }

                                    string part = string.Empty;
                                    while (c < queries.Length)
                                    {
                                        if (queries[c].Trim() != string.Empty)
                                        {
                                            part = queries[c].Substring(queries[c].ToLower().IndexOf("values"));
                                            part = part.Substring(part.IndexOf("("));  // from '(' to end
                                            part = part.Substring(0, part.Length - 1); // Remove ';'
                                            if (Buffer.Length + 1 + part.Length > 1024 * 1000)
                                            {
                                                Buffer.Remove(Buffer.Length - 2, 2);
                                                Buffer.Append(";");
                                                ms.SqlInsert(Buffer.ToString());
                                                Buffer.Clear();
                                                Buffer.Append(buf1);
                                                string lp2 =
                                                    Convert.ToInt32(Math.Floor((double)c / queries.Length * 100))
                                                    .ToString();
                                                if (lp2 != lastpercent)
                                                {
                                                    Console.Write(
                                                        "\rTable " + todo.PadRight(67) + "[" + lp2.PadLeft(3) + "%]");
                                                    lastpercent = lp2;
                                                }
                                            }

                                            Buffer.Append(part + ", ");
                                        }

                                        c++;
                                    }

                                    Buffer.Remove(Buffer.Length - 2, 2);
                                    Buffer.Append(";");
                                    ms.SqlInsert(Buffer.ToString());
                                    Buffer.Clear();
                                    string lp = Convert.ToInt32(Math.Floor((double)c / queries.Length * 100)).ToString();
                                    if (lp != lastpercent)
                                    {
                                        Console.Write("\rTable " + todo.PadRight(67) + "[" + lp.PadLeft(3) + "%]");
                                        lastpercent = lp;
                                    }
                                }
                            }
                            else
                            {
                                Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
                            }
                        }
                        else
                        {
                            sqlquery = File.ReadAllText(todo);
                            ms.SqlInsert(sqlquery);
                            Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
                        }

                        Console.WriteLine();
                    }
                }
            }

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("Database is fine.");
        }