public void CommitAfterConnectionDead()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(id INT, name VARCHAR(20))");
string connStr = GetConnectionString(true) + ";pooling=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlTransaction trans = c.BeginTransaction();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1, 'boo')", c))
{
cmd.ExecuteNonQuery();
}
KillConnection(c);
try
{
trans.Commit();
Assert.Fail("Should have thrown an exception");
}
catch (Exception ex)
{
}
Assert.AreEqual(ConnectionState.Closed, c.State);
c.Close(); // this should work even though we are closed
}
}
private void init(string connString)
{
connection = new MySqlConnection(connString);
connection.Open();
connection.ChangeDatabase(Server.MySQLDatabaseName);
transaction = connection.BeginTransaction();
}
public void BeginTransactionOnPreviouslyOpenConnection()
{
string connStr = GetConnectionString(true);
MySqlConnection c = new MySqlConnection(connStr);
c.Open();
c.Close();
try
{
c.BeginTransaction();
}
catch (Exception ex)
{
Assert.AreEqual("The connection is not open.", ex.Message);
}
}
public void RollingBackOnClose()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT) TYPE=InnoDB");
string connStr = GetConnectionString(true) + ";pooling=true;";
MySqlConnection c = new MySqlConnection(connStr);
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1)", c);
c.BeginTransaction();
cmd.ExecuteNonQuery();
c.Close();
MySqlConnection c2 = new MySqlConnection(connStr);
c2.Open();
MySqlCommand cmd2 = new MySqlCommand("SELECT COUNT(*) from Test", c2);
c2.BeginTransaction();
object count = cmd2.ExecuteScalar();
c2.Close();
Assert.AreEqual(0, count);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (MySqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
public bool AgregarUtilizandoLaMismaConexion(CierreDePeriodoEN oRegistroEN, DatosDeConexionEN oDatos)
{
Cnn = new MySqlConnection(TraerCadenaDeConexion(oDatos));
Cnn.Open();
MySqlTransaction MysqlTransaccion;
MysqlTransaccion = Cnn.BeginTransaction();
try
{
String mensaje = "";
TasaDeCambioAD oTasaDeCambioAD = new TasaDeCambioAD();
if (oTasaDeCambioAD.Agregar(oRegistroEN.oTasaDeCambioEN, oDatos, ref Cnn, ref MysqlTransaccion) == false)
{
mensaje = String.Format("Error : '{0}', producido al intentar guardar la tasa de cambio", oTasaDeCambioAD.Error);
throw new System.ArgumentException(mensaje);
}
if (Agregar(oRegistroEN, oDatos, ref Cnn, ref MysqlTransaccion) == false)
{
mensaje = String.Format("Error : '{0}', producido al intentar guardar el cierre del periodo", this.Error);
throw new System.ArgumentException(mensaje);
}
PeriodoAD oPeriodoAD = new PeriodoAD();
if (oPeriodoAD.ActualizarElEstadoDelPeriodo(oRegistroEN.oPeriodoEN, oDatos, ref Cnn, ref MysqlTransaccion) == false)
{
mensaje = String.Format("Error : '{0}', producido al intentar actualizar el estado del periodo", oPeriodoAD.Error);
throw new System.ArgumentException(mensaje);
}
if (AplicarSaldoAlCierre(oRegistroEN, oDatos, ref Cnn, ref MysqlTransaccion) == false)
{
mensaje = String.Format("Error : '{0}', producido al intentar realizar el cierre del periodo", this.Error);
throw new System.ArgumentException(mensaje);
}
oPeriodoAD = null;
MysqlTransaccion.Commit();
return(true);
}catch (Exception ex)
{
this.Error = ex.Message;
MysqlTransaccion.Rollback();
oTransaccionesAD = new TransaccionesAD();
DescripcionDeOperacion = string.Format("Se produjo el seguiente error: '{2}' al aplicar el cierre del periodo. {0} {1} ", Environment.NewLine, InformacionDelRegistro(oRegistroEN), ex.Message);
//Agregamos la Transacción....
TransaccionesEN oTran = InformacionDelaTransaccion(oRegistroEN, "Cerrar Periodo", "Cerrar periodo de Movimiento de cuentas", "ERROR");
oTransaccionesAD.Agregar(oTran, oDatos);
return(false);
}
finally
{
if (Cnn != null)
{
if (Cnn.State == ConnectionState.Open)
{
Cnn.Close();
}
}
Cnn = null;
Comando = null;
Adaptador = null;
oTransaccionesAD = null;
MysqlTransaccion = null;
}
}
static void Main(string[] args)
{
var con = new MySqlConnection(
"server = 192.168.13.86;User Id = root;password = 123456789;Database = SkyChen");
var user = new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "18988563330",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
};
var insertResult = con.CommandSet <SysUser>().Insert(user);
Console.WriteLine("Insert添加数{0}", insertResult);
user.Email = "*****@*****.**";
user.SysUserid = Guid.NewGuid().ToString("N");
var ifNotExistsResult = con.CommandSet <SysUser>().IfNotExists(a => a.Email == "*****@*****.**").Insert(user);
Console.WriteLine("IfNotExists添加数{0}", ifNotExistsResult);
user.SysUserid = Guid.NewGuid().ToString("N");
var ifNotExistsResult2 = con.CommandSet <SysUser>().IfNotExists(a => a.Email == "*****@*****.**").Insert(user);
Console.WriteLine("IfNotExists2添加数{0}", ifNotExistsResult2);
var getResult = con.QuerySet <SysUser>().WithNoLock().Get();
getResult.Email = "*****@*****.**";
var updateModelResult = con.CommandSet <SysUser>().Update(getResult);
Console.WriteLine("Update添加数{0}", updateModelResult);
var countResult = con.QuerySet <SysUser>().WithNoLock().Where(a => a.Email == "*****@*****.**").Count();
Console.WriteLine("Count数{0}", countResult);
var toListResult = con.QuerySet <SysUser>().WithNoLock().Where(a => a.Email == "*****@*****.**")
.OrderBy(a => a.CreateDatetime).Top(2).Select(a => a.Email).ToList();
Console.WriteLine("ToList数{0}", toListResult.Count());
var listResult2 = con.QuerySet <SysUser>().WithNoLock().Where(a => a.Email == "*****@*****.**")
.OrderBy(a => a.CreateDatetime).Select(a => a.Email).PageList(2, 2);
Console.WriteLine("PageList:{0}", listResult2.TotalPage);
var updateResult4 = con.QuerySet <SysUser>().WithNoLock().Sum(a => a.UserStatus);
Console.WriteLine("Sum:{0}", updateResult4);
var listResult3 = con.QuerySet <SysUser>().WithNoLock().Where(a => a.Email.Equals("*****@*****.**"))
.OrderBy(a => a.CreateDatetime).Select(a => a.Email).PageList(2, 2);
var deleteResult = con.CommandSet <SysUser>().Delete();
Console.WriteLine("Delete:{0}", deleteResult);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
using (var transaction = con.BeginTransaction())
{
con.CommandSet <SysUser>(transaction).Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "11111111111",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
});
con.CommandSet <SysUser>(transaction).Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "22222222222",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
});
transaction.Commit();
}
con.Transaction(tc =>
{
var SysUserid1 = tc.QuerySet <SysUser>().Where(a => a.Mobile == "18988561110").Select(a => a.SysUserid).Get();
var SysUserid2 = tc.QuerySet <SysUser>().Where(a => a.Mobile == "18988561111").Select(a => a.SysUserid).Get();
tc.CommandSet <SysUser>().Where(a => a.SysUserid == SysUserid1).Delete();
tc.CommandSet <SysUser>().Where(a => a.SysUserid == SysUserid2).Delete();
tc.CommandSet <SysUser>().Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "13536059332",
RealName = "大笨贞",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
});
}, ex =>
{
//do something
});
con.Dispose();
}
/// <summary>
/// 初始化配置数据，首次运行项目才需要
/// </summary>
public void InitConfiguration()
{
#region
var cityJsonString = @"[
{
'name': '南京',
'code': 'nanjing'
},
{
'name': '常州',
'code': 'changzhou'
},
{
'name': '淮安',
'code': 'huaian'
},
{
'name': '连云港',
'code': 'lianyungang'
},
{
'name': '南通',
'code': 'nantong'
},
{
'name': '苏州',
'code': 'suzhou'
},
{
'name': '泰州',
'code': 'tz'
},
{
'name': '无锡',
'code': 'wuxi'
},
{
'name': '宿迁',
'code': 'suqian'
},
{
'name': '徐州',
'code': 'xuzhou'
},
{
'name': '盐城',
'code': 'yancheng'
},
{
'name': '扬州',
'code': 'yangzhou'
},
{
'name': '镇江',
'code': 'zhenjiang'
},
{
'name': '杭州',
'code': 'hangzhou'
},
{
'name': '湖州',
'code': 'huzhou'
},
{
'name': '嘉兴',
'code': 'jiaxing'
},
{
'name': '金华',
'code': 'jinhua'
},
{
'name': '丽水',
'code': 'lishui'
},
{
'name': '宁波',
'code': 'ningbo'
},
{
'name': '衢州',
'code': 'quzhou'
},
{
'name': '绍兴',
'code': 'shaoxing'
},
{
'name': '台州',
'code': 'taizhou'
},
{
'name': '温州',
'code': 'wenzhou'
},
{
'name': '舟山',
'code': 'zhoushan'
},
{
'name': '福州',
'code': 'fuzhou'
},
{
'name': '龙岩',
'code': 'longyan'
},
{
'name': '南平',
'code': 'nanping'
},
{
'name': '宁德',
'code': 'ningde'
},
{
'name': '莆田',
'code': 'putian'
},
{
'name': '泉州',
'code': 'quanzhou'
},
{
'name': '三明',
'code': 'sanming'
},
{
'name': '漳州',
'code': 'zhangzhou'
},
{
'name': '济南',
'code': 'jinan'
},
{
'name': '滨州',
'code': 'binzhou'
},
{
'name': '德州',
'code': 'dezhou'
},
{
'name': '东营',
'code': 'dongying'
},
{
'name': '菏泽',
'code': 'heze'
},
{
'name': '济宁',
'code': 'jining'
},
{
'name': '聊城',
'code': 'liaocheng'
},
{
'name': '临沂',
'code': 'linyi'
},
{
'name': '青岛',
'code': 'qingdao'
},
{
'name': '日照',
'code': 'rizhao'
},
{
'name': '泰安',
'code': 'taian'
},
{
'name': '威海',
'code': 'weihai'
},
{
'name': '潍坊',
'code': 'weifang'
},
{
'name': '烟台',
'code': 'yantai'
},
{
'name': '枣庄',
'code': 'zaozhuang'
},
{
'name': '淄博',
'code': 'zibo'
},
{
'name': '南昌',
'code': 'nanchang'
},
{
'name': '抚州',
'code': 'fz'
},
{
'name': '赣州',
'code': 'ganzhou'
},
{
'name': '吉安',
'code': 'jian'
},
{
'name': '景德镇',
'code': 'jingdezhen'
},
{
'name': '九江',
'code': 'jiujiang'
},
{
'name': '萍乡',
'code': 'pingxiang'
},
{
'name': '上饶',
'code': 'shangrao'
},
{
'name': '新余',
'code': 'xinyu'
},
{
'name': '宜春',
'code': 'yc'
},
{
'name': '鹰潭',
'code': 'yingtan'
},
{
'name': '合肥',
'code': 'hefei'
},
{
'name': '安庆',
'code': 'anqing'
},
{
'name': '蚌埠',
'code': 'bengbu'
},
{
'name': '池州',
'code': 'chizhou'
},
{
'name': '滁州',
'code': 'chuzhou'
},
{
'name': '阜阳',
'code': 'fuyang'
},
{
'name': '淮北',
'code': 'huaibei'
},
{
'name': '淮南',
'code': 'huainan'
},
{
'name': '黄山',
'code': 'huangshan'
},
{
'name': '六安',
'code': 'luan'
},
{
'name': '马鞍山',
'code': 'maanshan'
},
{
'name': '芜湖',
'code': 'wuhu'
},
{
'name': '宿州',
'code': 'sz'
},
{
'name': '宣城',
'code': 'xuancheng'
},
{
'name': '广州',
'code': 'guangzhou'
},
{
'name': '潮州',
'code': 'chaozhou'
},
{
'name': '佛山',
'code': 'foshan'
},
{
'name': '河源',
'code': 'heyuan'
},
{
'name': '惠州',
'code': 'huizhou'
},
{
'name': '江门',
'code': 'jiangmen'
},
{
'name': '揭阳',
'code': 'jieyang'
},
{
'name': '茂名',
'code': 'maoming'
},
{
'name': '梅州',
'code': 'meizhou'
},
{
'name': '清远',
'code': 'qingyuan'
},
{
'name': '汕头',
'code': 'shantou'
},
{
'name': '汕尾',
'code': 'shanwei'
},
{
'name': '韶关',
'code': 'shaoguan'
},
{
'name': '阳江',
'code': 'yangjiang'
},
{
'name': '云浮',
'code': 'yunfu'
},
{
'name': '湛江',
'code': 'zhanjiang'
},
{
'name': '肇庆',
'code': 'zhaoqing'
},
{
'name': '海口',
'code': 'haikou'
},
{
'name': '南宁',
'code': 'nanning'
},
{
'name': '百色',
'code': 'bose'
},
{
'name': '北海',
'code': 'beihai'
},
{
'name': '崇左',
'code': 'chongzuo'
},
{
'name': '防城港',
'code': 'fangchenggang'
},
{
'name': '贵港',
'code': 'guigang'
},
{
'name': '桂林',
'code': 'guilin'
},
{
'name': '河池',
'code': 'hechi'
},
{
'name': '贺州',
'code': 'hezhou'
},
{
'name': '来宾',
'code': 'laibin'
},
{
'name': '柳州',
'code': 'liuzhou'
},
{
'name': '钦州',
'code': 'qinzhou'
},
{
'name': '梧州',
'code': 'wuzhou'
},
{
'name': '玉林',
'code': 'yl'
},
{
'name': '恩施',
'code': 'enshi'
},
{
'name': '黄冈',
'code': 'huanggang'
},
{
'name': '黄石',
'code': 'huangshi'
},
{
'name': '荆门',
'code': 'jingmen'
},
{
'name': '荆州',
'code': 'jingzhou'
},
{
'name': '十堰',
'code': 'shiyan'
},
{
'name': '随州',
'code': 'suizhou'
},
{
'name': '咸宁',
'code': 'xianning'
},
{
'name': '襄阳',
'code': 'xiangfan'
},
{
'name': '孝感',
'code': 'xiaogan'
},
{
'name': '宜昌',
'code': 'yichang'
},
{
'name': '长沙',
'code': 'changsha'
},
{
'name': '常德',
'code': 'changde'
},
{
'name': '郴州',
'code': 'chenzhou'
},
{
'name': '衡阳',
'code': 'hengyang'
},
{
'name': '怀化',
'code': 'huaihua'
},
{
'name': '娄底',
'code': 'loudi'
},
{
'name': '邵阳',
'code': 'shaoyang'
},
{
'name': '湘潭',
'code': 'xiangtan'
},
{
'name': '湘西',
'code': 'xiangxi'
},
{
'name': '益阳',
'code': 'yiyang'
},
{
'name': '永州',
'code': 'yongzhou'
},
{
'name': '岳阳',
'code': 'yueyang'
},
{
'name': '张家界',
'code': 'zhangjiajie'
},
{
'name': '株洲',
'code': 'zhuzhou'
},
{
'name': '郑州',
'code': 'zhengzhou'
},
{
'name': '安阳',
'code': 'anyang'
},
{
'name': '鹤壁',
'code': 'hebi'
},
{
'name': '焦作',
'code': 'jiaozuo'
},
{
'name': '开封',
'code': 'kaifeng'
},
{
'name': '洛阳',
'code': 'luoyang'
},
{
'name': '漯河',
'code': 'luohe'
},
{
'name': '南阳',
'code': 'nanyang'
},
{
'name': '平顶山',
'code': 'pingdingshan'
},
{
'name': '濮阳',
'code': 'puyang'
},
{
'name': '三门峡',
'code': 'sanmenxia'
},
{
'name': '商丘',
'code': 'shangqiu'
},
{
'name': '新乡',
'code': 'xinxiang'
},
{
'name': '信阳',
'code': 'xinyang'
},
{
'name': '许昌',
'code': 'xuchang'
},
{
'name': '周口',
'code': 'zhoukou'
},
{
'name': '驻马店',
'code': 'zhumadian'
},
{
'name': '阿拉善',
'code': 'alashan'
},
{
'name': '巴彦淖尔',
'code': 'bayannaoer'
},
{
'name': '包头',
'code': 'baotou'
},
{
'name': '赤峰',
'code': 'chifeng'
},
{
'name': '鄂尔多斯',
'code': 'eerduosi'
},
{
'name': '呼伦贝尔',
'code': 'hulunbeier'
},
{
'name': '通辽',
'code': 'tongliao'
},
{
'name': '乌兰察布',
'code': 'wulanchabu'
},
{
'name': '锡林郭勒',
'code': 'xilinguole'
},
{
'name': '兴安',
'code': 'xingan'
},
{
'name': '石家庄',
'code': 'shijiazhuang'
},
{
'name': '保定',
'code': 'baoding'
},
{
'name': '沧州',
'code': 'cangzhou'
},
{
'name': '承德',
'code': 'chengde'
},
{
'name': '邯郸',
'code': 'handan'
},
{
'name': '衡水',
'code': 'hengshui'
},
{
'name': '廊坊',
'code': 'langfang'
},
{
'name': '秦皇岛',
'code': 'qinhuangdao'
},
{
'name': '唐山',
'code': 'tangshan'
},
{
'name': '邢台',
'code': 'xingtai'
},
{
'name': '张家口',
'code': 'zhangjiakou'
},
{
'name': '太原',
'code': 'taiyuan'
},
{
'name': '大同',
'code': 'datong'
},
{
'name': '晋城',
'code': 'jincheng'
},
{
'name': '晋中',
'code': 'jinzhong'
},
{
'name': '临汾',
'code': 'linfen'
},
{
'name': '吕梁',
'code': 'lvliang'
},
{
'name': '朔州',
'code': 'shuozhou'
},
{
'name': '忻州',
'code': 'xinzhou'
},
{
'name': '阳泉',
'code': 'yangquan'
},
{
'name': '运城',
'code': 'yuncheng'
},
{
'name': '长治',
'code': 'changzhi'
},
{
'name': '沈阳',
'code': 'shenyang'
},
{
'name': '鞍山',
'code': 'anshan'
},
{
'name': '朝阳',
'code': 'chaoyang'
},
{
'name': '大连',
'code': 'dalian'
},
{
'name': '丹东',
'code': 'dandong'
},
{
'name': '阜新',
'code': 'fuxin'
},
{
'name': '葫芦岛',
'code': 'huludao'
},
{
'name': '锦州',
'code': 'jinzhou'
},
{
'name': '辽阳',
'code': 'liaoyang'
},
{
'name': '盘锦',
'code': 'panjin'
},
{
'name': '铁岭',
'code': 'tieling'
},
{
'name': '营口',
'code': 'yingkou'
},
{
'name': '长春',
'code': 'changchun'
},
{
'name': '白城',
'code': 'baicheng'
},
{
'name': '白山',
'code': 'baishan'
},
{
'name': '吉林',
'code': 'jilin'
},
{
'name': '辽源',
'code': 'liaoyuan'
},
{
'name': '四平',
'code': 'siping'
},
{
'name': '松原',
'code': 'songyuan'
},
{
'name': '通化',
'code': 'tonghua'
},
{
'name': '延边',
'code': 'yanbian'
},
{
'name': '哈尔滨',
'code': 'haerbin'
},
{
'name': '大庆',
'code': 'daqing'
},
{
'name': '大兴安岭',
'code': 'daxinganling'
},
{
'name': '鹤岗',
'code': 'hegang'
},
{
'name': '黑河',
'code': 'heihe'
},
{
'name': '鸡西',
'code': 'jixi'
},
{
'name': '佳木斯',
'code': 'jiamusi'
},
{
'name': '牡丹江',
'code': 'mudanjiang'
},
{
'name': '七台河',
'code': 'qitaihe'
},
{
'name': '齐齐哈尔',
'code': 'qiqihaer'
},
{
'name': '双鸭山',
'code': 'shuangyashan'
},
{
'name': '绥化',
'code': 'suihua'
},
{
'name': '伊春',
'code': 'yichun'
},
{
'name': '成都',
'code': 'chengdu'
},
{
'name': '阿坝',
'code': 'aba'
},
{
'name': '巴中',
'code': 'bazhong'
},
{
'name': '达州',
'code': 'dazhou'
},
{
'name': '德阳',
'code': 'deyang'
},
{
'name': '甘孜',
'code': 'ganzi'
},
{
'name': '广安',
'code': 'guangan'
},
{
'name': '广元',
'code': 'guangyuan'
},
{
'name': '乐山',
'code': 'leshan'
},
{
'name': '凉山',
'code': 'liangshan'
},
{
'name': '泸州',
'code': 'luzhou'
},
{
'name': '眉山',
'code': 'meishan'
},
{
'name': '绵阳',
'code': 'mianyang'
},
{
'name': '南充',
'code': 'nanchong'
},
{
'name': '内江',
'code': 'neijiang'
},
{
'name': '攀枝花',
'code': 'panzhihua'
},
{
'name': '遂宁',
'code': 'suining'
},
{
'name': '雅安',
'code': 'yaan'
},
{
'name': '宜宾',
'code': 'yibin'
},
{
'name': '资阳',
'code': 'ziyang'
},
{
'name': '自贡',
'code': 'zigong'
},
{
'name': '阿里',
'code': 'ali'
},
{
'name': '昌都',
'code': 'changdu'
},
{
'name': '林芝',
'code': 'linzhi'
},
{
'name': '那曲',
'code': 'naqu'
},
{
'name': '日喀则',
'code': 'rikaze'
},
{
'name': '山南',
'code': 'shannan'
},
{
'name': '昆明',
'code': 'kunming'
},
{
'name': '保山',
'code': 'baoshan'
},
{
'name': '楚雄',
'code': 'chuxiong'
},
{
'name': '大理',
'code': 'dali'
},
{
'name': '德宏',
'code': 'dehong'
},
{
'name': '迪庆',
'code': 'diqing'
},
{
'name': '红河',
'code': 'honghe'
},
{
'name': '丽江',
'code': 'lijiang'
},
{
'name': '临沧',
'code': 'lincang'
},
{
'name': '怒江',
'code': 'nujiang'
},
{
'name': '普洱',
'code': 'puer'
},
{
'name': '曲靖',
'code': 'qujing'
},
{
'name': '文山',
'code': 'wenshan'
},
{
'name': '西双版纳',
'code': 'xishuangbanna'
},
{
'name': '玉溪',
'code': 'yuxi'
},
{
'name': '昭通',
'code': 'zhaotong'
},
{
'name': '贵阳',
'code': 'guiyang'
},
{
'name': '安顺',
'code': 'anshun'
},
{
'name': '毕节',
'code': 'bijie'
},
{
'name': '六盘水',
'code': 'liupanshui'
},
{
'name': '黔东南',
'code': 'qiandongnan'
},
{
'name': '黔南',
'code': 'qiannan'
},
{
'name': '黔西南',
'code': 'qianxinan'
},
{
'name': '铜仁',
'code': 'tongren'
},
{
'name': '遵义',
'code': 'zunyi'
},
{
'name': '西安',
'code': 'xian'
},
{
'name': '安康',
'code': 'ankang'
},
{
'name': '宝鸡',
'code': 'baoji'
},
{
'name': '汉中',
'code': 'hanzhong'
},
{
'name': '商洛',
'code': 'shangluo'
},
{
'name': '铜川',
'code': 'tongchuan'
},
{
'name': '渭南',
'code': 'weinan'
},
{
'name': '咸阳',
'code': 'xianyang'
},
{
'name': '延安',
'code': 'yanan'
},
{
'name': '榆林',
'code': 'yulin'
},
{
'name': '乌鲁木齐',
'code': 'wulumuqi'
},
{
'name': '阿克苏',
'code': 'akesu'
},
{
'name': '阿勒泰',
'code': 'aletai'
},
{
'name': '巴音郭楞',
'code': 'bayinguoleng'
},
{
'name': '博尔塔拉',
'code': 'boertala'
},
{
'name': '昌吉',
'code': 'changji'
},
{
'name': '哈密',
'code': 'hami'
},
{
'name': '和田',
'code': 'hetian'
},
{
'name': '克孜勒苏',
'code': 'kezilesu'
},
{
'name': '塔城',
'code': 'tacheng'
},
{
'name': '吐鲁番',
'code': 'tulufan'
},
{
'name': '伊犁',
'code': 'yili'
},
{
'name': '果洛',
'code': 'guoluo'
},
{
'name': '海北',
'code': 'haibei'
},
{
'name': '海东',
'code': 'haidong'
},
{
'name': '海南',
'code': 'hainan'
},
{
'name': '海西',
'code': 'haixi'
},
{
'name': '黄南',
'code': 'huangnan'
},
{
'name': '玉树',
'code': 'yushu'
},
{
'name': '银川',
'code': 'yinchuan'
},
{
'name': '固原',
'code': 'guyuan'
},
{
'name': '石嘴山',
'code': 'shizuishan'
},
{
'name': '吴忠',
'code': 'wuzhong'
},
{
'name': '中卫',
'code': 'zhongwei'
},
{
'name': '白银',
'code': 'baiyin'
},
{
'name': '定西',
'code': 'dingxi'
},
{
'name': '甘南',
'code': 'gannan'
},
{
'name': '金昌',
'code': 'jinchang'
},
{
'name': '酒泉',
'code': 'jiuquan'
},
{
'name': '临夏',
'code': 'linxia'
},
{
'name': '陇南',
'code': 'longnan'
},
{
'name': '平凉',
'code': 'pingliang'
},
{
'name': '庆阳',
'code': 'qingyang'
},
{
'name': '天水',
'code': 'tianshui'
},
{
'name': '武威',
'code': 'wuwei'
},
{
'name': '张掖',
'code': 'zhangye'
},
{
'name': '上海',
'code': 'shanghai'
},
{
'name': '北京',
'code': 'beijing'
},
{
'name': '天津',
'code': 'tianjin'
},
{
'name': '重庆',
'code': 'chongqing'
}
]";
#endregion
#region 插入配置数据
dynamic lstCity = Newtonsoft.Json.JsonConvert.DeserializeObject <dynamic>(cityJsonString);
if (lstCity != null)
{
var lst = new List <BizCrawlerConfiguration>();
foreach (var cityInfo in lstCity)
{
if (cityInfo?.name?.Value != null && cityInfo.code?.Value != null)
{
lst.Add(new BizCrawlerConfiguration()
{
ConfigurationKey = 0,
ConfigurationValue = $"{{'cityname':'{Convert.ToString(cityInfo.name.Value)}','shortcutname':'{Convert.ToString(cityInfo.code.Value)}','pagecount':5}}",
ConfigurationName = ConstConfigurationName.BaiXing,
IsEnabled = true,
});
}
}
string sqlText = "INSERT INTO `housecrawler`.`CrawlerConfigurations` (`ConfigurationName`, `ConfigurationValue`) VALUES (@ConfigurationName, @ConfigurationValue);";
using (IDbConnection dbConnection = new MySqlConnection(""))
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
var result = dbConnection.Execute(sqlText,
lst, transaction: transaction);
transaction.Commit();
}
}
#endregion
}
public bool MakeCampingPayment(int accountId, decimal price)
{
int nreffectedrecords = -1;
//Check if the account balance
if (dh.CurrentBalance(accountId) - price < 0)
{
MessageBox.Show($"Account Id {accountId} does not have enough balance!\n" +
$"More {price - dh.CurrentBalance(accountId)} € is needed.");
}
else
{
//Show a brief balance and recipt info and then ask for confirmation
var result = MessageBox.Show(
$"Current Account Balance {dh.CurrentBalance(accountId)} €.\n" +
$"The fee of camping is {price} € (including 10 € late service fee)\n" +
$"After payment, the remianing balance would be {dh.CurrentBalance(accountId) - price} €\n" +
$"Do you want to proceed this payment?", $"Ask for permission", MessageBoxButtons.YesNo
);
//Create a new record in transaction Table
if (result == DialogResult.Yes)
{
using (MySqlConnection con = new MySqlConnection(cs))
{
con.Open();
MySqlTransaction trans = con.BeginTransaction();
string date = DateTime.Now.ToString("yyyy-MM-dd");
string time = DateTime.Now.ToString("HH:mm:ss");
string query = $"Insert Into transactions (`date`, `time`,`account_id`, `amount`, `current_balance`, `type`) VALUES " +
$"('{date}','{time}', {accountId}, {price}, {dh.CurrentBalance(accountId)-price}, 'camp')";
using (MySqlCommand cmd = new MySqlCommand(query, con, trans))
{
try
{
nreffectedrecords = cmd.ExecuteNonQuery();
if (nreffectedrecords > 0)
{
//Update the reservation status of camp spot
cmd.CommandText = $"Update camp_reservation set is_paid ='yes' where account_id = {accountId}";
nreffectedrecords = cmd.ExecuteNonQuery();
if (nreffectedrecords > 0)
{
cmd.CommandText =
$"Update accounts set currentbal ='{dh.CurrentBalance(accountId) - price}' where account_id = {accountId}";
nreffectedrecords = cmd.ExecuteNonQuery();
}
if (nreffectedrecords > 0)
{
trans.Commit();
return(true);
}
}
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show($"{ex.Message}");
return(false);
}
}
}
}
}
return(false);
}
/// <summary>
/// 执行SQL语句，返回受影响的行数(带事物)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns>受影响的行数</returns>
public int ExecuteTran(string sql, MySqlParameter[] sqlParams, int timeOut, out string errorMsg)
{
int intResult = 0;
MySqlConnection conn = null;
MySqlCommand cmd = new MySqlCommand();
MySqlTransaction sqlTran = null;
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (MySqlParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
conn.Open();
sqlTran = conn.BeginTransaction();
cmd.Transaction = sqlTran;
intResult = cmd.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception ex)
{
sqlTran.Rollback();
errorMsg = ex.ToString();
}
finally
{
if (sqlTran != null)
{
sqlTran.Dispose();
}
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(intResult);
}
public static string End(string id, string USER, string TOKEN)
{
if (!AccessToken.Read(USER, TOKEN))
{
return("登录超时");
}
IDictionary <string, string> fdict = new Dictionary <string, string>();
fdict.Add("football_round_chain_vote_id", id);
DataSet ds = DBHelper.Select("football_round_chain_vote_invite_view", "*", string.Empty, fdict, "and");
if (ds.Tables[0].Rows.Count == 0)
{
return("找不到该记录");
}
string price = ds.Tables[0].Rows[0]["price"].ToString();
string title = ds.Tables[0].Rows[0]["football_round_name"] + " " + ds.Tables[0].Rows[0]["football_round_chain_name"];
string chain_vote_status_id = "2";
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr["vote_sys_user_id"].ToString().Length == 0)
{
return("接龙未完成");
}
if (dr["match_result_id"].ToString().Length == 0)
{
return("仍有比赛未完成");
}
if (!dr["match_result_id"].Equals(dr["vote_match_result_id"]))
{
chain_vote_status_id = "3";
}
}
using (MySqlConnection dbConnection = new MySqlConnection(DBHelper.strConnection))
{
dbConnection.Open();
using (MySqlTransaction trans = dbConnection.BeginTransaction())
{
IDictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("chain_vote_status_id", chain_vote_status_id);
fdict = new Dictionary <string, string>();
fdict.Add("id", id);
DBHelper.Update("football_round_chain_vote", dict, fdict, "and", dbConnection, trans);
if (chain_vote_status_id.Equals("2") && (price.Length > 0))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
dict = new Dictionary <string, string>();
dict.Add("balance", "数字相加+" + price);
fdict = new Dictionary <string, string>();
fdict.Add("id", dr["vote_sys_user_id"].ToString());
DBHelper.Update("sys_user", dict, fdict, "and", dbConnection, trans);
sys_user_balance_change.Insert(dr["vote_sys_user_id"].ToString(), "2", price, title, "football_round_chain_vote", id, dbConnection, trans);
}
}
trans.Commit();
}
}
return(string.Empty);
}
public void DataProcess()
{
string xUser = LoginInfo.UserID;
string xCurrentDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (MySqlConnection myConnection = new MySqlConnection(xDb.conString))
{
myConnection.Open();
MySqlTransaction myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
MySqlCommand myCommand = myConnection.CreateCommand();
int xProductId = Convert.ToInt32(txtItemNo.Text);
string xBatch = txtBatch.Text;
double xMrp = Convert.ToDouble(txtMrp.Text);
string xReason = "SALES RETURN ";
string xExpiryDate = txtExpDate.Text;
string xQrySalesReturn = "insert into sales_return" +
"(sales_id," +
" date,product_id,qty,mrp,batch_id,expiry_date,reason,created_by,created_on) " +
" values(" + txtSalesId.Text + "," +
" '" + DateTime.Now.ToString("yyyy-MM-dd") + "'," +
" '" + xProductId + "'," +
" '" + Convert.ToInt32(txtChangeQty.Text) + "'," +
" '" + xMrp + "'," +
" '" + xBatch + "'," +
" '" + xExpiryDate + "'," +
" '" + xReason + rchRemarks.Text + "'," +
" '" + xUser + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
+ "')";
StockResponseModel stockResponseModel = xDb.GetStockFromQuery("select * from stock where product_id = " + xProductId + " and batch_id = '" + xBatch + "' and mrp = '" + xMrp + "'");
int xStockOldQty = 0;
int xStockNewQty = 0;
xStockOldQty = stockResponseModel.Qty;
int xChangeQty = Convert.ToInt32(txtChangeQty.Text);
xStockNewQty = xStockOldQty + xChangeQty;
string xQryStockUpdateEntry = "update stock set " +
" qty = " + xStockNewQty + ", updated_by = '" + xUser + "', updated_on = '" + xCurrentDateTime + "' " +
" where product_id=" + xProductId + " " +
" and batch_id = '" + xBatch + "' " +
" and mrp = '" + xMrp + "'";
string xQryStockHistory = "insert into stock_history" +
" (product_id,old_qty,change_qty,current_qty," +
" mrp,batch_id,expiry_date,reason,created_by,created_on)" +
" values(" + xProductId + "," + xStockOldQty + "," + xChangeQty + "," + xStockNewQty + "," + xMrp + "," +
" '" + xBatch + "','" + xExpiryDate + "','" + xReason + "'," +
" '" + xUser + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
+ "')";
myCommand.CommandText = xQrySalesReturn;
myCommand.ExecuteNonQuery();
myCommand.CommandText = xQryStockUpdateEntry;
myCommand.ExecuteNonQuery();
myCommand.CommandText = xQryStockHistory;
myCommand.ExecuteNonQuery();
myTrans.Commit();
MessageBox.Show("Sales Item Returned.");
DataClear();
}
}
public Boolean NuevoEmpleado(DataTable dtEmpleado, DataTable dtDireccion, DataTable dtRh)
{
Boolean Valor = false;
int idEmpleado = 0;
MySqlConnection cnObj = new MySqlConnection();
cnObj = objConexion.Conectar();
if (cnObj != null)
{
MySqlTransaction Trans = cnObj.BeginTransaction();
MySqlCommand cmdObj = new MySqlCommand();
cmdObj.Connection = cnObj;
string strSql;
strSql = "SELECT ";
strSql += "MAX(id_empleado + 1) ";
strSql += "FROM ";
strSql += "empleado";
cmdObj.CommandText = strSql;
MySqlDataReader rdrObj = cmdObj.ExecuteReader();
while (rdrObj.Read())
{
idEmpleado = Convert.ToInt32(rdrObj[0].ToString());
}
rdrObj.Close();
foreach (DataRow dRowEmpleado in dtEmpleado.Rows)
{
strSql = "INSERT ";
strSql += "INTO ";
strSql += "empleado ";
strSql += "(clave, ";
strSql += "a_paterno, ";
strSql += "a_materno, ";
strSql += "nombre, ";
strSql += "rfc, ";
strSql += "curp, ";
strSql += "nss, ";
strSql += "fecha_nac, ";
strSql += "alta, ";
strSql += "baja, ";
strSql += "estatus) ";
strSql += "VALUES ";
strSql += "('" + dRowEmpleado["clave"] + "', ";
strSql += "'" + dRowEmpleado["a_paterno"] + "', ";
strSql += "'" + dRowEmpleado["a_materno"] + "', ";
strSql += "'" + dRowEmpleado["nombre"] + "', ";
strSql += "'" + dRowEmpleado["rfc"] + "', ";
strSql += "'" + dRowEmpleado["curp"] + "', ";
strSql += "'" + dRowEmpleado["nss"] + "', ";
strSql += "'" + dRowEmpleado["fecha_nac"] + "', ";
strSql += "'" + dRowEmpleado["alta"] + "', ";
strSql += "'" + dRowEmpleado["baja"] + "', ";
strSql += "" + dRowEmpleado["estatus"] + ")";
cmdObj.CommandText = strSql;
try
{
cmdObj.ExecuteNonQuery();
}catch (MySqlException ex)
{
MessageBox.Show("Error al Ingresar los Datos, " + ex.Message, "Error Critico", MessageBoxButtons.OK, MessageBoxIcon.Error);
Trans.Rollback();
}
foreach (DataRow dRowDireccion in dtDireccion.Rows)
{
strSql = "INSERT ";
strSql += "INTO ";
strSql += "direccion ";
strSql += "(id_empleado, ";
strSql += "calle, ";
strSql += "num_ext, ";
strSql += "num_int, ";
strSql += "colonia, ";
strSql += "cod_postal, ";
strSql += "id_pais, ";
strSql += "id_estado, ";
strSql += "id_ciudad, ";
strSql += "telefono, ";
strSql += "correo, ";
strSql += "celular, ";
strSql += "contacto) ";
strSql += "VALUES ";
strSql += "(" + idEmpleado + ", ";
strSql += "'" + dRowDireccion["calle"] + "', ";
strSql += "'" + dRowDireccion["num_ext"] + "', ";
strSql += "'" + dRowDireccion["num_int"] + "', ";
strSql += "'" + dRowDireccion["colonia"] + "', ";
strSql += "'" + dRowDireccion["cod_postal"] + "', ";
strSql += "" + dRowDireccion["id_pais"] + ", ";
strSql += "" + dRowDireccion["id_estado"] + ", ";
strSql += "" + dRowDireccion["id_ciudad"] + ", ";
strSql += "'" + dRowDireccion["telefono"] + "', ";
strSql += "'" + dRowDireccion["correo"] + "', ";
strSql += "'" + dRowDireccion["celular"] + "', ";
strSql += "'" + dRowDireccion["contacto"] + "')";
cmdObj.CommandText = strSql;
try
{
cmdObj.ExecuteNonQuery();
}catch (MySqlException ex)
{
MessageBox.Show("Error al Ingresar los Datos, " + ex.Message, "Error Critico", MessageBoxButtons.OK, MessageBoxIcon.Error);
Trans.Rollback();
}
foreach (DataRow dRowRh in dtRh.Rows)
{
strSql = "INSERT ";
strSql += "INTO ";
strSql += "datos_rh ";
strSql += "(id_empleado, ";
strSql += "id_banco, ";
strSql += "cuenta_banco, ";
strSql += "id_jornada, ";
strSql += "id_sucursal, ";
strSql += "id_departamento, ";
strSql += "id_puesto, ";
strSql += "salario_diario, ";
strSql += "id_metodo_pago, ";
strSql += "id_contrato, ";
strSql += "id_tipo_salario) ";
strSql += "VALUES ";
strSql += "(" + idEmpleado + ", ";
strSql += "" + dRowRh["id_banco"] + ", ";
strSql += "'" + dRowRh["cuenta_banco"] + "', ";
strSql += "" + dRowRh["id_jornada"] + ", ";
strSql += "" + dRowRh["id_sucursal"] + ", ";
strSql += "" + dRowRh["id_departamento"] + ", ";
strSql += "" + dRowRh["id_puesto"] + ", ";
strSql += "'" + dRowRh["salario_diario"] + "', ";
strSql += "" + dRowRh["id_metodo_pago"] + ", ";
strSql += "" + dRowRh["id_contrato"] + ", ";
strSql += "" + dRowRh["id_tipo_salario"] + ")";
cmdObj.CommandText = strSql;
try
{
cmdObj.ExecuteNonQuery();
}catch (MySqlException ex)
{
MessageBox.Show("Error al Ingresar los Datos, " + ex.Message, "Error Critico", MessageBoxButtons.OK, MessageBoxIcon.Error);
Trans.Rollback();
}
}
}
Trans.Commit();
}
}
cnObj.Close();
return(Valor);
}
public void iniciarTransacao()
{
transacao = conexao.BeginTransaction(); //'indico o inicio da transacao'
}
public override void InsertIntoDbWithParameters(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt)
{
try
{
using (MySqlConnection sqlcon = new MySqlConnection(ConnectionString))
{
if (sqlcon.State != ConnectionState.Open)
{
sqlcon.Open();
}
string strQuery = BuildInsertStatementWithParameters(schmapper, dt);
using (MySqlTransaction trans = sqlcon.BeginTransaction())
{
using (MySqlCommand sqlcmd = new MySqlCommand(strQuery, sqlcon, trans))
{
sqlcmd.CommandType = CommandType.Text;
foreach (var Col in schmapper.Columns)
{
switch (Col.DataType)
{
case SchemaMapper_Column.ColumnDataType.Date:
sqlcmd.Parameters.Add("@" + Col.Name, MySqlDbType.DateTime);
break;
case SchemaMapper_Column.ColumnDataType.Text:
sqlcmd.Parameters.Add("@" + Col.Name, MySqlDbType.VarChar);
break;
case SchemaMapper_Column.ColumnDataType.Memo:
sqlcmd.Parameters.Add("@" + Col.Name, MySqlDbType.VarChar, 4000);
break;
case SchemaMapper_Column.ColumnDataType.Number:
sqlcmd.Parameters.Add("@" + Col.Name, MySqlDbType.Int64);
break;
}
}
foreach (DataRow drrow in dt.Rows)
{
foreach (var Col in schmapper.Columns)
{
sqlcmd.Parameters["@" + Col.Name].Value = drrow[Col.Name];
}
sqlcmd.ExecuteNonQuery();
}
trans.Commit();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool insertBook(BookDTO bookDTO)
{
MySqlConnection mySqlConnection = new MySqlConnection(dbInfo);
mySqlConnection.Open();
MySqlTransaction trans = mySqlConnection.BeginTransaction();
string b_name = bookDTO.B_name;
string b_author = bookDTO.B_author;
string b_publisher = bookDTO.B_puBlisher;
string b_category = bookDTO.B_category;
string b_summery = bookDTO.B_summary;
string b_img = bookDTO.B_img;
int b_stock = bookDTO.B_stock;
string b_guest = "0";
if (bookDTO.B_guest == "전체 이용")
{
b_guest = "0";
}
else if (bookDTO.B_guest == "12세 이용")
{
b_guest = "12";
}
else if (bookDTO.B_guest == "15세 이용")
{
b_guest = "15";
}
else if (bookDTO.B_guest == "성인")
{
b_guest = "19";
}
try
{
string sql = "insert into book values (NULL,'" + b_name + "'," + b_stock + ",'" + b_author + "','" + b_summery + "','" + b_img + "','" + b_guest + "',now())";
MySqlCommand mysqlCommand = new MySqlCommand(sql, mySqlConnection);
mysqlCommand.ExecuteNonQuery();
int categoryNum = resultCategory(b_category);
int publisherNum = resultPublisher(b_publisher);
sql = "insert into category values (" + categoryNum + ",NULL)";
mysqlCommand = new MySqlCommand(sql, mySqlConnection);
mysqlCommand.ExecuteNonQuery();
sql = "insert into publisher values (" + publisherNum + ",NULL)";
mysqlCommand = new MySqlCommand(sql, mySqlConnection);
mysqlCommand.ExecuteNonQuery();
trans.Commit();
mySqlConnection.Close();
}
catch (Exception e)
{
trans.Rollback();
MessageBox.Show(e.Message);
}
return(true);
}
public override void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
ts = privateconn.BeginTransaction(isolationLevel);
}
void ExecuteScripts(string scriptDirectory, string tablePrefix)
{
#region ExecuteScriptsSqlServer
using (var connection = new SqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var tablePrefixParameter = command.CreateParameter();
tablePrefixParameter.ParameterName = "tablePrefix";
tablePrefixParameter.Value = tablePrefix;
command.Parameters.Add(tablePrefixParameter);
var schemaParameter = command.CreateParameter();
schemaParameter.ParameterName = "schema";
schemaParameter.Value = "dbo";
command.Parameters.Add(schemaParameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
#region ExecuteScriptsMySql
using (var connection = new MySqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
#region ExecuteScriptsPostgreSql
using (var connection = new NpgsqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
#region ExecuteScriptsOracle
using (var connection = new OracleConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
}
public BankTransferEventArgs TransferBetween(IBankAccount FromAccount, IBankAccount ToAccount, Money Amount, BankAccountTransferOptions Options, string TransactionMessage, string JournalMessage)
{
long accountCount = -1;
PendingTransactionEventArgs pendingTransaction = new PendingTransactionEventArgs(FromAccount, ToAccount, Amount, Options, TransactionMessage, JournalMessage);
ITransaction sourceTran, destTran;
MySqlConnection conn = null;
MySqlTransaction sqlTrans = null;
BankTransferEventArgs args = new BankTransferEventArgs()
{
TransferSucceeded = false
};
string accountVerifyQuery = @"select count(*)
from `bank_account`
where `bank_account_id` = @0;" ;
Stopwatch sw = new Stopwatch();
if (SEconomyInstance.Configuration.EnableProfiler == true)
{
sw.Start();
}
if (ToAccount == null ||
TransferMaySucceed(FromAccount, ToAccount, Amount, Options) == false)
{
return(args);
}
if ((conn = Connection) == null)
{
TShock.Log.ConsoleError("[SEconomy MySQL] Cannot connect to the SQL server");
return(args);
}
conn.Open();
if ((accountCount = Connection.QueryScalar <long>(accountVerifyQuery, FromAccount.BankAccountK)) != 1)
{
TShock.Log.ConsoleError("[SEconomy MySQL] Source account " + FromAccount.BankAccountK + " does not exist.");
conn.Dispose();
return(args);
}
if ((accountCount = Connection.QueryScalar <long>(accountVerifyQuery, ToAccount.BankAccountK)) != 1)
{
TShock.Log.ConsoleError("[SEconomy MySQL] Source account " + FromAccount.BankAccountK + " does not exist.");
conn.Dispose();
return(args);
}
if (BankTransactionPending != null)
{
BankTransactionPending(this, pendingTransaction);
}
if (pendingTransaction == null ||
pendingTransaction.IsCancelled == true)
{
return(args);
}
args.Amount = pendingTransaction.Amount;
args.SenderAccount = pendingTransaction.FromAccount;
args.ReceiverAccount = pendingTransaction.ToAccount;
args.TransferOptions = Options;
args.TransactionMessage = pendingTransaction.TransactionMessage;
try {
sqlTrans = conn.BeginTransaction();
if ((sourceTran = BeginSourceTransaction(sqlTrans, FromAccount.BankAccountK, pendingTransaction.Amount, pendingTransaction.JournalLogMessage)) == null)
{
throw new Exception("BeginSourceTransaction failed");
}
if ((destTran = FinishEndTransaction(sqlTrans, ToAccount, pendingTransaction.Amount, pendingTransaction.JournalLogMessage)) == null)
{
throw new Exception("FinishEndTransaction failed");
}
BindTransactions(sqlTrans, sourceTran.BankAccountTransactionK, destTran.BankAccountTransactionK);
sqlTrans.Commit();
} catch (Exception ex) {
if (conn != null &&
conn.State == ConnectionState.Open)
{
try {
sqlTrans.Rollback();
} catch {
TShock.Log.ConsoleError("[SEconomy MySQL] Error in rollback:" + ex.ToString());
}
}
TShock.Log.ConsoleError("[SEconomy MySQL] Database error in transfer:" + ex.ToString());
args.Exception = ex;
return(args);
} finally {
if (conn != null)
{
conn.Dispose();
}
}
FromAccount.SyncBalance();
ToAccount.SyncBalance();
args.TransferSucceeded = true;
if (BankTransferCompleted != null)
{
BankTransferCompleted(this, args);
}
if (SEconomyInstance.Configuration.EnableProfiler == true)
{
sw.Stop();
TShock.Log.ConsoleInfo("[SEconomy MySQL] Transfer took {0} ms", sw.ElapsedMilliseconds);
}
return(args);
}
public bool removeUser(string pId, string pUserId)
{
bool _success = false;
using (MySqlConnection _conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
{
_conn.Open();
MySqlTransaction _trans = _conn.BeginTransaction();
MySqlCommand _cmd = new MySqlCommand("call spRemoveUser('" + pId +
"','" + pUserId + "');", _conn);
try
{
_cmd.Transaction = _trans;
int _rowsAffected = _cmd.ExecuteNonQuery();
_trans.Commit();
_conn.Close();
if (_rowsAffected > 0)
{
_success = true;
}
else
{
_success = false;
}
}
catch
{
_trans.Rollback();
_success = false;
}
}
return(_success);
#region "MySql Server Code"
/*bool _success = false;
* using (MySqlConnection _conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
* {
* _conn.Open();
* MySqlTransaction _trans = _conn.BeginTransaction();
* MySqlCommand _cmd = new MySqlCommand("exec spRemoveUser @Username=N'" + pUsername +
* "', @UsernameRemove=N'" + pUsernameRemove + "'", _conn);
* try
* {
* _cmd.Transaction = _trans;
* int _rowsAffected = _cmd.ExecuteNonQuery();
* _trans.Commit();
* _conn.Close();
* if (_rowsAffected > 0)
* {
* _success = true;
* }
* else
* {
* _success = false;
* }
* }
* catch
* {
* _trans.Rollback();
* _success = false;
* }
* }
* return _success;*/
#endregion
}
public ActionResult Register()
{
//Already logged in -> no need to register
if (!String.IsNullOrEmpty(Session["user"] as string) || !String.IsNullOrEmpty(Session["role"] as string))
{
return(RedirectToAction("Index"));
}
bool isPost = Request.HttpMethod == "POST";
if (isPost)
{
if (Request["password"] == null || Request["password"] != Request["passwordRepeat"])
{
ModelState.AddModelError("Error", "Die Passwörter stimmen nicht über ein!");
return(View());
}
string result = PasswordStorage.CreateHash(Request["password"]);
var results = result.Split(':');
string hash = results.Last();
string salt = results[results.Length - 2];
bool loggedIn = false;
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
MySqlTransaction tr = null;
try
{
con.Open();
// innerhalb der Connection con eine Transaktion beginnen
tr = con.BeginTransaction();
using (MySqlCommand cmd = new MySqlCommand()
{
Connection = con, Transaction = tr
})
{
cmd.CommandText =
"INSERT INTO Benutzer(`E-Mail`, Nutzername, Geburtsdatum, Anlegedatum, Aktiv, Vorname, Nachname, Salt, Hash) " +
"VALUES(@mail, @name, @date, CURDATE(), 0, @firstName, @lastName, @salt, @hash)";
cmd.Parameters.AddWithValue("mail", Request["mail"]);
cmd.Parameters.AddWithValue("name", Request["name"]);
cmd.Parameters.AddWithValue("date", !String.IsNullOrEmpty(Request["birthdate"]) ? Request["birthdate"] : "null");
cmd.Parameters.AddWithValue("firstName", Request["firstName"]);
cmd.Parameters.AddWithValue("lastName", Request["lastName"]);
cmd.Parameters.AddWithValue("salt", salt);
cmd.Parameters.AddWithValue("hash", hash);
var rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("id", cmd.LastInsertedId);
if (Request["role"] == "Mitarbeiter" || Request["role"] == "Student")
{
cmd.CommandText = "INSERT INTO `FH Angehörige`(Nummer) VALUES(@id)";
cmd.ExecuteNonQuery();
}
switch (Request["role"])
{
case "Gast":
if (!String.IsNullOrEmpty(Request["expireDate"]))
{
cmd.CommandText =
"INSERT INTO Gäste(Nummer, Ablaufdatum, Grund) VALUES(@id, @date, @reason)";
cmd.Parameters.AddWithValue("date", Request["expireDate"]);
}
else
{
cmd.CommandText = "INSERT INTO Gäste(Nummer, Grund) VALUES(@id, @reason)";
}
cmd.Parameters.AddWithValue("reason", !String.IsNullOrEmpty(Request["reason"]) ? Request["reason"] : "null");
break;
case "Mitarbeiter":
cmd.CommandText = "INSERT INTO Mitarbeiter(Nummer, Telefon, Büro) VALUES(@id, @phone, @office)";
cmd.Parameters.AddWithValue("phone", !String.IsNullOrEmpty(Request["phone"]) ? Request["phone"] : "null");
cmd.Parameters.AddWithValue("office", !String.IsNullOrEmpty(Request["office"]) ? Request["office"] : "null");
break;
case "Student":
cmd.CommandText = "INSERT INTO Studenten(Nummer, Matrikelnummer, Studiengang) VALUES(@id, @matriculationNumber, @degree)";
cmd.Parameters.AddWithValue("matriculationNumber", Request["matriculationNumber"]);
cmd.Parameters.AddWithValue("degree", Request["degree"]);
break;
}
rows = cmd.ExecuteNonQuery();
tr.Commit();
}
con.Close();
loggedIn = true;
}
catch (Exception e)
{
tr?.Rollback();
con.Close();
ModelState.AddModelError("Error", e.Message);
return(View());
}
}
if (loggedIn)
{
return(RedirectToAction("Index"));
}
}
return(View());
}
/// <summary>
/// 执行MySql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
//引发事件
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
static public bool book_dates(List <DateTime> dates, string bid, string roID)
{
MySqlConnection conn = new MySqlConnection(Config.connString);
MySqlCommand command = conn.CreateCommand();
MySqlDataReader reader;
string count, blid;
//command.CommandText = query;
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false); // Error
}
MySqlTransaction trans = conn.BeginTransaction();
// Insert booking_line
try
{
command.CommandText = string.Format("insert into booking_lines values (NULL, {0}, \'{1}\', \'{2}\');select last_insert_id();", bid, dates.First().ToString("yyyy-MM-dd"), dates.Last().AddDays(1).ToString("yyyy-MM-dd"));
reader = command.ExecuteReader();
reader.Read();
blid = reader[0].ToString();
reader.Close();
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show(ex.Message);
conn.Close();
return(false); // Error
}
// insert booking_entries
foreach (var date in dates)
{
try
{
// Set database
command.CommandText = string.Format("insert into booking_entries values (NULL, {0}, {1}, \'{2}\');", blid, roID, date.ToString("yyyy-MM-dd"));
command.ExecuteNonQuery();
// Get validation count
command.CommandText = string.Format("select count(roID) - (select count(roID) from booking_entries natural join rent_object_types where roID = {0} and Date = \'{1}\') from rent_objects natural join rent_object_types where roID = {0};", roID, date.ToString("yyyy-MM-dd"));
reader = command.ExecuteReader();
reader.Read();
count = reader[0].ToString();
reader.Close();
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show(ex.Message);
conn.Close();
return(false); // Error
}
// Validate insert
if (Int32.Parse(count) < 0)
{
trans.Rollback();
conn.Close();
return(false); // Error
}
}
trans.Commit();
conn.Close();
return(true); // Success
}
/// <summary>
/// Initializes a new instance of the <see cref="T:p5.mysql.Transaction"/> class.
/// </summary>
public Transaction(ApplicationContext context, Node args)
{
_connection = Connection.Active(context, args);
_transaction = _connection.BeginTransaction();
}
public Client Register(RegisterModel model)
{
Client client = null;
int newId;
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select email from clients", conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader["email"].ToString() == model.Email)
{
return(null);
}
}
}
using (var transaction = conn.BeginTransaction())
{
var insertCommand = conn.CreateCommand();
insertCommand.CommandText = "call register_client(@lastname, @firstname, @patronymic," +
"@country, @city, @email, @password, @phone_number);";
insertCommand.Parameters.AddWithValue("@email", model.Email);
insertCommand.Parameters.AddWithValue("@password", model.Password);
insertCommand.Parameters.AddWithValue("@lastname", model.Surname);
insertCommand.Parameters.AddWithValue("@firstname", model.Name);
insertCommand.Parameters.AddWithValue("@patronymic", model.Patronymic);
insertCommand.Parameters.AddWithValue("@country", model.Country);
insertCommand.Parameters.AddWithValue("@city", model.City);
insertCommand.Parameters.AddWithValue("@phone_number", model.PhoneNumber);
insertCommand.CommandText += "select LAST_INSERT_ID();";
newId = Convert.ToInt32(insertCommand.ExecuteScalar());
transaction.Commit();
}
MySqlCommand command = new MySqlCommand("select * from clients where id = (@newId);", conn);
command.Parameters.AddWithValue("@newId", newId.ToString());
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
client = new Client()
{
Id = Convert.ToInt32(reader["id"]),
FirstNameId = Convert.ToInt32(reader["id_name2"]),
LastNameId = Convert.ToInt32(reader["id_name1"]),
PatronymicId = Convert.ToInt32(reader["id_name3"] != DBNull.Value ? reader["id_name3"] : null),
PhoneNumber = reader["phonenumber"].ToString(),
AddressId = Convert.ToInt32(reader["id_address"] != DBNull.Value ? reader["id_address"] : null),
PassportId = Convert.ToInt32(reader["id_passport"] != DBNull.Value ? reader["id_passport"] : null),
Email = reader["email"].ToString(),
Role = reader["role"].ToString(),
Money = Convert.ToInt32(reader["id_address"] != DBNull.Value ? reader["id_address"] : 0),
BlockingId = null
};
}
}
}
return(client);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
public static int ExecuteSqlTran(System.Collections.Generic.List <CommandInfo> cmdList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
MySqlCommand cmd = new MySqlCommand();
try
{
int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
continue;
}
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
}
trans.Commit();
return(count);
}
catch
{
trans.Rollback();
throw;
}
}
}
}
public void StartTransaction()
{
currentTransaction = connection.BeginTransaction();
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.users model)
{
int newId;
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "users(");
strSql.Append("group_id,user_name,salt,password,mobile,email,avatar,nick_name,sex,birthday,telphone,area,address,qq,msn,amount,point,exp,status,reg_time,reg_ip)");
strSql.Append(" values (");
strSql.Append("@group_id,@user_name,@salt,@password,@mobile,@email,@avatar,@nick_name,@sex,@birthday,@telphone,@area,@address,@qq,@msn,@amount,@point,@exp,@status,@reg_time,@reg_ip)");
MySqlParameter[] parameters =
{
new MySqlParameter("@group_id", MySqlDbType.Int32, 4),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@salt", MySqlDbType.VarChar, 20),
new MySqlParameter("@password", MySqlDbType.VarChar, 100),
new MySqlParameter("@mobile", MySqlDbType.VarChar, 20),
new MySqlParameter("@email", MySqlDbType.VarChar, 50),
new MySqlParameter("@avatar", MySqlDbType.VarChar, 255),
new MySqlParameter("@nick_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@sex", MySqlDbType.VarChar, 20),
new MySqlParameter("@birthday", MySqlDbType.Date),
new MySqlParameter("@telphone", MySqlDbType.VarChar, 50),
new MySqlParameter("@area", MySqlDbType.VarChar, 255),
new MySqlParameter("@address", MySqlDbType.VarChar, 255),
new MySqlParameter("@qq", MySqlDbType.VarChar, 20),
new MySqlParameter("@msn", MySqlDbType.VarChar, 100),
new MySqlParameter("@amount", MySqlDbType.Decimal, 5),
new MySqlParameter("@point", MySqlDbType.Int32, 4),
new MySqlParameter("@exp", MySqlDbType.Int32, 4),
new MySqlParameter("@status", MySqlDbType.Int32, 4),
new MySqlParameter("@reg_time", MySqlDbType.Date),
new MySqlParameter("@reg_ip", MySqlDbType.VarChar, 20)
};
parameters[0].Value = model.group_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.salt;
parameters[3].Value = model.password;
parameters[4].Value = model.mobile;
parameters[5].Value = model.email;
parameters[6].Value = model.avatar;
parameters[7].Value = model.nick_name;
parameters[8].Value = model.sex;
if (model.birthday != null)
{
parameters[9].Value = model.birthday;
}
else
{
parameters[9].Value = DBNull.Value;
}
parameters[10].Value = model.telphone;
parameters[11].Value = model.area;
parameters[12].Value = model.address;
parameters[13].Value = model.qq;
parameters[14].Value = model.msn;
parameters[15].Value = model.amount;
parameters[16].Value = model.point;
parameters[17].Value = model.exp;
parameters[18].Value = model.status;
parameters[19].Value = model.reg_time;
parameters[20].Value = model.reg_ip;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
public bool Insertar(BE_ALM_MOVIMIENTOS entCabecera, List <BE_ALM_MOVIMIENTOSDET> lstDetalle, List <BE_ALM_INVENTARIOLOTE> lstLote)
{
bool booOk = false;
int intFila = 0;
DatosMySql xMiFuncion = new DatosMySql();
MySqlTransaction trans;
xMiFuncion.ReAbrirConeccion(mysConec);
trans = mysConec.BeginTransaction();
try
{
if (xMiFuncion.StoreEjecutar("alm_movimientos_insertar", entCabecera, mysConec, 0) == true)
{
for (intFila = 0; intFila <= lstDetalle.Count - 1; intFila++)
{
entCabecera.n_id = Convert.ToInt32(xMiFuncion.intIdGenerado);
lstDetalle[intFila].n_idmov = Convert.ToInt32(xMiFuncion.intIdGenerado);
if (xMiFuncion.StoreEjecutar("alm_movimientosdet_insertar", lstDetalle[intFila], mysConec, null) == true)
{
booOk = true;
}
else
{
// CONTROLAR EL ERROR
booOcurrioError = xMiFuncion.booOcurrioError;
StrErrorMensaje = xMiFuncion.StrErrorMensaje;
IntErrorNumber = xMiFuncion.IntErrorNumber;
trans.Rollback();
booOk = false;
return(booOk);
}
// AGREGAMOS LOS LOTES
lstLote[intFila].n_iddocmov = Convert.ToInt32(xMiFuncion.intIdGenerado);
if (xMiFuncion.StoreEjecutar("alm_inventariolotes_insertar", lstLote[intFila], mysConec, null) == true)
{
booOk = true;
}
else
{
// CONTROLAR EL ERROR
booOcurrioError = xMiFuncion.booOcurrioError;
StrErrorMensaje = xMiFuncion.StrErrorMensaje;
IntErrorNumber = xMiFuncion.IntErrorNumber;
trans.Rollback();
booOk = false;
return(booOk);
}
}
}
else
{
booOcurrioError = xMiFuncion.booOcurrioError;
StrErrorMensaje = xMiFuncion.StrErrorMensaje;
IntErrorNumber = xMiFuncion.IntErrorNumber;
trans.Rollback();
booOk = false;
return(booOk);
}
if (booOk == true)
{
trans.Commit();
}
else
{
trans.Rollback();
}
return(booOk);
}
catch (Exception exc)
{
// SI SUCEDE UN ERROR DEVOLVEMOS FALSO
booOcurrioError = xMiFuncion.booOcurrioError;
StrErrorMensaje = xMiFuncion.StrErrorMensaje;
IntErrorNumber = xMiFuncion.IntErrorNumber;
trans.Rollback();
return(booOk);
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的 Object[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
MySqlCommand cmd = new MySqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
Object[] cmdParms = (Object[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
}
//установка сложности
public static bool SetСomplexity(string field_value, Ticket ticket, string logPath, string connectionString, string operationType)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
long count = 0;//счетчик количества записей в таблице
connection.Open();
//проверяем наличие записи в базе
string queryString = $@"select count(*) count
from otrs.dynamic_field_value d
where d.object_id = @object_id and d.field_id = 3;";
MySqlCommand cmd = new MySqlCommand(queryString, connection);
cmd.Parameters.AddWithValue("object_id", ticket.Id);
count = (long)cmd.ExecuteScalar();
//если записи нет, то добавляем запись
if (count == 0 && operationType == "INSERT")
{
string queryString1 = $@"INSERT INTO otrs.dynamic_field_value
set field_id = 3,
object_id = @object_id,
value_text = @value_text;";
MySqlCommand cmd1 = new MySqlCommand(queryString1, connection);
cmd1.Parameters.AddWithValue("object_id", ticket.Id);
cmd1.Parameters.AddWithValue("value_text", field_value);
try
{
cmd1.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
cmd1.ExecuteNonQuery();
cmd1.Transaction.Commit();
cmd1.Transaction.Dispose();
}
catch (Exception e)
{
cmd1.Transaction.Rollback();
cmd1.Transaction.Dispose();
throw new Exception("Ошибка записи значения сложности в базу данных для ticket_id = " + ticket.Id + Environment.NewLine + e.Message + e.StackTrace);
}
//MySqlDataReader reader1 = query1.ExecuteReader();
//reader1.Close();
string message = "Установлена " + GetParamValue(field_value) + " сложность для заявки " + ticket.Id + " Тема: " + ticket.Title;
Console.WriteLine(message);
WriteToLog(logPath, message);
return(true);
}
//если запись есть, то обновляем значение сложности
else if (count == 1 && operationType == "UPDATE")
{
string queryString2 = $@"UPDATE otrs.dynamic_field_value d
set value_text = @value_text
where d.object_id = @object_id
and d.field_id = 3;";
MySqlCommand cmd2 = new MySqlCommand(queryString2, connection);
cmd2.Parameters.AddWithValue("object_id", ticket.Id);
cmd2.Parameters.AddWithValue("value_text", field_value);
try
{
cmd2.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
cmd2.ExecuteNonQuery();
cmd2.Transaction.Commit();
cmd2.Transaction.Dispose();
}
catch (Exception e)
{
cmd2.Transaction.Rollback();
cmd2.Transaction.Dispose();
throw new Exception("Ошибка обновления значения сложности в базе данных для ticket_id = " + ticket.Id + Environment.NewLine + e.Message + e.StackTrace);
}
//MySqlDataReader reader2 = cmd2.ExecuteReader();
//reader2.Close();
string message = "Изменена сложность на " + GetParamValue(field_value) + " для заявки " + ticket.Id + " Тема: " + ticket.Title;
Console.WriteLine(message);
WriteToLog(logPath, message);
return(true);
}
//если записей больше одной, то записываем ошибку в лог
else if (count > 1)
{
string message = "В таблице otrs.dynamic_field_value обнаружены дублирующиеся строки с object_id = " + ticket.Id + " и field_id = " + Сomplexity.Id;
Console.WriteLine(message);
WriteToLog(logPath, message);
}
}
return(false);
}
public void CommitDoesNotTimeout()
{
const int requiredNumberOfRuns = 1;
const int binarySize = 5000000;
const int requiredNumberOfRowsPerRun = 100;
Debug.WriteLine("Required Number Of Runs :" + requiredNumberOfRuns);
Debug.WriteLine("Required Number Of Rows Per Run :" + requiredNumberOfRowsPerRun);
suExecSQL("SET GLOBAL max_allowed_packet=64000000");
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
using (MySqlConnection connection = new MySqlConnection(GetConnectionString(true)))
{
connection.Open();
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "DROP TABLE IF EXISTS test_timeout;";
command.ExecuteNonQuery();
StringBuilder sqlCommand = new StringBuilder(512);
sqlCommand.Append("CREATE TABLE test_timeout (");
sqlCommand.Append("identity INT NOT NULL auto_increment, ");
sqlCommand.Append("a INT NOT NULL, ");
sqlCommand.Append("b INT NOT NULL, ");
sqlCommand.Append("c INT NOT NULL, ");
sqlCommand.Append("binary_data LONGBLOB NOT NULL, ");
sqlCommand.Append("PRIMARY KEY(identity), ");
sqlCommand.Append("KEY `abc` (`a`,`b`, `c`) ");
sqlCommand.Append(") ENGINE = INNODB");
command.CommandText = sqlCommand.ToString();
command.ExecuteNonQuery();
}
for (int numberOfRuns = 0; numberOfRuns < requiredNumberOfRuns; ++numberOfRuns)
{
using (MySqlTransaction transaction = connection.BeginTransaction())
{
Stopwatch stopwatch = Stopwatch.StartNew();
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "INSERT INTO test_timeout VALUES (?f1, ?f2, ?f3, ?f4, ?f5)";
command.Parameters.Add("?f1", MySqlDbType.Int32);
command.Parameters.Add("?f2", MySqlDbType.Int32);
command.Parameters.Add("?f3", MySqlDbType.Int32);
command.Parameters.Add("?f4", MySqlDbType.Int32);
command.Parameters.Add("?f5", MySqlDbType.LongBlob);
command.CommandTimeout = 0;
command.Prepare();
byte[] buffer;
using (MemoryStream stream = new MemoryStream())
{
using (BinaryWriter binary = new BinaryWriter(stream))
{
int count = 0;
while (stream.Position < binarySize)
{
binary.Write(++count);
}
}
buffer = stream.ToArray();
}
for (int i = 0; i < requiredNumberOfRowsPerRun; ++i)
{
command.Parameters[1].Value = i;
command.Parameters[2].Value = i;
command.Parameters[3].Value = i;
command.Parameters[4].Value = buffer;
command.ExecuteNonQuery();
}
}
transaction.Commit();
Assert.IsNotNull(transaction);
stopwatch.Stop();
double seconds = stopwatch.Elapsed.TotalSeconds;
double recordsPerSecond = requiredNumberOfRowsPerRun / seconds;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("Truncate Result : Insert {0} Took {1:F4}; Per Second {2:F1} ",
requiredNumberOfRowsPerRun, seconds, recordsPerSecond);
Debug.WriteLine(sb.ToString());
}
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "SELECT * FROM test_timeout";
Stopwatch stopwatch = Stopwatch.StartNew();
int count = 0;
using (MySqlDataReader reader = command.ExecuteReader())
{
int previous = -1;
while (reader.Read())
{
int current = reader.GetInt32(0);
Assert.Greater(current, previous);
previous = current;
++count;
}
}
stopwatch.Stop();
double seconds = stopwatch.Elapsed.TotalSeconds;
double recordsPerSecond = count / seconds;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("Test Result : Select {0} Took {1:F4}; Per Second {2:F1} ",
count, seconds, recordsPerSecond);
Debug.WriteLine(sb.ToString());
}
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "TRUNCATE TABLE test_timeout";
command.ExecuteNonQuery();
}
}
MySqlConnection.ClearPool(connection);
}
}
public void SnapshotIsolationLevelThrowsNotSupportedException()
{
using (MySqlConnection connection = new MySqlConnection(GetConnectionString(true)))
{
connection.Open();
MySqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
transaction.Commit();
}
}
public static bool Insert(Order order)
{
MySqlTransaction tr = null;
try
{
using (var conn = new MySqlConnection(Globals.CONN_STR))
{
order.OrderNo = DocumentGenerate.GetDocumentRunning("SO");
conn.Open();
tr = conn.BeginTransaction();
var sql = @"INSERT INTO orders
(order_no,
order_date,
customer_code,
order_flag,
comments,
active,
create_by)
VALUES(@order_no,
@order_date,
@customer_code,
@order_flag,
@comments,
@active,
@create_by)" ;
var cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
cmd.Parameters.AddWithValue("order_date", order.RequestDate);
cmd.Parameters.AddWithValue("customer_code", order.Customer.CustomerCode);
cmd.Parameters.AddWithValue("order_flag", order.OrderFlag);
cmd.Parameters.AddWithValue("comments", order.Comments);
cmd.Parameters.AddWithValue("active", order.Active);
cmd.Parameters.AddWithValue("create_by", order.CreateBy);
cmd.ExecuteNonQuery();
sql = @"INSERT INTO orders_item
(order_no,
product_code,
seq,
order_qty,
order_wgh,
bom_code,
order_set_qty,
order_set_wgh,
create_by)
VALUES(
@order_no,
@product_code,
@seq,
@order_qty,
@order_wgh,
@bom_code,
@order_set_qty,
@order_set_wgh,
@create_by)" ;
foreach (var item in order.OrderItems)
{
cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
cmd.Parameters.AddWithValue("product_code", item.Product.ProductCode);
cmd.Parameters.AddWithValue("seq", item.Seq);
cmd.Parameters.AddWithValue("order_qty", item.OrderQty);
cmd.Parameters.AddWithValue("order_wgh", item.OrderWgh);
cmd.Parameters.AddWithValue("bom_code", item.BomCode);
cmd.Parameters.AddWithValue("order_set_qty", item.OrderSetQty);
cmd.Parameters.AddWithValue("order_set_wgh", item.OrderSetWgh);
cmd.Parameters.AddWithValue("active", order.Active);
cmd.Parameters.AddWithValue("create_by", order.CreateBy);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
return(true);
}
catch (Exception ex)
{
tr.Rollback();
throw;
}
}
public static void intervogelsoortall(Vogelsoort vogel)
{
MySqlConnection myConnection = new MySqlConnection("server = localhost; user id = root; password=ascent;persistsecurityinfo=True;database=datanallyspapegaaien");
myConnection.Open();
MySqlCommand myCommand = myConnection.CreateCommand();
MySqlTransaction myTrans;
// Start a local transaction
myTrans = myConnection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
Int64 papeid = new Int64();
try
{
try
{
MySqlDataReader reader = null;
string selectCmd = "LAST_INSERT_ID() FROM vogelsoort";
MySqlCommand command = new MySqlCommand(selectCmd, myConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
string idpape = reader.ToString();
papeid = Convert.ToInt32(idpape);
}
}
catch (Exception ex)
{
}
foreach (Vogelondersoort onder in vogel.ondersoorten)
{
Int64 papidnew = new Int64();
papidnew = (papeid++);
myCommand.CommandText = "INSERT INTO ondersoort (naam) VALUES ('?ondersoortnaam')";
myCommand.Parameters.Add("?ondersoortnaam", MySqlDbType.VarChar).Value = onder.Naam;
myCommand.ExecuteNonQuery();
myCommand.CommandText = "INSERT INTO hoofdtoonder (Id, idondersoorten) VALUES ( @lastparrot, LAST_INSERT_ID() from ondersoort)";
myCommand.Parameters.Add("@lastparrot", MySqlDbType.Int32).Value = papidnew;
myCommand.ExecuteNonQuery();
}
myCommand.CommandText = "INSERT INTO vogelsoort (naam, idsublistsoorten) VALUES ( @vogelsoortnaam, LAST_INSERT_ID() from hooftoonder)";
myCommand.Parameters.Add("@vogelsoortnaam", MySqlDbType.Int32).Value = vogel.Soortnaam;
myCommand.ExecuteNonQuery();
myTrans.Commit();
}
catch (Exception e)
{
try
{
myTrans.Rollback();
}
catch (MySqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}
public static bool Update(Order order)
{
MySqlTransaction tr = null;
try
{
using (var conn = new MySqlConnection(Globals.CONN_STR))
{
conn.Open();
tr = conn.BeginTransaction();
var sql = @"SELECT order_flag FROM orders WHERE order_no=@order_no";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
var orderFlag = (int)cmd.ExecuteScalar();
sql = @"SELECT sum(unload_wgh) as unload_wgh FROM orders_item WHERE order_no=@order_no";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
var unloadWgh = (decimal)cmd.ExecuteScalar();
if (orderFlag > 0 || unloadWgh > 0)
{
throw new Exception("ไม่สามารถบันทึกเอกสารได้ \n\t เนื่องจากเอกสารได้นำไปใช้งานแล้ว");
}
sql = @"UPDATE orders
SET order_date=@order_date,
customer_code=@customer_code,
order_flag=@order_flag,
comments=@comments,
active=@active,
modified_at=CURRENT_TIMESTAMP,
modified_by=@modified_by
WHERE order_no=@order_no" ;
cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
cmd.Parameters.AddWithValue("order_date", order.RequestDate);
cmd.Parameters.AddWithValue("customer_code", order.Customer.CustomerCode);
cmd.Parameters.AddWithValue("order_flag", order.OrderFlag);
cmd.Parameters.AddWithValue("comments", order.Comments);
cmd.Parameters.AddWithValue("active", order.Active);
cmd.Parameters.AddWithValue("modified_by", order.ModifiedBy);
var affRow = cmd.ExecuteNonQuery();
sql = @"Delete From orders_item
WHERE order_no=@order_no" ;
cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
cmd.ExecuteNonQuery();
sql = @"INSERT INTO orders_item
(order_no,
product_code,
seq,
order_qty,
order_wgh,
bom_code,
order_set_qty,
order_set_wgh,
create_by)
VALUES(
@order_no,
@product_code,
@seq,
@order_qty,
@order_wgh,
@bom_code,
@order_set_qty,
@order_set_wgh,
@create_by)" ;
foreach (var item in order.OrderItems)
{
cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
cmd.Parameters.AddWithValue("product_code", item.Product.ProductCode);
cmd.Parameters.AddWithValue("seq", item.Seq);
cmd.Parameters.AddWithValue("order_qty", item.OrderQty);
cmd.Parameters.AddWithValue("order_wgh", item.OrderWgh);
cmd.Parameters.AddWithValue("bom_code", item.BomCode);
cmd.Parameters.AddWithValue("order_set_qty", item.OrderSetQty);
cmd.Parameters.AddWithValue("order_set_wgh", item.OrderSetWgh);
cmd.Parameters.AddWithValue("create_by", order.CreateBy);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
return(true);
}
catch (Exception ex)
{
throw;
}
}
public string updateUser(User pUser)
{
string _Id = "";
using (MySqlConnection _conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
{
_conn.Open();
MySqlTransaction _trans = _conn.BeginTransaction();
MySqlCommand _cmd = new MySqlCommand("call spUpdateUser('" + pUser.Id +
"','" + pUser.Username +
"','" + pUser.Password +
"','" + pUser.EmployeeId +
"','" + pUser.UserGroupId +
"','" + pUser.Remarks +
"','" + pUser.UserId + "');", _conn);
try
{
_cmd.Transaction = _trans;
_Id = _cmd.ExecuteScalar().ToString();
_trans.Commit();
_conn.Close();
}
catch
{
_trans.Rollback();
_Id = "";
}
}
return(_Id);
#region "MySql Server Code"
/*bool _success = false;
* using (MySqlConnection _conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
* {
* _conn.Open();
* MySqlTransaction _trans = _conn.BeginTransaction();
* MySqlCommand _cmd = new MySqlCommand("exec spUpdateUser @Username=N'" + pUser.Username +
* "', @Password=N'" + pUser.Password +
* "', @EmployeeId=N'" + pUser.EmployeeId +
* "', @UserGroupId=N'" + pUser.UserGroupId +
* "', @Remarks=N'" + pUser.Remarks +
* "', @UsernameUpdate=N'"+pUser.CreatedBy+"'", _conn);
* try
* {
* _cmd.Transaction = _trans;
* int _rowsAffected = _cmd.ExecuteNonQuery();
* _trans.Commit();
* _conn.Close();
* if (_rowsAffected > 0)
* {
* _success = true;
* }
* else
* {
* _success = false;
* }
* }
* catch
* {
* _trans.Rollback();
* _success = false;
* }
* }
* return _success;*/
#endregion
}
public void RollingBackOnClose()
{
execSQL("CREATE TABLE Test (id INT) ENGINE=InnoDB");
string connStr = GetPoolingConnectionString();
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1)", c);
c.BeginTransaction();
cmd.ExecuteNonQuery();
}
using (MySqlConnection c2 = new MySqlConnection(connStr))
{
c2.Open();
MySqlCommand cmd2 = new MySqlCommand("SELECT COUNT(*) from Test", c2);
c2.BeginTransaction();
object count = cmd2.ExecuteScalar();
Assert.AreEqual(0, count);
}
MySqlConnection connection = new MySqlConnection(connStr);
connection.Open();
KillConnection(connection);
}
public static string Insert(string football_round_chain_id, string sys_user_id, string football_match_id, string match_result_id, string football_match_ids, string USER, string TOKEN)
{
if (!AccessToken.Read(USER, TOKEN))
{
return("登录超时");
}
IDictionary <string, string> fdict = new Dictionary <string, string>();
fdict.Add("id", football_round_chain_id);
fdict.Add("delete_flag", "IS NULL");
DataSet ds = DBHelper.Select("football_round_chain", "participant_amount,name", string.Empty, fdict, "and");
if (ds.Tables[0].Rows.Count == 0)
{
return("竞猜不存在");
}
string participant_amount = ds.Tables[0].Rows[0]["participant_amount"].ToString();
string football_round_chain_name = ds.Tables[0].Rows[0]["name"].ToString();
string[] football_match_idarr = football_match_ids.Split(',');
if (football_match_idarr.Length != Convert.ToInt32(participant_amount))
{
return("接龙长度异常");
}
fdict = new Dictionary <string, string>();
fdict.Add("id", sys_user_id);
fdict.Add("delete_flag", "IS NULL");
ds = DBHelper.Select("sys_user", "balance", string.Empty, fdict, "and");
if (ds.Tables[0].Rows.Count == 0)
{
return("用户不存在");
}
if (Convert.ToInt32(ds.Tables[0].Rows[0]["balance"]) < 6)
{
return("爱心不足6元");
}
using (MySqlConnection dbConnection = new MySqlConnection(DBHelper.strConnection))
{
dbConnection.Open();
using (MySqlTransaction trans = dbConnection.BeginTransaction())
{
IDictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("football_round_chain_id", football_round_chain_id);
dict.Add("sys_user_id", sys_user_id);
dict.Add("participant_need", (Convert.ToInt32(participant_amount) - 1).ToString());
dict.Add("chain_vote_status_id", "1");
dict.Add("vote_datetime", DateTime.Now.ToString());
DBHelper.Insert("football_round_chain_vote", dict, dbConnection, trans);
string football_round_chain_vote_id = DBHelper.SelectNewId(dbConnection, trans);
dict = new Dictionary <string, string>();
dict.Add("football_round_chain_vote_id", football_round_chain_vote_id);
dict.Add("sys_user_id", sys_user_id);
dict.Add("football_match_id", football_match_id);
dict.Add("match_result_id", match_result_id);
dict.Add("vote_datetime", DateTime.Now.ToString());
DBHelper.Insert("football_round_chain_vote_invite", dict, dbConnection, trans);
foreach (string football_match_idi in football_match_idarr)
{
if (football_match_idi.Equals(football_match_id))
{
continue;
}
dict = new Dictionary <string, string>();
dict.Add("football_round_chain_vote_id", football_round_chain_vote_id);
dict.Add("football_match_id", football_match_idi);
DBHelper.Insert("football_round_chain_vote_invite", dict, dbConnection, trans);
}
dict = new Dictionary <string, string>();
dict.Add("balance", "数字相减-6");
fdict = new Dictionary <string, string>();
fdict.Add("id", sys_user_id);
DBHelper.Update("sys_user", dict, fdict, "and", dbConnection, trans);
sys_user_balance_change.Insert(sys_user_id, "2", "-6", "发起接龙“" + football_round_chain_name + "”", "football_round_chain_vote", football_round_chain_vote_id, dbConnection, trans);
trans.Commit();
}
}
return(string.Empty);
}