Пример #1
0
 public MySql.Data.MySqlClient.MySqlDataReader RunCommandObj(string sql)
 {
     Command             = Connection.CreateCommand();
     Command.CommandType = System.Data.CommandType.Text;
     Command.CommandText = sql;
     return(Command.ExecuteReader());
 }
Пример #2
0
        /// <summary>
        /// Create the dabase if it does not exist.
        /// </summary>
        /// <remarks>Mysql/mariadb specific</remarks>
        private void CreateIfNotExists()
        {
            string connStr = LibLogic.Setup.DbFactoryWithoutDatabase.ConnectionString;
            var    cn      = new MySql.Data.MySqlClient.MySqlConnection(connStr);
            var    cmd     = cn.CreateCommand();

            cmd.CommandText = string.Format("CREATE DATABASE IF NOT EXISTS `{0}` CHARACTER SET utf8 COLLATE utf8_unicode_ci;", Setup.DatabaseName);
            cmd.CommandType = System.Data.CommandType.Text;

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();


            // Sessions database
            // Alls sessions are store in mysql instead of in process.
            // Provides better support for clustering, load balancing and restarting sites after updates
            // without users being logged out or loosing data
            var cn2  = new MySql.Data.MySqlClient.MySqlConnection(connStr);
            var cmd2 = cn2.CreateCommand();

            cmd2.CommandText = "CREATE DATABASE IF NOT EXISTS `VpnSessions` CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
            cmd2.CommandType = System.Data.CommandType.Text;

            cn2.Open();
            cmd2.ExecuteNonQuery();
            cn2.Close();
        }
Пример #3
0
        /// <summary>
        /// ตรวจสอบว่าใน database server มี function นี้อยู่หรือไม่
        /// </summary>
        /// <param name="name">ชื่อ function</param>
        /// <returns>bool</returns>
        static bool FunctionIsExist(string name)
        {
            int result = 0;

            using (var connection = new MySql.Data.MySqlClient.MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString))
                using (var command = connection.CreateCommand())
                {
                    var sb = new StringBuilder();
                    sb.AppendLine("SELECT COUNT(ROUTINE_NAME) as Result");
                    sb.AppendLine("FROM INFORMATION_SCHEMA.ROUTINES");
                    sb.AppendLine("WHERE ROUTINE_TYPE = @RoutineType");
                    sb.AppendLine("AND ROUTINE_SCHEMA = @RoutineSchema");
                    sb.AppendLine("AND ROUTINE_NAME = @RoutineName;");

                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = sb.ToString();
                    command.Parameters.AddWithValue("@RoutineType", "FUNCTION");
                    command.Parameters.AddWithValue("@RoutineSchema", "opminspection");
                    command.Parameters.AddWithValue("@RoutineName", name);

                    connection.Open();
                    result = Convert.ToInt32(command.ExecuteScalar());
                    connection.Close();
                }

            return(result > 0);
        }
Пример #4
0
        /// <summary>
        /// Creates a new command, enlisting it in the current transaction
        /// </summary>
        public MySqlData.MySqlClient.MySqlCommand CreateCommand()
        {
            var command = _currentConnection.CreateCommand();

            command.Transaction = _currentTransaction;
            return(command);
        }
            } // GetFromRow

            public CmsPersistentVariable Fetch(string name)
            {
                if (name.Trim() == "")
                {
                    return(new CmsPersistentVariable("", null));
                }


                using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
                {
                    OpenMySqlConnection(conn);

                    string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables ";
                    sql += " WHERE Name like @Name";

                    MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;

                    cmd.Parameters.AddWithValue("@Name", name);

                    MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sqlDA.Fill(ds);

                    if (this.hasSingleRow(ds))
                    {
                        DataRow dr = ds.Tables[0].Rows[0];
                        return(GetFromRow(dr));
                    }
                    return(new CmsPersistentVariable("", null));
                }
            } // Fetch
        /// <summary>
        /// 动态数据入库操作
        /// </summary>
        private static void SaveDynamicData(MySql.Data.MySqlClient.MySqlConnection connection, List <string> dynamicSqlCommandCollection, AisControl controlObject)
        {
            // 开始事务处理
            MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
            MySql.Data.MySqlClient.MySqlCommand     command     = connection.CreateCommand();
            try
            {
                // 批量更新
                foreach (string cmd in dynamicSqlCommandCollection)
                {
                    command.CommandText = cmd;
                    command.ExecuteNonQuery();
                }

                // 提交数据入库
                transaction.Commit();
                if (controlObject != null)
                {
                    // 提交错误日志
                    controlObject.AddStatusString("动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                }
            }
            catch (Exception ex)
            {
                // 放弃当前的数据入库操作
                transaction.Rollback();
                if (controlObject != null)
                {
                    // 提交错误日志
                    controlObject.AddStatusString("动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                }
            }
        }
Пример #7
0
        internal List <Mercado> RetrievebyConst(int id)
        {
            MySql.Data.MySqlClient.MySqlConnection con     = Connect();
            MySql.Data.MySqlClient.MySqlCommand    command = con.CreateCommand();
            command.CommandText = "select * from mercado where id = @A";
            command.Parameters.AddWithValue("@A", id);


            try
            {
                con.Open();
                MySql.Data.MySqlClient.MySqlDataReader res = command.ExecuteReader();

                Mercado        d        = null;
                List <Mercado> mercados = new List <Mercado>();
                while (res.Read())
                {
                    Debug.WriteLine("Recuperado: " + res.GetInt32(0) + " " + res.GetString(1) + " " + res.GetInt32(2) + " " + res.GetString(3));
                    d = new Mercado(res.GetInt32(0), res.GetDouble(1), res.GetDouble(2), res.GetDouble(3), res.GetInt32(4), res.GetInt32(5));
                    mercados.Add(d);
                }

                con.Close();
                return(mercados);
            }
            catch (MySql.Data.MySqlClient.MySqlException e)
            {
                Debug.WriteLine("Se ha producido un error de conexión");
                return(null);
            }
        }
Пример #8
0
        // Visibilidad para que sea visto por otras clases internal - como protected.
        internal List <Mercado> Retrieve()
        {
            MySql.Data.MySqlClient.MySqlConnection conexion = Connect();
            MySql.Data.MySqlClient.MySqlCommand    cmd      = conexion.CreateCommand();
            cmd.CommandText = " select * from mercado";

            try
            {
                conexion.Open();
                MySql.Data.MySqlClient.MySqlDataReader res = cmd.ExecuteReader();

                Mercado        mercado   = null;
                List <Mercado> lMercados = new List <Mercado>();
                while (res.Read())
                {
                    Debug.WriteLine("Mercado: " + res.GetInt32(0) + " " + res.GetString(1) + " " +
                                    res.GetString(2) + " " + res.GetString(3) + " ");
                    mercado = new Mercado(res.GetInt32(0), res.GetDouble(1), res.GetDouble(2), res.GetDouble(3), res.GetDouble(4), res.GetDouble(5));
                    lMercados.Add(mercado);
                }

                conexion.Close();
                return(lMercados);
            }
            catch (MySql.Data.MySqlClient.MySqlException e)
            {
                Debug.WriteLine("Se ha producido un error de conexión.");
                return(null);
            }
        }
Пример #9
0
        private static void MySQL_OpenConnection()
        {
            string info = "server=localhost;user=root;database=tun;port=3306;password=Camara20";

            MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(info);
            MySql.Data.MySqlClient.MySqlCommand    command;

            try
            {
                Console.WriteLine("MySQL: Connecting to 'tun' database...\n");
                connection.Open();

                // TODO: executing queries here
                command             = connection.CreateCommand();
                command.CommandText = "DELETE FROM account";
                command.ExecuteNonQuery();

                Console.WriteLine("MySQL: all 'account' instances were successfully deleted!\n");

                command.CommandText = "INSERT INTO account (username, password) VALUES ('ShaBer', 'qwerty');";
                command.ExecuteNonQuery();

                Console.WriteLine("MySQL: 'account' inserted successfully!\n");
            }
            catch (Exception ex)
            {
                Console.WriteLine("MySQL: An error occured while atempting to connect to 'tun'");
                Console.WriteLine($"MySQL: - {ex.ToString()}\n");
            }

            connection.Close();
            Console.WriteLine("MySQL: Closing connection");
        }
Пример #10
0
        // Devuelve la lista entera:
        internal List <Receta> Retrieve()
        {
            // Conexión a sql y consulta:
            MySql.Data.MySqlClient.MySqlConnection conexion = Connect();
            MySql.Data.MySqlClient.MySqlCommand    cmd      = conexion.CreateCommand();
            cmd.CommandText = " select * from recetas";

            try
            {
                conexion.Open();
                MySql.Data.MySqlClient.MySqlDataReader res = cmd.ExecuteReader();

                // Creamos objeto mercado y lista:
                Receta        receta  = null;
                List <Receta> recetas = new List <Receta>();

                while (res.Read())
                {
                    // Introducimos los datos y añadimos a la lista todo:
                    receta = new Receta(res.GetInt32(0), res.GetString(1), res.GetString(2), res.GetInt32(3));
                    recetas.Add(receta);
                }

                // Cerramos la conexión a la bd:
                conexion.Close();
                return(recetas);
            }
            catch (MySql.Data.MySqlClient.MySqlException e)
            {
                Debug.WriteLine("Se ha producido un error de conexión.");
                return(null);
            }
        }
Пример #11
0
 static void Main(string[] args)
 {
     using (ConnectionMultiplexer redis = ConnectionMultiplexer.Connect("127.0.0.1:6379"))
     {
         ISubscriber sub = redis.GetSubscriber();
         sub.Subscribe("createtenant", (channel, message) => {
             Console.WriteLine($"收到消息:{message}\n 开始创建表……");
             using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(message))
             {
                 Console.WriteLine("打开数据库连接");
                 connection.Open();
                 Console.WriteLine("数据库连接打开成功");
                 Console.WriteLine("开始创建用户表");
                 var cmd         = connection.CreateCommand();
                 cmd.CommandText = createUser;
                 cmd.ExecuteNonQuery();
                 Console.WriteLine("用户表创建成功\n 开始创建订单表");
                 cmd.CommandText = createOrder;
                 cmd.ExecuteNonQuery();
                 Console.WriteLine("订单表创建成功\n 开始创建商品表");
                 cmd.CommandText = createGoods;
                 cmd.ExecuteNonQuery();
                 Console.WriteLine("商品表创建成功");
                 cmd.Dispose();
             }
         });
         Console.WriteLine("已订阅 messages");
         Console.ReadKey();
     }
 }
Пример #12
0
        public static int QueryIdentityTransaction(this MySql.Data.MySqlClient.MySqlConnection db, MySql.Data.MySqlClient.MySqlTransaction trans, string query, out long identity, params object[] args)
        {
            Stopwatch sw       = new Stopwatch();
            int       affected = 0;

            sw.Start();

            using (var com = db.CreateCommand()) {
                com.CommandText    = query;
                com.Transaction    = trans;
                com.CommandTimeout = 60;

                for (int i = 0; i < args.Length; i++)
                {
                    com.AddParameter("@" + i, args[i]);
                }

                try {
                    affected = com.ExecuteNonQuery();
                } catch (Exception ex) {
                    TShock.Log.ConsoleError("[SEconomy MySQL] Query error: {0}", ex.Message);
                    affected = -1;
                }
                identity = com.LastInsertedId;
            }

            sw.Stop();
            if (sw.Elapsed.TotalSeconds > 10)
            {
                TShock.Log.ConsoleError("[SEconomy MySQL] Your MySQL server took {0} seconds to respond!\r\nConsider squashing your journal.", sw.Elapsed.TotalSeconds);
            }

            return(affected);
        }
Пример #13
0
        public ActionResult Contact()
        {
            var users = new List <User>();

            using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString.Get("EscademyDB")))
            {
                conn.Open();

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT FirstName, LastName, ProfilePicture, Id FROM esc_accounts WHERE Id = 1 OR Id = 2";
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        users.Add(new User()
                        {
                            Id        = reader.GetInt32("Id"),
                            FirstName = reader.GetString("FirstName"),
                            LastName  = reader.GetString("LastName"),
                            Picture   = reader.GetString("ProfilePicture")
                        });
                    }
                }

                conn.Close();
            }
            ViewBag.users = users;

            return(View());
        }
