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);
}
}
}
}
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();
}
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);
}
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);
}
}
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);
}
}
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();
}
}
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;
}
}
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);
}
}
/// <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); }
}
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;
}
}
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);
}
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);
}
/// <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);
// }
}
}
}
public bool RemoveUserInfo(string key)
{
var connection = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
return(connection.Execute($"delete from {tmpUserCacheTableName} where `key`='{key}'") > 0);
}
