Пример #1
0
        public void UpdateMatchup(MatchupModel model)
        {
            using (IDbConnection connection =
                       new MySql.Data.MySqlClient.MySqlConnection(GlobalConfig.CnnString(db)))
            {
                // spMatchups_Update @id, @WinnerId
                var p = new DynamicParameters();
                if (model.Winner != null)
                {
                    p.Add("@Id", model.Id);
                    p.Add("@WinnerId", model.Winner.Id);

                    connection.Execute("dbo.spMatchups_Update", p, commandType: CommandType.StoredProcedure);
                }

                // spMatchupEntries_Update id, TeamCompeting, Score
                foreach (MatchupEntryModel me in model.Entries)
                {
                    if (me.TeamCompeting != null)
                    {
                        p = new DynamicParameters();
                        p.Add("@Id", me.Id);
                        p.Add("@TeamCompetingId", me.TeamCompeting.Id);
                        p.Add("@Score", me.Score);

                        connection.Execute("dbo.spMatchupEntries_Update", p, commandType: CommandType.StoredProcedure);
                    }
                }
            }
        }
Пример #2
0
        public static void Dosomething3()
        {
            Console.WriteLine("..............................................................");
            string tmpConnection = "server=rds5n95035s53i40c6m5o.mysql.rds.aliyuncs.com;uid=qingwatianya;pwd=qingwatianya101674;database=mvctest;Allow User Variables=True";

            MySql.Data.MySqlClient.MySqlConnection ff = new MySql.Data.MySqlClient.MySqlConnection(tmpConnection);
            string tmpSql = "UPDATE modules SET Module_URL='2222';";

            ff.Execute(tmpSql);

            //using (System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\log33332.txt", true))
            //{
            //    sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + "JobDone.--------------------------------------------------");
            //    sw.Flush();
            //    sw.Close();
            //    sw.Dispose();
            //}

            //FileStream fs = new FileStream(@"C:\example.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
            //StreamWriter sw = new StreamWriter(fs);
            //sw.BaseStream.Seek(0, SeekOrigin.End);
            //sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + "JobDone.--------------------------------------------------");
            //sw.Flush();
            //fs.Flush(true);
            //sw.Close();
            //fs.Close();
            //sw.Dispose();
            //fs.Dispose();
        }
Пример #3
0
        public static void FFF()
        {
            string tmpConnection = "server=rds5n95035s53i40c6m5o.mysql.rds.aliyuncs.com;uid=qingwatianya;pwd=qingwatianya101674;database=mvctest;Allow User Variables=True";

            MySql.Data.MySqlClient.MySqlConnection ff = new MySql.Data.MySqlClient.MySqlConnection(tmpConnection);
            string tmpSql = string.Format("UPDATE modules SET Module_URL='{0}';", DateTime.Now.ToString());

            ff.Execute(tmpSql);
        }
Пример #4
0
        public void UpdateState(Order order)
        {
            using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionStr))
            {
                connection.Open();

                connection.Execute("update oms_order set order_status=@Status where id=@Id", order);
            }
        }
Пример #5
0
 public static void SetName(string id, string name)
 {
     using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
     {
         connection.Execute("insert into useridname values(@id1, @name1)", new
         {
             id1   = id,
             name1 = name
         });
     }
 }
        public TeamModel CreateTeam(TeamModel model)
        {
            using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(GlobalConfig.CnnString(db)))
            {
                var p = new DynamicParameters();
                p.Add("TeamName", model.TeamName);
                p.Add("id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
                connection.Execute("spTeams_Insert", p, commandType: CommandType.StoredProcedure);
                model.Id = p.Get <int>("id");

                foreach (PersonModel tm in model.TeamMembers)
                {
                    p = new DynamicParameters();
                    p.Add("PersonId", tm.Id);
                    p.Add("TeamId", model.Id);
                    connection.Execute("spTeamMembers_Insert", p, commandType: CommandType.StoredProcedure);
                }
                return(model);
            }
        }