Пример #14
0
 public override void LoadQueries()
 {
     MySql.Data.MySqlClient.MySqlConnection dbConn = this.ActiveConnection as MySql.Data.MySqlClient.MySqlConnection;
     if (dbConn != null && dbConn.State == System.Data.ConnectionState.Open)
     {
         // get procedures
         String sql = "SHOW PROCEDURE STATUS;";
         //String sql = "SHOW FUNCTION STATUS;";
         var cmd = dbConn.CreateCommand();
         cmd.CommandText = sql;
         var reader = cmd.ExecuteReader();
         Queries.Clear();
         while (reader.HasRows && reader.Read())
         {
             Query q = new Query();
             q.Name = reader[1].ToString();
             Queries.Add(q);
         }
         reader.Close();
         foreach (Query q in Queries)
         {
             q.Load(dbConn);
         }
     }
 }
Пример #15
0
        /// <summary>
        /// สร้าง mysql function ลง database server
        /// </summary>
        /// <param name="name">ชื่อ function</param>
        /// <returns>ข้อความแจ้งผลลัพท์</returns>
        static string CreateFunction(string name)
        {
            if (name == "truncatetime")
            {
                if (!Program.FunctionIsExist(name))
                {
                    using (var connection = new MySql.Data.MySqlClient.MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString))
                        using (var command = connection.CreateCommand())
                        {
                            var sb = new StringBuilder();
                            sb.AppendLine("CREATE DEFINER=`root`@`localhost` FUNCTION `TruncateTime`(dateValue DateTime) RETURNS date");
                            sb.AppendLine("BEGIN");
                            sb.AppendLine("RETURN Date(dateValue);");
                            sb.AppendLine("END");

                            command.CommandType = System.Data.CommandType.Text;
                            command.CommandText = sb.ToString();

                            connection.Open();
                            command.ExecuteNonQuery();
                            connection.Close();
                        }

                    return("Create \"TruncateTime\" successfully.");
                }
                else
                {
                    return("\"TruncateTime\" already exist.");
                }
            }

            return("Invalid function name.");
        }
Пример #16
0
        public IEnumerable <string> GetLocations()
        {
            string        connectionString = Settings.Default.SwissCenterConnectionString;
            IDbConnection connection       = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
            IDbCommand    cmd = connection.CreateCommand();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select location_id, name from media_locations";

            using (connection)
            {
                connection.Open();

                IDataReader reader = cmd.ExecuteReader();

                if (reader != null)
                {
                    using (reader)
                    {
                        while (reader.Read())
                        {
                            yield return(EnsureSlashes((string)reader["name"]));
                        }
                    }
                }
            }
        }
Пример #17
0
            } // Fetch

            public CmsPersistentVariable[] FetchAllWithNamePrefix(string namePrefix)
            {
                if (namePrefix.Trim() == "")
                {
                    return(new CmsPersistentVariable[0]);
                }


                using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
                {
                    OpenMySqlConnection(conn);

                    string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables ";
                    sql += " WHERE Name like @Name";

                    MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;

                    cmd.Parameters.AddWithValue("@Name", namePrefix + "%");

                    MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sqlDA.Fill(ds);

                    List <CmsPersistentVariable> arrayList = new List <CmsPersistentVariable>();
                    if (this.hasRows(ds))
                    {
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            arrayList.Add(GetFromRow(dr));
                        } // foreach row
                    }
                    return(arrayList.ToArray());
                }
            } // FetchAllWithNamePrefix
