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); }