Пример #7
0
        public void Execute(string connection, string sql, object parameters)
        {
            using (var c = new MySqlData.MySqlClient.MySqlConnection(connection))
            {
                _log.LogTrace <MySqlQuery>($"EXECUTE: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}");

                c.Open();
                c.Execute(sql, parameters);
                c.Close();
            }
        }
Пример #8
0
 public void Delete(Shopping shopping)
 {
     try
     {
         using (var sqlConnection = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
         {
             sqlConnection.Execute("DELETE FROM ShoppingList WHERE IdItem = @idItem", shopping);
         }
     }
     catch (Exception)
     {
         throw;
     }
 }
Пример #9
0
 public void CreateListItem(Shopping shopping)
 {
     try
     {
         using (var sqlConnection = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
         {
             var sqlExec = sqlConnection.Execute("INSERT INTO ShoppingList(Description) VALUES (@Description)", shopping);
         }
     }
     catch (Exception)
     {
         throw;
     }
 }
 public PersonModel CreatePerson(PersonModel model)
 {
     using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(GlobalConfig.CnnString(db)))
     {
         var p = new DynamicParameters();
         p.Add("FirstName", model.FirstName);
         p.Add("LastName", model.LastName);
         p.Add("EmailAddress", model.EmailAddress);
         p.Add("CellphoneNumb", model.CellphoneNumb);
         p.Add("id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
         connection.Execute("spPeople_Insert", p, commandType: CommandType.StoredProcedure);
         model.Id = p.Get <int>("id");
         return(model);
     }
 }
 // TODO - Make the CreatePrize method actually save to the database
 /// <summary>
 /// Saves PrizeModel to the database
 /// </summary>
 /// <param name="model">The prize information</param>
 /// <returns>the prize information, including the unique identifier</returns>
 public PrizeModel CreatePrize(PrizeModel model)
 {
     using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(GlobalConfig.CnnString(db)))
     {
         var p = new DynamicParameters();
         p.Add("PlaceNumber", model.PlaceNumber);
         p.Add("PlaceName", model.PlaceName);
         p.Add("PrizeAmount", model.PrizeAmount);
         p.Add("PricePercentage", model.PrizePercentage);
         p.Add("id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
         connection.Execute("spPrizes_Insert", p, commandType: CommandType.StoredProcedure);
         model.Id = p.Get <int>("id");
         return(model);
     }
 }
Пример #12
0
 /// <summary>
 ///  更新
 /// </summary>
 /// <typeparam name="T">模型</typeparam>
 /// <param name="sql">SQL语句</param>
 /// <param name="obj">匿名函数new{}</param>
 /// <param name="connString">链接字符串</param>
 /// <returns></returns>
 public static bool Update(string sql, object obj, string connString)
 {
     try
     {
         using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
         {
             var result = conn.Execute(sql, obj);
             if (result > 0)
             {
                 return(true);
             }
             return(false);
         }
     }
     catch { return(false); }
 }
Пример #13
0
 public void UpdateListItem(Shopping shopping)
 {
     try
     {
         using (var sqlConnection = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
         {
             sqlConnection.Execute(@"UPDATE ShoppingList
                                    SET Description = @Description
                                    WHERE IdItem = @IdItem", shopping);
         }
     }
     catch (Exception)
     {
         throw;
     }
 }
Пример #14
0
        public int Execute(string sql)
        {
            int i = 0;

            try
            {
                //using (var cn = new System.Data.SqlClient.SqlConnection(ConnStr))
                //{
                //    i = cn.Execute(sql);
                //}

                if (_ProviderName == "System.Data.SqlClient")
                {
                    using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(ConnStr))
                    {
                        i = cn.Execute(sql);
                    }
                }
                else if (_ProviderName == "MySql.Data.MySqlClient")
                {
                    using (MySql.Data.MySqlClient.MySqlConnection cn = new MySql.Data.MySqlClient.MySqlConnection(ConnStr))
                    {
                        i = cn.Execute(sql);
                    }
                }
                else
                {
                    using (System.Data.SqlClient.SqlConnection cn = SqlClient)
                    {
                        i = cn.Execute(sql);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(i);
        }
Пример #15
0
        public int Execute(List <SqlObject> SqlList)
        {
            int i = 0;

            try
            {
                //using (var cn = new System.Data.SqlClient.SqlConnection(ConnStr))
                //{
                //    using (var transactionScope = new TransactionScope())
                //    {
                //        foreach (SqlObject d in SqlList)
                //        {
                //            i += cn.Execute(d.sql, d.param);
                //        }

                //        transactionScope.Complete();
                //    }
                //}

                if (_ProviderName == "System.Data.SqlClient")
                {
                    using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(ConnStr))
                    {
                        using (var transactionScope = new TransactionScope())
                        {
                            foreach (SqlObject d in SqlList)
                            {
                                i += cn.Execute(d.sql, d.param);
                            }

                            transactionScope.Complete();
                        }
                    }
                }
                else if (_ProviderName == "MySql.Data.MySqlClient")
                {
                    using (MySql.Data.MySqlClient.MySqlConnection cn = new MySql.Data.MySqlClient.MySqlConnection(ConnStr))
                    {
                        using (var transactionScope = new TransactionScope())
                        {
                            foreach (SqlObject d in SqlList)
                            {
                                i += cn.Execute(d.sql, d.param);
                            }

                            transactionScope.Complete();
                        }
                    }
                }
                else
                {
                    using (System.Data.SqlClient.SqlConnection cn = SqlClient)
                    {
                        using (var transactionScope = new TransactionScope())
                        {
                            foreach (SqlObject d in SqlList)
                            {
                                i += cn.Execute(d.sql, d.param);
                            }

                            transactionScope.Complete();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(i);
        }
Пример #16
0
        /// <summary>
        /// Adds boats to the race db. Runs logic for for whether they have crew or not.
        /// </summary>
        /// <param name="boat"></param>
        public static void SetBoats(Boats boat)
        {
            using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
            {
                // If they don't have crew remove single.
                if (Program.Globals.Crew == null)
                {
                    // Try to add them.
                    try
                    {
                        InsertInto(boat, 0);
                    }

                    // Else add new DB for new race then add them
                    catch
                    {
                        var sql = new StringBuilder();
                        sql.Append("CREATE TABLE if not exists  ");
                        sql.Append(_race);
                        sql.Append(
                            " (`name` varchar(50) NOT NULL,`boat` varchar(50) DEFAULT NULL," +
                            "`boatNumber` int(11) DEFAULT NULL," +
                            "`crew` int(1) DEFAULT NULL,PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET" +
                            " = utf8mb4;");
                        connection.Execute(sql.ToString());
                        InsertInto(boat, 0);
                    }
                }

                //Else remove both.
                else
                {
                    // Try to add one person.
                    try
                    {
                        InsertInto(boat, 0);
                    }

                    // Else create db and then add person.
                    catch
                    {
                        var sql = new StringBuilder();
                        sql.Append("CREATE TABLE if not exists  ");
                        sql.Append(_race);
                        sql.Append(
                            " (`name` varchar(50) NOT NULL,`boat` varchar(50) DEFAULT NULL," +
                            "`boatNumber` int(11) DEFAULT NULL," +
                            "`crew` int(1) DEFAULT NULL,PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET" +
                            " = utf8mb4;");
                        connection.Execute(sql.ToString());
                        InsertInto(boat, 0);
                    }

                    // In every case we will add the second without fail.
//                    finally
//                    {

                    var boats1 = new Boats(Program.Globals.Crew, boat.BoatName, boat.BoatNumber);
                    InsertInto(boats1, 1);

//                    }
                }
            }
        }
Пример #17
0
        public bool RemoveUserInfo(string key)
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);

            return(connection.Execute($"delete from {tmpUserCacheTableName} where `key`='{key}'") > 0);
        }