Пример #18
0
        private void TestADO()
        {
            var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);

            conn.Open();

            var cmd = conn.CreateCommand();

            cmd.CommandText = "select * from customers";

            var rdr = cmd.ExecuteReader();

            DataTable table = new DataTable();

            table.Load(rdr);

            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn col in table.Columns)
                {
                    Response.Write(row[col.ColumnName] + ", ");
                }
                Response.Write("<br />");
            }
            rdr.Close();
            rdr.Dispose();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
            } // Update

            public bool Delete(int PersistentVariableId)
            {
                if (PersistentVariableId < 0)
                {
                    return(false);
                }

                using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
                {
                    OpenMySqlConnection(conn);

                    MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE from persistentvariables where PersistentVariableId = @PersistentVariableId;";

                    cmd.Parameters.AddWithValue("@PersistentVariableId", PersistentVariableId);

                    int numUpdated = cmd.ExecuteNonQuery();

                    if (numUpdated < 1)
                    {
                        return(false);
                    }

                    return(true);
                }
            }
        public object GetBalance()
        {
            decimal balance = 0.0M;
            bool    auth    = false;

            if (EnsureUserRights(UserLevel.Coach))
            {
                auth = true;
                var user = GetCurrentUser();

                using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString.Get("EscademyMDB")))
                {
                    conn.Open();
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "SELECT Status, Amount FROM esc_Transactions WHERE ReceiverId=@accId";
                        cmd.Parameters.AddWithValue("@accId", user.Id);
                        var reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            if (reader.GetString("Status") == "Pending")
                            {
                                balance += reader.GetDecimal("Amount");
                            }
                        }
                    }
                    conn.Close();
                }
            }

            return(Json(new { Auth = auth ? "OK" : "NO_AUTH", Balance = balance }));
        }
            } // Fetch

            public CmsPersistentVariable[] FetchAll()
            {
                using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConfigUtils.getConfigValue("ConnectionString", "")))
                {
                    OpenMySqlConnection(conn);

                    string sql = "SELECT PersistentVariableId, Name, PersistedValue from persistentvariables; ";


                    MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;



                    MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sqlDA.Fill(ds);

                    List <CmsPersistentVariable> arrayList = new List <CmsPersistentVariable>();
                    if (this.hasRows(ds))
                    {
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            arrayList.Add(GetFromRow(dr));
                        } // foreach row
                    }     // if there is data

                    return(arrayList.ToArray());
                }
            } // FetchAll
Пример #22
0
        public Tuple <bool, string> Run(string id)
        {
            var model = _sqlpublishorderRepository.Table.FirstOrDefault(s => s.Id == id);

            model.IsRunning = true;
            model.Result    = "Running";
            _sqlpublishorderRepository.Update(model);

            try
            {
                if (model.YimiSqlProvider.SqlProviderType == SqlProviderType.MsSql)
                {
                    using (SqlConnection conn = new SqlConnection(model.YimiSqlProvider.Connectstring))
                    {
                        conn.Open();
                        //string sql = $"Use [{model.YimiSqlProvider.DbName}]" + System.Environment.NewLine;
                        //sql += model.SqlText + System.Environment.NewLine;
                        //sql += "go";

                        SqlCommand sqlCommand = conn.CreateCommand();
                        sqlCommand.CommandType    = System.Data.CommandType.Text;
                        sqlCommand.CommandTimeout = 1000;
                        sqlCommand.CommandText    = model.SqlText;

                        sqlCommand.ExecuteNonQuery();
                    }
                }
                else
                {
                    using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(model.YimiSqlProvider.Connectstring))
                    {
                        conn.Open();
                        var sqlCommand = conn.CreateCommand();
                        sqlCommand.CommandType    = System.Data.CommandType.Text;
                        sqlCommand.CommandTimeout = 1000;
                        sqlCommand.CommandText    = model.SqlText;

                        sqlCommand.ExecuteNonQuery();
                    }
                }


                model.IsRunning   = false;
                model.IsPublished = true;
                model.Result      = "Run Successfully!";
                _sqlpublishorderRepository.Update(model);
                return(new Tuple <bool, string>(true, ""));
            }
            catch (Exception ex)
            {
                model.IsRunning = false;

                model.Result = ex.Message;
                _sqlpublishorderRepository.Update(model);

                return(new Tuple <bool, string>(false, ex.Message));
            }
        }
Пример #23
0
        }//LireHDR
        private void MySqlInsertBD(string nomFichier, string codeClient, DateTime dateRéception, DateTime dateTraitement, string nomFichierOriginal, string numFaxExpediteur, bool traite) //Fonction qui fait les insertions dans la base de donnée concernant les fax
        {  
            try
            {               
                connection.Open();
                LocalInsertBD();

                MySql.Data.MySqlClient.MySqlCommand inserteFax = connection.CreateCommand();
                MySql.Data.MySqlClient.MySqlCommand nombreFax = connection.CreateCommand();

                nombreFax.CommandText = "SELECT LEFT ('datetraitement',10) AS DT, COUNT(*) FROM FAX GROUP BY DT";

                inserteFax.CommandText = "INSERT INTO fax (nomFichier, codeClient, dateReception, dateTraitement,nomFichierOriginal, numFaxExpediteur, traite) " +
                    " VALUES(@nf, @cd, @dateRecept, @dateTraite, @nfOriginal,@numFaxExp, @traite)";

                inserteFax.Parameters.AddWithValue("@nf", nomFichier);
                inserteFax.Parameters.AddWithValue("@cd", codeClient);
                inserteFax.Parameters.AddWithValue("@dateRecept", dateRéception);
                inserteFax.Parameters.AddWithValue("@dateTraite", dateTraitement);
                inserteFax.Parameters.AddWithValue("@nfOriginal", nomFichierOriginal);
                inserteFax.Parameters.AddWithValue("@numFaxExp", numFaxExpediteur);
                inserteFax.Parameters.AddWithValue("@traite", traite);
                //Insert les éléments dans la base de donnée MySql
                inserteFax.ExecuteNonQuery();
                inserteFax.Connection = connection;

                //Récupere le nombre de fax dans la base
                MySql.Data.MySqlClient.MySqlDataReader dataReader = nombreFax.ExecuteReader();
                if (dataReader.Read())
                {
                    Int32.TryParse(dataReader.GetValue(1).ToString(), out CompteurFaxBD);

                }
            }
            catch (DbException err) //Si une connection est impossible on enregistre les données dans un fichier texte
            {
                ++erreurBD;
                Console.Write(err);
                CompteurFaxEnAttente++;
                texte = texte + (nomFichier + ";" + codeClient + ";" + dateRéception + ";" + dateTraitement + ";" + nomFichierOriginal + ";" + numFaxExpediteur + ";" + traite) + Environment.NewLine;
   
            }
            connection.Close();
        }//MySqlInsertBD
Пример #24
0
        public List <NumEntry> GetTimeNumValue(string year, int fid)
        {
            string condition = " AND `key` > 1420041600 AND `key` < 1451577600";

            if (year == "2016")
            {
                condition = " AND `key` > 1451577600 AND `key` < 1483200000";
            }
            if (year == "2017")
            {
                condition = " AND `key` > 1483200000 AND `key` < 1514736000";
            }
            if (year == "2018")
            {
                condition = " AND `key` > 1514736000 AND `key` < 1546272000";
            }
            if (year == "2019")
            {
                condition = " AND `key` > 1546272000";
            }
            List <NumEntry> lne    = new List <NumEntry>();
            string          sql    = "SELECT * FROM time_number_entry WHERE feed_id = " + fid + condition;// + " limit 1000";
            string          conStr = StaticData.conStr;
            IDbConnection   cn     = new MySql.Data.MySqlClient.MySqlConnection(conStr);

            try
            {
                cn.Open();
                IDbCommand cmd = cn.CreateCommand();
                cmd.CommandTimeout = 3000;
                cmd.CommandText    = sql;
                IDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    NumEntry fm = new NumEntry();
                    fm.key     = r["key"] is DBNull ? null : r["key"].ToString();
                    fm.value   = r["value"] is DBNull ? 0 : Convert.ToDecimal(r["value"]);
                    fm.id      = r["id"] is DBNull ? 0 : Convert.ToInt32(r["id"]);
                    fm.feed_id = r["feed_id"] is DBNull ? -1 : Convert.ToInt32(r["feed_id"]);
                    lne.Add(fm);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                }
            }
            return(lne);
        }
Пример #25
0
        public List <FeedModel> GetFeeds()
        {
            List <FeedModel> lfs = new List <FeedModel>();
            string           sql = @"SELECT p2.p0t,p2.p1t,p2.title p2t,p.* from feed p INNER JOIN(
SELECT p1.p0t,p1.title p1t, p.*from feed p INNER JOIN(
(SELECT p0.title p0t, p.* FROM feed p INNER JOIN
(SELECT * FROM feed WHERE(creator_id = 50 or owner_id = 50) AND parent_id = 0 AND `status` != -1) p0
ON p.parent_id = p0.id)) p1
 ON p.parent_id = p1.id) p2
   ON p.parent_id = p2.id"; //白城

            //红光大道
            sql = @" SELECT p2.p0t,p2.p1t,p2.title p2t, p.*from feed p INNER JOIN(
SELECT p1.p0t, p1.title p1t, p.* from feed p INNER JOIN(
(SELECT p0.title p0t, p.* FROM feed p INNER JOIN
(SELECT * FROM feed WHERE(id = 17687) AND parent_id = 0 AND `status` != -1) p0
ON p.parent_id = p0.id)) p1
 ON p.parent_id = p1.id) p2
   ON p.parent_id = p2.id";

            string        conStr = StaticData.conStr;
            IDbConnection cn     = new MySql.Data.MySqlClient.MySqlConnection(conStr);

            try
            {
                cn.Open();
                IDbCommand cmd = cn.CreateCommand();
                cmd.CommandText = sql;
                IDataReader r = cmd.ExecuteReader();
                while (r.Read())
                {
                    FeedModel fm = new FeedModel();
                    fm.P0T        = r["p0t"] is DBNull ? null : (string)r["p0t"];
                    fm.P1T        = r["p1t"] is DBNull ? null : (string)r["p1t"];
                    fm.P2T        = r["p2t"] is DBNull ? null : (string)r["p2t"];
                    fm.ID         = r["id"] is DBNull ? -1 : Convert.ToInt32(r["id"]);
                    fm.Title      = r["title"] is DBNull ? null : (string)r["title"];
                    fm.Value_Type = r["value_type"] is DBNull ? -1 : (int)r["value_type"];
                    fm.Created    = r["created"] is DBNull ? DateTime.Now : DateTime.Parse(r["created"].ToString());
                    lfs.Add(fm);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                }
            }
            return(lfs);
        }
Пример #26
0
        private void SaveData(MySql.Data.MySqlClient.MySqlConnection connection, List <string> dynamicSqlCommandCollection, TCPUDPServer controlObject, DateTime Sample, int FrameIndex, int ParamIndex, byte AtnDataType, byte[] Atn1Data, byte[] Atn2Data, byte[] Atn3Data, byte[] Atn4Data, byte[] Atn5Data, byte[] Atn6Data, byte[] Atn7Data, byte[] Atn8Data)
        {
            // 开始事务处理
            MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
            MySql.Data.MySqlClient.MySqlCommand     command     = connection.CreateCommand();
            try
            {
                command.Parameters.AddWithValue("@Sample", Sample);
                command.Parameters.AddWithValue("@FrameIndex", FrameIndex);
                command.Parameters.AddWithValue("@ParamIndex", ParamIndex);
                command.Parameters.AddWithValue("@AtnDataType", AtnDataType);
                command.Parameters.AddWithValue("@Atn1Data", Atn1Data);
                command.Parameters.AddWithValue("@Atn2Data", Atn2Data);
                command.Parameters.AddWithValue("@Atn3Data", Atn3Data);
                command.Parameters.AddWithValue("@Atn4Data", Atn4Data);
                command.Parameters.AddWithValue("@Atn5Data", Atn5Data);
                command.Parameters.AddWithValue("@Atn6Data", Atn6Data);
                command.Parameters.AddWithValue("@Atn7Data", Atn7Data);
                command.Parameters.AddWithValue("@Atn8Data", Atn8Data);
                // 批量更新
                foreach (string cmd in dynamicSqlCommandCollection)
                {
                    command.CommandText = cmd;



                    command.ExecuteNonQuery();
                }

                // 提交数据入库
                transaction.Commit();
                //if (controlObject != null)
                //{
                //    // 提交错误日志
                //    // controlObject.AddStatusString("动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //    if (dynamicSqlCommandCollection.Count != 0)
                //    {
                //        this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //    }
                //    //  this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //}
            }
            catch (Exception ex)
            {
                // 放弃当前的数据入库操作
                transaction.Rollback();
                //if (controlObject != null)
                //{
                //   // this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                //    // 提交错误日志
                //    //controlObject.AddStatusString("动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                //}
                Console.WriteLine(ex);
            }
        }
Пример #27
0
        override public IDbCommand CreateCommand()
        {
            MySQLConnection sc = InternalConnection as MySQLConnection;

            if (null == sc)
            {
                throw new InvalidOperationException("InvalidConnType00" + InternalConnection.GetType().FullName);
            }

            return(sc.CreateCommand());
        }
Пример #28
0
        internal void TruncateMinuteWiseMeasures()
        {
            using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
            {
                mySqlConn.Open();
                var sqlCom = mySqlConn.CreateCommand();
                sqlCom.CommandText = @"TRUNCATE TABLE minute_wise;";
                sqlCom.ExecuteNonQuery();

                mySqlConn.Close();
            }
        }
        public void DeleteTable(MySql.Data.MySqlClient.MySqlConnection connect, MySql.Data.MySqlClient.MySqlCommand command, DataTable table)
        {
            var connectionString = String.Format("Server=localhost;Database=test;username=root;password=;Port=3306");

            using (connect = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                connect.Open();
                command             = connect.CreateCommand();
                command.CommandText = String.Format("DROP TABLE {0}", table.TableName);
                command.ExecuteNonQuery();
            }
        }
Пример #30
0
        public void TearDown()
        {
            string connStrDrop = LibLogic.Setup.DbFactoryWithoutDatabase.ConnectionString;
            var    cnDrop      = new MySql.Data.MySqlClient.MySqlConnection(connStrDrop);
            var    cmdDrop     = cnDrop.CreateCommand();

            cmdDrop.CommandText = string.Format("DROP DATABASE IF EXISTS `{0}`;", testingdb);
            cmdDrop.CommandType = System.Data.CommandType.Text;

            cnDrop.Open();
            cmdDrop.ExecuteNonQuery();
            cnDrop.Close();
        }
        public int Mysql_File_Save(int PropertyObject, int FileSize, string FileName, string Content_Type,
            int Height, int Width, byte[] Image, byte[] ImagePreview, bool IsDeleted)
        {
            int result = -1;
            using (MySql.Data.MySqlClient.MySqlConnection oConn =
                new MySql.Data.MySqlClient.MySqlConnection(this.connStr))
            {
                oConn.Open();

                MySql.Data.MySqlClient.MySqlCommand cmd = oConn.CreateCommand();
                cmd.Connection = oConn;


                //Add new 
                //oCommand.CommandText = "insert into cust_file(customer_id, filename, filedata, contenttype, length) " +
                //    "values( ?in_customer_id, ?in_filename, ?in_filedata, ?in_contenttype, ?in_length)";

                //INSERT INTO myrealty.images (id, img) VALUES (<INT(11)>, <LONGBLOB>);
                cmd.CommandText = @"SET GLOBAL max_allowed_packet=16*1024*1024; 
INSERT INTO ObjectImages (PropertyObject_Id, FileSize, FileName, Content_Type, Height, Width,
Image, ImagePreview, IsDeleted) VALUES (?PropertyObject, ?FileSize, ?FileName, ?Content_Type, ?Height, ?Width,
?Image, ?ImagePreview, ?IsDeleted); select last_insert_id();";


                //oCommand.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("?PropertyObject", PropertyObject);

                cmd.Parameters.Add("?FileSize", FileSize);

                cmd.Parameters.Add("?FileName", FileName);

                cmd.Parameters.Add("?Content_Type", Content_Type);

                cmd.Parameters.Add("?Height", Height);

                cmd.Parameters.Add("?Width", Width);

                cmd.Parameters.Add("?Image", MySql.Data.MySqlClient.MySqlDbType.LongBlob);
                cmd.Parameters["?Image"].Value = Image;

                cmd.Parameters.Add("?ImagePreview", MySql.Data.MySqlClient.MySqlDbType.LongBlob);
                cmd.Parameters["?ImagePreview"].Value = ImagePreview;

                cmd.Parameters.Add("?IsDeleted", IsDeleted);

                result = Convert.ToInt32(cmd.ExecuteScalar());
                oConn.Close();
            }
            return result;
        }
Пример #32
0
        public void InsertIntoFragt(Package package)
        {
            conn.ConnectionString = connectionstring;
            conn.Open();

            int price = 0;

            if (package.Price != "")
            {
                price = Convert.ToInt32(package.Price);
            }
            MySql.Data.MySqlClient.MySqlCommand command = conn.CreateCommand();
            command.CommandText = "INSERT INTO Fragt (`id`, `date`, `type`, `price`, `country`, `comment`) VALUES ("
                                  + package.Id + ", '"
                                  + package.Date.ToString("dd/MM/yyyy") + "', '"
                                  + package.Type + "', "
                                  + price + ", '"
                                  + package.Country + "', '"
                                  + package.Comment + "')";
            command.ExecuteNonQuery();

            conn.Close();
        }
Пример #33
0
        internal void TruncateAllTables()
        {
            using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
            {
                mySqlConn.Open();
                var sqlCom = mySqlConn.CreateCommand();
                sqlCom.CommandText = @"
            TRUNCATE TABLE plants;
            TRUNCATE TABLE user_has_plant;
            TRUNCATE TABLE kwh_by_day;
            TRUNCATE TABLE user_has_plant;
            TRUNCATE TABLE measure;
            TRUNCATE TABLE temporary_measure;";
                sqlCom.ExecuteNonQuery();

                mySqlConn.Close();
            }
        }
Пример #34
0
        private void ExecuteSqlFromFile(string path)
        {
            try
            {
                if (File.Exists(path))
                {
                    string sqlText = File.ReadAllText(path);
                    using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
                    {
                        mySqlConn.Open();
                        var sqlCom = mySqlConn.CreateCommand();
                        sqlCom.CommandText = sqlText;

                        sqlCom.ExecuteNonQuery();
                        mySqlConn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Пример #35
0
        private static void updateVersionInSQL(long svnVersion)
        {
            ConsoleLogStartAction(Resources.UILogging.UpdateSQL);

            var mysqlHost = ConfigurationManager.AppSettings[AppSettingKeys.MySqlHost];
            var mysqlUser = ConfigurationManager.AppSettings[AppSettingKeys.MySqlUser];
            var mysqlDatabase = ConfigurationManager.AppSettings[AppSettingKeys.MySqlDatabase];

            if (mysqlHost.isNullOrEmpty())
            {
                Console.WriteLine(Resources.Questions.MySQLHost);
                mysqlHost = Console.ReadLine();
            }

            if (mysqlUser.isNullOrEmpty())
            {
                Console.WriteLine(Resources.Questions.MySQLUser);
                mysqlUser = Console.ReadLine();
            }

            if (mysqlDatabase.isNullOrEmpty())
            {
                Console.WriteLine(Resources.Questions.MySQLDatabase);
                mysqlDatabase = Console.ReadLine();
            }

            Console.WriteLine(Resources.Questions.MySQLPassword);

            var connexionString = string.Empty;
            using (SecureString mysqlPassword = GetPassword())
            {
                connexionString = string.Format(Program.MySqlConnectionString, mysqlHost, mysqlDatabase, mysqlUser, mysqlPassword.ConvertToUnsecureString());

                using (var connexion = new MySql.Data.MySqlClient.MySqlConnection(connexionString))
                {
                    try
                    {
                        connexion.Open();
                        var command = connexion.CreateCommand();
                        command.CommandText = Program.MySqlUpdate;
                        command.Parameters.AddWithValue(Program.MySqlVersion, svnVersion);

                        command.ExecuteNonQuery();

                        connexion.Close();
                    }
                    catch (MySql.Data.MySqlClient.MySqlException ex)
                    {
                        Console.WriteLine(string.Format(Resources.Errors.MySqlUpdate, ex.Message));
                    }
                }
            }
            ConsoleEndAction();
        }
        public int AddPropertyObject(PropertyObject po)
        {
            int result = -1;
            using (MySql.Data.MySqlClient.MySqlConnection oConn =
                new MySql.Data.MySqlClient.MySqlConnection(this.connStr))
            {
                oConn.Open();

                MySql.Data.MySqlClient.MySqlCommand cmd = oConn.CreateCommand();
                cmd.Connection = oConn;

                cmd.CommandText = @"INSERT INTO PropertyObjects (City_Id, CityDistrict_Id, PropertyType_Id, PropertyAction_Id,
Title,PropertyDescription,RoomCount,TotalSpace,LivingSpace,KitchenSpace,BuildingTypeName_Id,Floor,CountFloors,IsNewBuilding,BalconAvailable,
BalconSpace,isBalconGlassed,ContactName,Price,Currency_Id,PriceForTypeName_Id,NoCommission,Phone1,Phone2,Phone3,Periods_Id,CreatedDate,SourceUrl,
UserOwner_Id,DistanceToCity,WCType_Id,CountPhotos,CommercialPropertyType_Id,ServiceType_Id,IsActive,IsDeleted,DeletedDate,LinkOfObjectGrab) values (?City, ?CityDistrict, ?PropertyType, 
?PropertyAction,
?Title,?PropertyDescription,?RoomCount,?TotalSpace,?LivingSpace,?KitchenSpace,?BuildingTypeName,?Floor,?CountFloors,?IsNewBuilding,?BalconAvailable,
?BalconSpace,?isBalconGlassed,?ContactName,?Price,?Currency,?PriceForTypeName,?NoCommission,?Phone1,?Phone2,?Phone3,?Periods,?CreatedDate,?SourceUrl,
?UserOwner,?DistanceToCity,?WCType,?CountPhotos,?CommercialPropertyType,?ServiceType,?IsActive,?IsDeleted,?DeletedDate,?LinkOfObjectGrab);
select last_insert_id();";

                cmd.Parameters.Add("?City", (po.City != null ? po.City.Id.ToString() : null));

                cmd.Parameters.Add("?CityDistrict", (po.CityDistrict != null ? po.CityDistrict.Id.ToString() : null));

                cmd.Parameters.Add("?PropertyType", (po.PropertyType != null ? po.PropertyType.Id.ToString() : null));

                cmd.Parameters.Add("?PropertyAction", (po.PropertyAction != null ? po.PropertyAction.Id.ToString() : null));

                cmd.Parameters.Add("?Title", po.Title);

                cmd.Parameters.Add("?PropertyDescription", po.PropertyDescription);

                cmd.Parameters.Add("?RoomCount", po.RoomCount);

                cmd.Parameters.Add("?TotalSpace", po.TotalSpace);

                cmd.Parameters.Add("?LivingSpace", po.LivingSpace);

                cmd.Parameters.Add("?KitchenSpace", po.KitchenSpace);

                cmd.Parameters.Add("?BuildingTypeName", (po.BuildingTypeName != null ? po.BuildingTypeName.Id.ToString() : null));

                cmd.Parameters.Add("?Floor", po.Floor);

                cmd.Parameters.Add("?CountFloors", po.CountFloors);

                cmd.Parameters.Add("?IsNewBuilding", po.IsNewBuilding);

                cmd.Parameters.Add("?BalconAvailable", po.BalconAvailable);

                cmd.Parameters.Add("?BalconSpace", po.BalconSpace);

                cmd.Parameters.Add("?isBalconGlassed", po.isBalconGlassed);

                cmd.Parameters.Add("?ContactName", po.ContactName);

                cmd.Parameters.Add("?Price", po.Price);

                cmd.Parameters.Add("?Currency", (po.Currency != null ? po.Currency.Id.ToString() : null));

                cmd.Parameters.Add("?PriceForTypeName", (po.PriceForTypeName != null ? po.PriceForTypeName.Id.ToString() : null));

                cmd.Parameters.Add("?NoCommission", po.NoCommission);

                cmd.Parameters.Add("?Phone1", po.Phone1);
                cmd.Parameters.Add("?Phone2", po.Phone2);
                cmd.Parameters.Add("?Phone3", po.Phone3);

                cmd.Parameters.Add("?Periods", (po.Periods != null ? po.Periods.Id.ToString() : null));

                cmd.Parameters.Add("?CreatedDate", po.CreatedDate);

                cmd.Parameters.Add("?SourceUrl", po.SourceUrl);

                cmd.Parameters.Add("?UserOwner", (po.UserOwner != null ? po.UserOwner.Id.ToString() : null));

                cmd.Parameters.Add("?DistanceToCity", po.DistanceToCity);

                cmd.Parameters.Add("?WCType", (po.WCType!=null?po.WCType.Id.ToString():null));

                cmd.Parameters.Add("?CountPhotos", po.CountPhotos);

                cmd.Parameters.Add("?CommercialPropertyType", (po.CommercialPropertyType != null ? po.CommercialPropertyType.Id.ToString() : null));

                cmd.Parameters.Add("?ServiceType", (po.ServiceType != null ? po.ServiceType.Id.ToString() : null));

                cmd.Parameters.Add("?IsActive", po.IsActive);

                cmd.Parameters.Add("?IsDeleted", po.IsDeleted);

                cmd.Parameters.Add("?DeletedDate", po.DeletedDate);

                cmd.Parameters.Add("?LinkOfObjectGrab", po.LinkOfObjectGrab);


                result = Convert.ToInt32(cmd.ExecuteScalar());
                oConn.Close();

            }
            return result;
        }
Пример #37
0
        private void button1_Click(object sender, EventArgs args)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            if (sfd.ShowDialog(this) == DialogResult.OK)
            {
                BinaryFormatter bf = new BinaryFormatter();
                List<Runner> runners = new List<Runner>();
                Event e = new Event();
                List<Result> results = new List<Result>();
                List<RadioControl> radious = new List<RadioControl>();

                MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("");
                conn.Open();
                MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from runners where tavid=" + textBox1.Text;
                IDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    runners.Add(new Runner()
                    {
                        Name = reader["name"] as string,
                        Class = reader["class"] as string,
                        club = reader["club"] as string,
                        dbId = Convert.ToInt32(reader["dbid"])

                    });
                }
                e.Runners = runners.ToArray();
                reader.Close();

                cmd.CommandText = "select * from results where tavid=" + textBox1.Text;
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    results.Add(new Result()
                    {
                        control = Convert.ToInt32(reader["control"]),
                        status = Convert.ToInt32(reader["status"]),
                        time = Convert.ToInt32(reader["time"]),
                        changed = Convert.ToDateTime(reader["changed"]),
                        dbId = Convert.ToInt32(reader["dbid"])

                    });
                }
                e.Results = results.ToArray();
                reader.Close();

                cmd.CommandText = "select * from splitcontrols where tavid=" + textBox1.Text;
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    radious.Add(new RadioControl()
                    {
                        classname = reader["classname"] as string,
                         code = Convert.ToInt32(reader["code"]),
                          corder = Convert.ToInt32(reader["corder"]),
                           name = reader["name"] as string
                    });
                }
                e.RadioControls = radious.ToArray();
                reader.Close();

                cmd.CommandText = "select * from login where tavid=" + textBox1.Text;
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    e.CompDate = Convert.ToDateTime(reader["compDate"]);
                    e.CompId = Convert.ToInt32(textBox1.Text);
                    e.CompName = reader["compname"] as string;
                    e.organizer = reader["organizer"] as string;
                    e.Public = Convert.ToBoolean(reader["public"]);

                }
                reader.Close();

                FileStream fs = File.Create(sfd.FileName);
                bf.Serialize(fs, e);
                fs.Close();
                conn.Close();
            }
        }
Пример #38
0
        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(textBox2.Text);
            try
            {
                conn.Open();
                MySql.Data.MySqlClient.MySqlCommand cmd = conn.CreateCommand();
                double numSeconds = ((TimeSpan)(replayEvent.Results.Max(x => x.changed) - replayEvent.Results.Min(x => x.changed))).TotalSeconds;
                cmd.CommandText = "delete from login where tavid=" + replayEvent.CompId;
                cmd.ExecuteNonQuery();
                cmd.CommandText = "insert into login(tavid,user,pass,compName,organizer,compDate,public) values(?id,?user,?pass,?compName,?organizer,?compdate,?public)";
                cmd.Parameters.AddWithValue("?id", replayEvent.CompId);
                cmd.Parameters.AddWithValue("?user", "");
                cmd.Parameters.AddWithValue("?pass", "");
                cmd.Parameters.AddWithValue("?compName", replayEvent.CompName);
                cmd.Parameters.AddWithValue("?organizer", replayEvent.organizer);
                cmd.Parameters.AddWithValue("?compDate", replayEvent.CompDate);
                cmd.Parameters.AddWithValue("?public", replayEvent.Public);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                cmd.CommandText = "delete from splitcontrols where tavid=" + replayEvent.CompId;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "insert into splitcontrols(tavid,classname,name,code,corder) values(?id,?class,?name,?code,?corder)";
                cmd.Parameters.AddWithValue("?id", replayEvent.CompId);
                cmd.Parameters.AddWithValue("?class", "");
                cmd.Parameters.AddWithValue("?name", "");
                cmd.Parameters.AddWithValue("?code", 0);
                cmd.Parameters.AddWithValue("?corder", 0);
                foreach (var r in replayEvent.RadioControls)
                {
                    cmd.Parameters["?class"].Value = r.classname;
                    cmd.Parameters["?name"].Value = r.name;
                    cmd.Parameters["?code"].Value = r.code;
                    cmd.Parameters["?corder"].Value = r.corder;
                    cmd.ExecuteNonQuery();
                }
                cmd.Parameters.Clear();

                cmd.CommandText = "delete from runners where tavid=" + replayEvent.CompId;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "delete from results where tavid=" + replayEvent.CompId;
                cmd.ExecuteNonQuery();

                WebClient wc = new WebClient();

                cmd.Parameters.AddWithValue("?compid", replayEvent.CompId);
                cmd.Parameters.AddWithValue("?name", "");
                cmd.Parameters.AddWithValue("?club", "");
                cmd.Parameters.AddWithValue("?class", "");
                cmd.Parameters.AddWithValue("?id", 0);
                cmd.CommandText = "REPLACE INTO runners VALUES (?compid,?name,?club,?class,0,?id)";
                foreach (var r in replayEvent.Runners)
                {
                    cmd.Parameters["?name"].Value = r.Name;
                    cmd.Parameters["?club"].Value = r.club;
                    cmd.Parameters["?class"].Value = r.Class;
                    cmd.Parameters["?id"].Value = r.dbId;
                    cmd.ExecuteNonQuery();
                }
                cmd.Parameters.Clear();

                cmd.Parameters.AddWithValue("?compid", replayEvent.CompId);
                cmd.Parameters.AddWithValue("?id", 0);
                cmd.Parameters.AddWithValue("?control", -1);
                cmd.Parameters.AddWithValue("?time", -1);
                cmd.Parameters.AddWithValue("?status", -1);
                cmd.CommandText = "REPLACE INTO Results VALUES(?compid,?id,?control,?time,?status,Now())";

                if (!string.IsNullOrEmpty(textBox3.Text))
                {
                    string startlist;
                    startlist = HttpUtility.HtmlDecode(wc.DownloadString(textBox3.Text));
                    foreach (var r in replayEvent.Runners)
                    {
                        int rIdx = startlist.IndexOf(r.Name);
                        if (rIdx > 0)
                        {
                            int sIdx = startlist.IndexOf(" class=\"t\">", rIdx);
                            sIdx += 11;
                            int eIdx = startlist.IndexOf("</td>", sIdx);
                            string start = startlist.Substring(sIdx, eIdx - sIdx);

                            string[] p = start.Split(':');
                            int starttime = 0;
                            if (p.Length == 3)
                            {
                                starttime = Convert.ToInt32(p[0]) * 360000
                                    + Convert.ToInt32(p[1]) * 6000 +
                                        +Convert.ToInt32(p[2]) * 100;
                            }
                            else
                            {
                                starttime = Convert.ToInt32(p[0]) * 360000
                                    + Convert.ToInt32(p[1]) * 6000;
                            }

                            cmd.Parameters["?id"].Value = r.dbId;
                            cmd.Parameters["?control"].Value = 100;
                            cmd.Parameters["?time"].Value = starttime;
                            cmd.Parameters["?status"].Value = 0;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }

                do
                {
                    DateTime clock = replayEvent.Results.Min(x => x.changed);
                    DateTime endClock = replayEvent.Results.Max(x => x.changed);

                    IDbCommand cmd2 = conn.CreateCommand();
                    cmd2.CommandText = "delete from results where tavid = " + replayEvent.CompId + " and control <> 100";
                    cmd2.ExecuteNonQuery();

                    while (clock <= endClock)
                    {
                        if (backgroundWorker1.CancellationPending)
                            break;

                        var next = replayEvent.Results.Where(x => x.changed >= clock).OrderBy(x => x.changed).First().changed;
                        IEnumerable<Result> changes = null;
                        if (((TimeSpan)(next - clock)).TotalMinutes > 10)
                        {
                            changes = replayEvent.Results.Where(x => x.changed >= clock && x.changed <= next);
                            clock = next;
                        }
                        else
                        {
                            changes = replayEvent.Results.Where(x => x.changed == clock);
                        }

                        lblTime.Invoke(new MethodInvoker(delegate
                            {
                                lblTime.Text = clock.ToString("yyyy-MM-dd HH:mm:ss");
                            }));

                        foreach (var c in changes)
                        {
                            cmd.Parameters["?id"].Value = c.dbId;
                            cmd.Parameters["?control"].Value = c.control;
                            cmd.Parameters["?time"].Value = c.time;
                            cmd.Parameters["?status"].Value = c.status;
                            cmd.ExecuteNonQuery();
                        }

                        if (jumpToNext)
                        {
                            clock = replayEvent.Results.Where(x => x.changed > clock).OrderBy(x => x.changed).First().changed;
                            jumpToNext = false;
                        }
                        else
                        {
                            clock = clock.AddSeconds(1);
                        }
                        Thread.Sleep(1000);
                    }
                } while (!backgroundWorker1.CancellationPending);
            }
            finally
            {
                conn.Close();
            }
        }
        static void Main(string[] args)
        {
            if (args == null) return;

            var db = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["UmecaDb"].ConnectionString);
            db.Open();

            var command = db.CreateCommand();
            command.CommandText = "select * from cat_device where is_obsolete = 0";

            command.CommandType = CommandType.Text;
            var reader = command.ExecuteReader();
            var result = new List<Device>();

            while (reader.Read())
            {
                result.Add(new Device
                {
                    Id = reader.GetInt64("id_device"),
                    Name = reader.GetString("name"),
                    Ip = reader.GetString("Ip"),
                    Port = reader.GetInt32("Port")
                });
            }

            db.Close();
            db.Dispose();

            var character = ' ';
            var service = new Program();

            do
            {
                switch (character)
                {
                    case '1': //Read all users
                        service.ReadUsers();

                        Console.Clear();
                        Console.WriteLine("Usuarios encontrados: {0}", service._usersList.Count);
                        Console.BackgroundColor = ConsoleColor.Yellow;
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine("{0,5} {1,-30} {2,-15} {3,-5} {4,-8} {5}", "ID", "Name", "Pass", "Role", "Enabled", "FP");
                        Console.BackgroundColor = ConsoleColor.Black;
                        Console.ForegroundColor = ConsoleColor.Gray;
                        foreach (var user in service._usersList)
                        {
                            Console.WriteLine("{0,5} {1,-30} {2,15} {3,5} {4,8} {5}", user.EnrollNumber, user.Name, user.Password, user.Privilege, user.Enabled, user.FingerPrints.Count);
                        }
                        Console.Write("Presione cualquier tecla para continuar...");
                        Console.ReadKey();
                        break;
                    case '2':
                        Console.Clear();
                        Console.WriteLine("Introduzca los siguientes datos:");

                        int enrollNumber, privilege, fingerIndex;
                        string line, name, password;
                        bool enabled;

                        do
                        {
                            Console.Write("ID: ");
                            line = Console.ReadLine();

                        } while (!int.TryParse(line, out enrollNumber));

                        Console.Write("Nombre: ");
                        name = Console.ReadLine();
                        Console.Write("Contraseña: ");
                        password = Console.ReadLine();

                        do
                        {
                            Console.Write("Rol: ");
                            line = Console.ReadLine();
                        } while (!int.TryParse(line, out privilege));

                        do
                        {
                            Console.Write("Habilitado: ");
                            line = Console.ReadLine();
                        } while (!bool.TryParse(line, out enabled));

                        do
                        {
                            Console.Write("Dedo: ");
                            line = Console.ReadLine();
                        } while (!int.TryParse(line, out fingerIndex) || fingerIndex < 0 || fingerIndex >= 10);

                        service.Write(enrollNumber, name, password, privilege, enabled, fingerIndex);

                        Console.Write("Presione cualquier tecla para continuar...");
                        Console.ReadKey();
                        break;
                    case '3':
                        Console.Clear();
                        Console.WriteLine("Introduzca los siguientes datos:");

                        do
                        {
                            Console.Write("ID: ");
                            line = Console.ReadLine();

                        } while (!int.TryParse(line, out enrollNumber));

                        do
                        {
                            Console.Write("Dedo: ");
                            line = Console.ReadLine();
                        } while (!int.TryParse(line, out fingerIndex) || fingerIndex < 0 || fingerIndex >= 10);

                        service.Enroll(enrollNumber, fingerIndex);

                        Console.Write("Presione cualquier tecla para continuar...");
                        Console.ReadKey();
                        break;
                    case '4':
                        service.ReadLogs();

                        Console.Clear();
                        Console.WriteLine("Logs encontrados: {0}", service._attendanceLogs.Count);
                        Console.BackgroundColor = ConsoleColor.Yellow;
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine("{0,-10} {1,10} {2,15}", "ID", "Event", "Date");
                        Console.BackgroundColor = ConsoleColor.Black;
                        Console.ForegroundColor = ConsoleColor.Gray;
                        foreach (var user in service._attendanceLogs)
                        {
                            Console.WriteLine("{0,-10} {1,10} {2,15}", user.EnrollNumber, user.InOutMode == 4 ? "In" : (user.InOutMode == 5 ? "Out" : user.InOutMode.ToString()), user.Date);
                        }
                        Console.Write("Presione cualquier tecla para continuar...");
                        Console.ReadKey();
                        break;
                    case 'T':
                        service.SetDateTime();
                        break;
                    case 'C':
                        service.Clear();
                        break;
                    case 'E':
                        service.Events();
                        break;
                }
                Menu();

            } while ((character = Console.ReadKey().KeyChar) != 'x');
        }
        public static void GenerateCodeForTable(string tablename, string templatetext, string destination)
        {
            var template = templatetext.Replace("[TABLE]", tablename.ToLower());

            using (var sw = new System.IO.StreamWriter(destination + tablename + ".cs"))
            using (var con = new MySql.Data.MySqlClient.MySqlConnection(GetMangosConnectionString))
            {
                con.Open();
                using (var command = con.CreateCommand())
                {
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = string.Format("select * from {0} limit 1", tablename);

                    try
                    {
                        var sb_object = new StringBuilder();
                        var sb_Insert_part1 = new StringBuilder();
                        var sb_Insert_part2 = new StringBuilder();
                        var sb_Insert_part3 = new StringBuilder();
                        var sb_Update = new StringBuilder();
                        var sb_Delete = new StringBuilder();

                        sb_Insert_part1.Append("INSERT IGNORE INTO `\" + TableName + \"` (");
                        sb_Insert_part2.Append(" VALUES (");
                        sb_Insert_part3.Append("\", ");

                        sb_Update.AppendLine("\t\t\tsb.Append(\"UPDATE `\" + TableName + \"` SET \");");

                        using (var dr = command.ExecuteReader())
                        {
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                string fieldname = dr.GetName(i).ToLower();
                                string valuename = fieldname;

                                if (valuename == "class") { valuename = "class_"; }
                                if (valuename == "event") { valuename = "event_"; }

                                if (i != 0)
                                {
                                    sb_Update.AppendLine("\t\t\tif(" + valuename + " != null)");
                                    sb_Update.AppendLine("\t\t\t{");
                                    if (dr.GetFieldType(i) == typeof(System.String))
                                    {
                                        sb_Update.AppendLine("\t\t\t\tsb.AppendLine(\"`" + fieldname + "`='\" + " + valuename + ".ToSQL() + \"'\");");
                                    }
                                    else if (dr.GetFieldType(i) == typeof(System.Single))
                                    {
                                        sb_Update.AppendLine("\t\t\t\tsb.AppendLine(\"`" + fieldname + "`='\" + ((Decimal)" + valuename + ".Value).ToString() + \"'\");");
                                    }
                                    else
                                    {
                                        sb_Update.AppendLine("\t\t\t\tsb.AppendLine(\"`" + fieldname + "`='\" + " + valuename + ".Value.ToString() + \"'\");");
                                    }
                                    sb_Update.AppendLine("\t\t\t}");
                                }

                                if (i == dr.FieldCount - 1)
                                {
                                    sb_Insert_part1.Append("`" + fieldname + "`{" + (i + 1).ToString() + "})");
                                    sb_Insert_part2.Append("'{" + i.ToString() + "}'{" + (i + 2).ToString() + "});");
                                    if (dr.GetFieldType(i) == typeof(System.String))
                                    {
                                        sb_Insert_part3.Append(valuename + ".ToSQL(), GetInsertCommandCustomFields(), GetInsertCommandCustomValues());");
                                    }
                                    else if (dr.GetFieldType(i) == typeof(System.Single))
                                    {
                                        sb_Insert_part3.Append("((Decimal)" + valuename + ".GetValueOrDefault()), GetInsertCommandCustomFields(), GetInsertCommandCustomValues());");
                                    }
                                    else
                                    {
                                        sb_Insert_part3.Append(valuename + ".GetValueOrDefault(), GetInsertCommandCustomFields(), GetInsertCommandCustomValues());");
                                    }
                                }
                                else
                                {
                                    sb_Insert_part1.Append(string.Format("`{0}`, ", fieldname));
                                    sb_Insert_part2.Append("'{" + i.ToString() + "}', ");

                                    if (dr.GetFieldType(i) == typeof(System.String))
                                    {
                                        sb_Insert_part3.Append(valuename + ".ToSQL(), ");
                                    }
                                    else if (dr.GetFieldType(i) == typeof(System.Single))
                                    {
                                        sb_Insert_part3.Append("((Decimal)" + valuename + ".GetValueOrDefault()), ");
                                    }
                                    else
                                    {
                                        sb_Insert_part3.Append(valuename + ".GetValueOrDefault(), ");
                                    }
                                }

                                if (dr.GetFieldType(i) == typeof(System.String))
                                {
                                    sb_object.AppendLine(string.Format("\t\tpublic {0} {1};", dr.GetFieldType(i), valuename));
                                }
                                else
                                {
                                    sb_object.AppendLine(string.Format("\t\tpublic {0}? {1};", dr.GetFieldType(i), valuename));
                                }
                            }
                            string idvaluename = dr.GetName(0).ToLower();

                            if (idvaluename == "class") { idvaluename = "class_"; }
                            if (idvaluename == "event") { idvaluename = "event_"; }
                            sb_Update.AppendLine("\t\t\t\tsb = sb.Replace(\"\\r\\n\", \", \");");
                            sb_Update.AppendLine("\t\t\t\tsb.Append(\" WHERE `" + dr.GetName(0).ToLower() + "`='\" + " + idvaluename + ".Value.ToString() + \"';\");");
                            sb_Update.AppendLine("\t\t\t\tsb = sb.Replace(\",  WHERE\", \" WHERE\");");
                            sb_Delete.Append(" `" + dr.GetName(0).ToLower() + "`='\" + " + idvaluename + ".Value.ToString() + \"';\"");
                        }

                        template = template.Replace("[FIELDS]", sb_object.ToString());
                        template = template.Replace("[INSERT]", sb_Insert_part1.ToString() + sb_Insert_part2.ToString() + sb_Insert_part3.ToString());
                        template = template.Replace("[UPDATE]", sb_Update.ToString());
                        template = template.Replace("[DELETE]", sb_Delete.ToString());

                        sw.Write(template);
                    }
                    catch (Exception exc)
                    {
                        Console.WriteLine("Error: {0}, {1}", exc.Message, command.CommandText);
                    }
                }
            }
        }
Пример #41
0
        private void importar()
        {
            label1.Text = "Iniciando Importação. Aguarde";
            label1.Visible = true;

            Cursor.Current = Cursors.WaitCursor;
            Application.DoEvents();

            limpaTabelas();
            label1.Text = "Dados locais apagados";
            Application.DoEvents();

            //abre conexao
            try
            {
                myconn = new MySql.Data.MySqlClient.MySqlConnection(strconn);
                myconn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Não foi possível conectar ao servidor, verifique a sua conexão com a internet.");
                return;
            }

            if (myconn.State == ConnectionState.Open)
            {
                Cursor.Current = Cursors.WaitCursor;
                Application.DoEvents();
                //inicia a transacao
                MySql.Data.MySqlClient.MySqlCommand mycommand = myconn.CreateCommand();
                mycommand.Connection = myconn;
                mycommand.CommandText =
                    "SELECT `uso_mutuo`.`id`,"+
                        "`uso_mutuo`.`descricao` "+
                    "FROM `eletrocad`.`uso_mutuo`";
                //mycommand.Parameters.Clear();
                //mycommand.Parameters.AddWithValue("estado", cbEstado.Text);
                //mycommand.Parameters.AddWithValue("ano", txtAno.Value);

                try
                {
                    MySql.Data.MySqlClient.MySqlDataReader reader = mycommand.ExecuteReader();
                    while (reader.Read())
                    {
                        //
                        //insere nova programação
                        db.eletrocadDataSet.uso_mutuoRow pipRow;
                        pipRow = eletrocadDataSet.uso_mutuo.Newuso_mutuoRow();
                        pipRow.id = reader.GetInt32("id");
                        pipRow.descricao = reader.GetString("descricao");
                        eletrocadDataSet.uso_mutuo.Rows.Add(pipRow);

                    }
                    uso_mutuoTableAdapter.Update(eletrocadDataSet.uso_mutuo);
                    eletrocadDataSet.uso_mutuo.AcceptChanges();
                    uso_mutuoTableAdapter.Fill(eletrocadDataSet.uso_mutuo);

                    reader.Close();

                    Cursor.Current = Cursors.Default;
                    Application.DoEvents();
                    MessageBox.Show("Informações recuperadas com sucesso");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Não foi possível importar: " + ex.Message);
                }
                finally
                {
                    //myconn.Close();
                }
            }
            myconn.Close();
            label1.Visible = false;
            Cursor.Current = Cursors.Default;
            Application.DoEvents();
        }