private void ExceptionTest() { using (SqlConnection connection = new SqlConnection(_connectionString)) { connection.Open(); SqlTransaction tx = connection.BeginTransaction(); string invalidSaveStateMessage = SystemDataResourceManager.Instance.SQL_NullEmptyTransactionName; string executeCommandWithoutTransactionMessage = SystemDataResourceManager.Instance.ADP_TransactionRequired("ExecuteNonQuery"); string transactionConflictErrorMessage = SystemDataResourceManager.Instance.ADP_TransactionConnectionMismatch; string parallelTransactionErrorMessage = SystemDataResourceManager.Instance.ADP_ParallelTransactionsNotSupported("SqlConnection"); AssertException <InvalidOperationException>(() => { SqlCommand command = new SqlCommand("sql", connection); command.ExecuteNonQuery(); }, executeCommandWithoutTransactionMessage); AssertException <InvalidOperationException>(() => { SqlConnection con1 = new SqlConnection(_connectionString); con1.Open(); SqlCommand command = new SqlCommand("sql", con1); command.Transaction = tx; command.ExecuteNonQuery(); }, transactionConflictErrorMessage); AssertException <InvalidOperationException>(() => { connection.BeginTransaction(null); }, parallelTransactionErrorMessage); AssertException <InvalidOperationException>(() => { connection.BeginTransaction(""); }, parallelTransactionErrorMessage); AssertException <ArgumentException>(() => { tx.Rollback(null); }, invalidSaveStateMessage); AssertException <ArgumentException>(() => { tx.Rollback(""); }, invalidSaveStateMessage); AssertException <ArgumentException>(() => { tx.Save(null); }, invalidSaveStateMessage); AssertException <ArgumentException>(() => { tx.Save(""); }, invalidSaveStateMessage); } }
public bool Adder(SqlConnection connection) { SqlTransaction transaction = connection.BeginTransaction(); SqlCommand command = connection.CreateCommand(); command.Transaction = transaction; bool delete = true; if (delete) { command.CommandText = "DELETE studio " + "WHERE id_studio > 20"; command.ExecuteNonQuery(); transaction.Save("0"); } int i = 0; while (delete && i < 20) { i++; command.CommandText = "INSERT studio(name_studio, year_," + " center_studio, site_studio) values ('Когда наступит'," + " 2019, '?', '!?')"; command.ExecuteNonQuery(); transaction.Save(i.ToString()); reader(command); Console.WriteLine("Продолжить добавление?(1/0):"); int cont = 1; if (!Int32.TryParse(Console.ReadLine(), out cont)) { delete = false; } if (cont != 1) { delete = false; } } Console.WriteLine("Восстановление!"); while (i > 0) { i--; transaction.Rollback(i.ToString()); Console.WriteLine("------------------------"); Console.WriteLine("Шаг назад"); Console.WriteLine("------------------------"); reader(command); } return(true); }
/// <summary> 开启事务或保存点,返回string.Empty或保存点的名称 /// </summary> public string BeginTransaction() { if (Transaction == null) { Transaction = DbConnection.BeginTransaction(); return(string.Empty); } else { var tranPoint = Guid.NewGuid().ToString("N"); Transaction.Save(tranPoint); return(tranPoint); } }
/// <summary> /// Propósito: Guarda una transacción pendiente en la conexión abierta para esta clase hasta un savepoint /// con el nombre dado. /// Cuando se envía un rollback, quien llama puede hacer rollback hasta este punto o a la transacción completa. /// </summary> /// <param name="savePointName">Nombre del savepoint a guardar dentro de la transacción actual.</param> /// <returns>true, si el save fue exitoso, sino genera una Exception</returns> public bool SaveTransaction(string savePointName) { try { if (!_isTransactionPending) { // No hay una transacción pendiente throw new Exception("SaveTransaction::No hay una transacción pendiente."); } if ((_dBConnection.State & ConnectionState.Open) == 0) { // no hay conexión abierta throw new Exception("SaveTransaction::La conexión no está abierta."); } // save the transaction _currentTransaction.Save(savePointName); // Store the savepoint in the list. _savePoints.Add(savePointName); return(true); } catch (Exception ex) { // bubble error throw ex; } }
/// <summary> /// 事务提交多个数据库操作,返回每一次执行影响的行数列表 /// </summary> /// <param name="cmdTextList">SQL语句组</param> /// <returns></returns> public List <int> ExecTransaction(List <string> cmdTextList) { List <int> ExecQueryList = new List <int>(); string timePoint = DateTime.Now.ToString("yyyyMMddHHmmssfff"); string transName = "TRANS" + timePoint; this.Open(); SqlCommand cmd = this.SQLConn.CreateCommand(); SqlTransaction trans = this.SQLConn.BeginTransaction(transName); cmd.Connection = this.SQLConn; cmd.Transaction = trans; try { trans.Save(transName); for (int i = 0; i < cmdTextList.Count; i++) { cmd.CommandText = cmdTextList[i]; ExecQueryList.Add(cmd.ExecuteNonQuery()); } trans.Commit(); } catch { trans.Rollback(transName); } this.Close(); return(ExecQueryList); }
/// <summary> /// Purpose: Saves a pending transaction on the open connection object of this class to a 'savepoint' /// with the given name. /// When a rollback is issued, the caller can rollback to this savepoint or roll back the complete transaction. /// </summary> /// <param name="savePointName">Name of the savepoint to store the current transaction under.</param> /// <returns>true, if save was succesful, or an Exception exception is thrown</returns> public bool SaveTransaction(string savePointName) { try { if (!_isTransactionPending) { // no transaction pending throw new Exception("SaveTransaction::No transaction pending."); } if ((_dBConnection.State & ConnectionState.Open) == 0) { // no open connection throw new Exception("SaveTransaction::Connection is not open."); } // save the transaction _currentTransaction.Save(savePointName); // Store the savepoint in the list. _savePoints.Add(savePointName); return(true); } catch (Exception ex) { // bubble error throw ex; } }
public void SaveTransaction(string savePointName) { if (_currentTrasaction == null) { return; } _currentTrasaction.Save(savePointName); }
void InsertDataToDatabase() { TakeCourseData(); TakeStudentData(); try { conn.Open(); SqlTransaction sqlTransaction = conn.BeginTransaction(); SqlCommand cmdInserts = conn.CreateCommand(); cmdInserts.Transaction = sqlTransaction; try { cmdInserts.CommandText = "Insert into tblCourse(name,fees) values(@cname,@cfees)"; cmdInserts.Parameters.AddWithValue("@cname", courseName); cmdInserts.Parameters.AddWithValue("@cfees", fees); cmdInserts.ExecuteNonQuery(); sqlTransaction.Save("t1"); cmdInserts.CommandText = "Insert into tblStudent(name,age) values(@sname,@sage)"; cmdInserts.Parameters.AddWithValue("@sname", studentName); cmdInserts.Parameters.AddWithValue("@sage", age); cmdInserts.ExecuteNonQuery(); try { Console.WriteLine("Please enter name"); string name = Console.ReadLine(); Console.WriteLine("Please enter num"); int number = int.Parse(Console.ReadLine()); cmdInserts.CommandText = "Insert into tblDummy values(@name,@num)"; cmdInserts.Parameters.AddWithValue("@name", number); cmdInserts.Parameters.AddWithValue("@num", name); cmdInserts.ExecuteNonQuery(); } catch (Exception) { sqlTransaction.Rollback("t1"); } sqlTransaction.Commit(); Console.WriteLine("Course and Student added"); } catch (Exception e) { Console.WriteLine(e.Message); //Console.WriteLine(e.StackTrace); sqlTransaction.Rollback(); Console.WriteLine("GO go nothing has been inserted. All gone..."); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { conn.Close(); } }
public void BeginTransaction(string name) { if (++_transactionCounter == 1) { _transaction = _connection.BeginTransaction(name); } else { _transaction.Save(name); } }
private static void BanishVillain(int villainId, SqlConnection connection, SqlTransaction transaction) { string sql = File.ReadAllText(@".\..\..\..\..\MinionsDB\Scripts\RemoveVillain-BanishVillain.sql"); using (SqlCommand command = new SqlCommand(sql, connection, transaction)) { command.Parameters.AddWithValue("@villainId", villainId); command.ExecuteNonQuery(); transaction.Save("VillainBanished"); } }
private void ScopedTransactionTest() { using (SqlConnection connection = new SqlConnection(_connectionString)) { SqlCommand command = new SqlCommand("select * from " + _tempTableName1 + " where CustomerID='ZYXWV'", connection); connection.Open(); SqlTransaction tx = connection.BeginTransaction("transName"); command.Transaction = tx; using (SqlDataReader reader = command.ExecuteReader()) { Assert.False(reader.HasRows, "Error: table is in incorrect state for test."); } using (SqlCommand command2 = connection.CreateCommand()) { command2.Transaction = tx; command2.CommandText = "INSERT INTO " + _tempTableName1 + " VALUES ( 'ZYXWV', 'XYZ', 'John' );"; command2.ExecuteNonQuery(); } tx.Save("saveName"); //insert another one using (SqlCommand command2 = connection.CreateCommand()) { command2.Transaction = tx; command2.CommandText = "INSERT INTO " + _tempTableName1 + " VALUES ( 'ZYXW2', 'XY2', 'KK' );"; command2.ExecuteNonQuery(); } tx.Rollback("saveName"); using (SqlDataReader reader = command.ExecuteReader()) { Assert.True(reader.HasRows, "Error Scoped Transaction Test : incorrect number of rows in table after rollback to save state one."); int count = 0; while (reader.Read()) { count++; } Assert.Equal(1, count); } tx.Rollback(); connection.Close(); } }
public static void Transactions() { string connectionString = GetConnectionString(); using (var connection = new SqlConnection(connectionString)) { connection.Open(); SqlTransaction tx = connection.BeginTransaction(); tx.Save("one"); tx.Commit(); } }
private static int ReleaseVillainMinions(int villainId, SqlConnection connection, SqlTransaction transaction) { string sql = File.ReadAllText(@".\..\..\..\..\MinionsDB\Scripts\RemoveVillain-ReleaseMinions.sql"); int minionsReleased = 0; using (SqlCommand command = new SqlCommand(sql, connection, transaction)) { command.Parameters.AddWithValue("@villainId", villainId); minionsReleased = command.ExecuteNonQuery(); transaction.Save("MinionsReleased"); } return(minionsReleased); }
public FlowTemplateStepRepositoryTests() { _connection = new SqlConnection(LocalConnectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); _context = new FlowDataContext(_connection) { Transaction = _transaction }; newTemplate = new FlowTemplate { Name = "Example Template 1" }; _context.FlowTemplates.InsertOnSubmit(newTemplate); _context.SubmitChanges(); startStep = new Library.Data.FlowTemplateStep { FlowTemplateId = newTemplate.Id, Name = "Example StartStep 1", StepTypeId = 1 }; collectDataStep = new Library.Data.FlowTemplateStep { FlowTemplateId = newTemplate.Id, Name = "Example CollectDataStep 2", StepTypeId = 3 }; storeDataStep = new Library.Data.FlowTemplateStep { FlowTemplateId = newTemplate.Id, Name = "Example StoreDataStep 3", StepTypeId = 4 }; stopStep = new Library.Data.FlowTemplateStep { FlowTemplateId = newTemplate.Id, Name = "Example StopStep 4", StepTypeId = 2 }; _context.FlowTemplateSteps.InsertAllOnSubmit(new[] { startStep, collectDataStep, storeDataStep, stopStep }); _context.SubmitChanges(); _transaction.Save("insert"); _repository = new FlowTemplateStepRepository(_context); }
public ActionResult OwnerRegistration(Owner o) { SqlTransaction trans = null; EncryptDecrypt e = new EncryptDecrypt(); try { // TODO: Add insert logic here SqlConnection con = new SqlConnection(@"Data Source=(localdb)\MsSqlLocalDb;Initial Catalog=project;Integrated Security=True"); con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; trans = con.BeginTransaction("abc"); cmd.Transaction = trans; cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into Users values(@UserName,@Password,@Type,@Flag)"; cmd.Parameters.AddWithValue("UserName", o.Email); cmd.Parameters.AddWithValue("Password", e.Base64Encode(o.Password)); cmd.Parameters.AddWithValue("Flag", 1); cmd.Parameters.AddWithValue("Type", "Owner"); trans.Save("saveusers"); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandType = CommandType.Text; cmd.CommandText = "insert into Owner values(@Email,@Name,@Phone,@Description,@Address,@Latitude,@Longitude,@Flag)"; cmd.Parameters.AddWithValue("Email", o.Email); cmd.Parameters.AddWithValue("Name", o.Name); cmd.Parameters.AddWithValue("Phone", o.Phone); cmd.Parameters.AddWithValue("Description", o.Description); cmd.Parameters.AddWithValue("Address", o.Address); cmd.Parameters.AddWithValue("Latitude", o.Latitude); cmd.Parameters.AddWithValue("Longitude", o.Longitude); cmd.Parameters.AddWithValue("Flag", 0); cmd.ExecuteNonQuery(); trans.Commit(); con.Close(); ViewData["msg"] = "registered succsessfully"; return(RedirectToAction("Login", "LoginId")); } catch (SqlException ex) { ViewBag.Message = "error"; trans.Rollback(); return(View()); } }
/// <summary> /// 创建事物回滚片段(回滚点) /// </summary> /// <param name="savePointName"></param> /// <returns></returns> public string SaveTransactionPoint(string savePointName) { if (string.IsNullOrEmpty(savePointName)) { return(string.Empty); } if (null != myTransaction) { myTransaction.Save(savePointName); return(savePointName); } return(null); }
public FlowTemplateRepositoryTests() { _connection = new SqlConnection(LocalConnectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); using (var command = new SqlCommand(@"INSERT INTO FlowTemplate (Name) VALUES ('Example Template 1');", _connection, _transaction)) { command.ExecuteNonQuery(); } _transaction.Save("insert"); _context = new FlowDataContext(_connection) { Transaction = _transaction }; _repository = new FlowTemplateRepository(_context); }
static void Main(string[] args) { using (SqlConnection testConnection = new SqlConnection(connectionString)) { SqlCommand testCommand = testConnection.CreateCommand(); testConnection.Open(); SqlTransaction myTransaction = testConnection.BeginTransaction(); testCommand.Transaction = myTransaction; try { testCommand.CommandText = "Insert into Customers (FirstName, LastName, AccountBalance) Values ('Bat','Man',100)"; testCommand.ExecuteNonQuery(); myTransaction.Save("firstCustomer"); testCommand.CommandText = "Insert into Customers (FirstName, LastName, AccountBalance) Values ('The','Joker',100)"; testCommand.ExecuteNonQuery(); myTransaction.Rollback("firstCustomer"); testCommand.CommandText = "Insert into Customers (FirstName, LastName, AccountBalance) Values ('Robin','Sidekick',100)"; testCommand.ExecuteNonQuery(); myTransaction.Commit(); testCommand.CommandText = "Select * from Customers"; SqlDataReader sqlDa = testCommand.ExecuteReader(); while (sqlDa.Read()) { Console.WriteLine( " FirstName: " + sqlDa["FirstName"] + " LastName = " + sqlDa["LastName"] + " AccountBalance = " + sqlDa["AccountBalance"]); } sqlDa.Close(); } catch (System.Exception ex) { Console.WriteLine(ex.ToString()); } testConnection.Close(); } // testConnection.Dispose is called automatically. }
public FlowInstanceRepositoryTests() { Console.WriteLine("SomeFixture ctor: This should only be run once"); _connection = new SqlConnection(LocalConnectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); using (var command = new SqlCommand("INSERT INTO FlowInstance (Id) VALUES (1);", _connection, _transaction)) { command.ExecuteNonQuery(); } _transaction.Save("insert"); _context = new FlowDataContext(_connection) { Transaction = _transaction }; _flowInstanceRepository = new FlowInstanceRepository(_context); }
/// <summary> /// トランザクション設定 /// </summary> public void BeginTransaction() { if (isTran && isSetSavePoint) { throw new Exception("トランザクションが設定できませんでした。"); } if (isTran) { tran.Save(SavePointName); isSetSavePoint = true; } else { this.tran = this.conn.BeginTransaction(); this.isTran = true; } }
public void SaveTrans(string tag) { if (trans == null) { throw new SqlServerException( "Occurred transaction doesn't begin error in UlSqlServer.SaveTrans", ESqlServerException.SaveTransaction); } try { trans.Save(tag); } catch (Exception e) { string msg = $"{e.ToString()}\r\nOccurred begin transaction exception in UlSqlServer.SaveTrans"; throw new SqlServerException(msg, ESqlServerException.SaveTransaction); } }
private void TestTransaction() { using (var sqlConnection = new SqlConnection(ConString)) { sqlConnection.Open(); // 新建一个事务 SqlTransaction sqlTransaction = sqlConnection.BeginTransaction("TestTransaction"); SqlCommand sqlCommand = sqlConnection.CreateCommand(); sqlCommand.Transaction = sqlTransaction; try { // 指令1 sqlCommand.CommandText = "INSERT INTO UserBasicInfo VALUES ('Ezio', 'Ezio Auditore', '男', '31','*****@*****.**','BlackWater','123')"; sqlCommand.ExecuteNonQuery(); // 建立一个保存点 sqlTransaction.Save("Ezio"); // 不能执行的指令2 try { sqlCommand.CommandText = "INSERT INTO UserBasicInfo VALUES ('Hking', 'Hking Auditore', '男', '20','*****@*****.**','Strawberry','123','123')"; sqlCommand.ExecuteNonQuery(); } catch (Exception e) { //回滚到保存点 sqlTransaction.Rollback("Ezio"); } //提交操作 sqlTransaction.Commit(); Response.Write("<script>alert('SUCCESS!')</script>"); } catch { Response.Write("<script>alert('ERROR!')</script>"); } } }
/// <summary> /// Initiates a database transaction. /// </summary> /// <param name="iso">The isolation level to use.</param> /// <remarks> /// <para> /// Database transactions may be nested. This class implements this /// via save points. Note that every call to <see cref="BeginTransaction" /> /// must be matched with a call to <see cref="Commit" /> or <see cref="Rollback" />. /// </para> /// <note> /// The isolation level will be ignored for nested /// transactions. /// </note> /// </remarks> public void BeginTransaction(IsolationLevel iso) { if (sqlCon == null) { throw new InvalidOperationException(NotOpenMsg); } if (sqlTrans == null) { sqlTrans = sqlCon.BeginTransaction(iso); transactions.Push(null); } else { var savePoint = SavePointPrefix + (nextSavePoint++).ToString(); sqlTrans.Save(savePoint); transactions.Push(savePoint); } }
protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString); SqlCommand cmd1 = new SqlCommand(); cmd1.CommandType = CommandType.Text; cmd1.CommandText = "update SampleAccount set Balance = Balance - @Amount where Id='A'"; cmd1.Connection = con; cmd1.Parameters.Add(new SqlParameter("@Amount", Convert.ToInt32(TextBox1.Text))); SqlCommand cmd2 = new SqlCommand("update SampleAccount set Balance = Balance + @Amount where Id='B'", con); cmd2.Parameters.Add(new SqlParameter("@Amount", Convert.ToInt32(TextBox1.Text))); SqlTransaction trans = null; try { con.Open(); trans = con.BeginTransaction(); cmd1.Transaction = trans; cmd1.ExecuteNonQuery(); trans.Save("nash"); throw new ApplicationException(); cmd2.Transaction = trans; cmd2.ExecuteNonQuery(); trans.Commit(); Page.Response.Write("Success"); } catch (Exception) { trans.Rollback("nash"); Page.Response.Write("Fail"); trans.Commit(); } finally { con.Close(); } }
public static void Actualizar(Proveedor proveedor) { using (SqlConnection conn = new SqlConnection(GlobalConnectionString)) { SqlTransaction tran = conn.BeginTransaction(); conn.Open(); string varSql = @"UPDATE Proveedores SET Nombre=@nombre,Telefono=@telefono,Direccion=@direccion, Email=@email WHERE IdProveedor=@idproveedor"; SqlCommand cmd = new SqlCommand(varSql, conn); cmd.Parameters.AddWithValue("@nombre", proveedor.Nombre); cmd.Parameters.AddWithValue("@telefono", proveedor.Telefono); cmd.Parameters.AddWithValue("@direccion", proveedor.Direccion); cmd.Parameters.AddWithValue("@email", proveedor.Email); cmd.Parameters.AddWithValue("@idproveedor", proveedor.IdProveedor); cmd.ExecuteNonQuery(); tran.Save("update1"); //cmd.Parameters.Clear(); //string sql=@"INSERT INTO Proveedor_Producto(IdProveedor,IdProducto) VALUES(@idproveedor,@idproducto)"; //cmd.CommandText = sql; //foreach (Producto producto in proveedor.Productos) //{ //if (!ChequearProducto(proveedor.IdProveedor, producto.IdProducto)) //{ // cmd.Parameters.AddWithValue("@idproveedor", proveedor.IdProveedor); // cmd.Parameters.AddWithValue("@idproducto", producto.IdProducto); // cmd.ExecuteNonQuery(); //} //} try { tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw new Exception(ex.Message); } } }
/// <summary> /// 事务提交多个数据库操作,返回每一次执行影响的行数列表 /// </summary> /// <param name="cmdTextList">SQL语句组</param> /// <param name="cmdParametersList">SQL参数列表集合</param> /// <returns></returns> public List <int> ExecTransaction(List <string> cmdTextList, List <List <SqlParameter> > cmdParametersList) { if (cmdTextList.Count != cmdParametersList.Count) { throw new Exception("SQL语句数与参数列表数不相等"); } List <int> ExecQueryList = new List <int>(); string timePoint = DateTime.Now.ToString("yyyyMMddHHmmssfff"); string transName = "TRANS" + timePoint; this.Open(); SqlCommand cmd = this.SQLConn.CreateCommand(); SqlTransaction trans = this.SQLConn.BeginTransaction(transName); cmd.Connection = this.SQLConn; cmd.Transaction = trans; try { trans.Save(transName); for (int i = 0; i < cmdTextList.Count; i++) { cmd.CommandText = cmdTextList[i]; cmd.Parameters.AddRange(cmdParametersList[i].ToArray()); ExecQueryList.Add(cmd.ExecuteNonQuery()); cmd.Parameters.Clear(); } trans.Commit(); } catch (Exception e) { log.Save(e, cmd); trans.Rollback(transName); } this.Close(); return(ExecQueryList); }
/// <summary> /// 设置监测点 /// </summary> /// <param name="savepointName">监测点名称</param> public void SavePoint(string savepointName) { m_Transaction.Save(savepointName); }
private void Transact() { tran = null; using (con = new SqlConnection(cs)) { con.Open(); using (tran = con.BeginTransaction(IsolationLevel.Serializable)) { //Thread.Sleep(5000); try { //TÌM KHÁCH HÀNG TRONG DB string cmnd = ""; string stateKH = ""; cmd = new SqlCommand("SELECT CMND,TrangThai FROM KHACH_HANG WHERE CMND = @cmnd", con, tran); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); rdr = cmd.ExecuteReader(); if (rdr.Read()) { cmnd = rdr["CMND"].ToString(); stateKH = rdr["TrangThai"].ToString(); } rdr.Close(); //KHÔNG CÓ KHÁCH HÀNG TRONG DB if (cmnd.Equals("")) { //THÊM THÔNG TIN KHÁCH HÀNG VÀO DB cmd = new SqlCommand("INSERT INTO KHACH_HANG(HoDem,Ten,CMND,TrangThai) VALUES(@hodem,@ten,@cmnd,@trangthaikh)", con, tran); cmd.Parameters.AddWithValue("@hodem", txtLastName.Text.Trim()); cmd.Parameters.AddWithValue("@ten", txtFirstName.Text.Trim()); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); cmd.Parameters.AddWithValue("@trangthaikh", "Checkin"); tran.Save("insertGuest"); cmd.ExecuteNonQuery(); } else //CÓ KHÁCH HÀNG TRONG DB { //KIỂM TRA XEM KHÁCH HÀNG CÓ ĐANG CHECKIN if (stateKH.Equals("Checkin")) //CÓ { MessageBox.Show("Khách hàng này đang Checkin", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); return; } else if (stateKH.Equals("Reserve")) //KHÁCH HÀNG CÓ ĐẶT TRƯỚC { //CẬP NHẬT TRẠNG THÁI SANG CHECKIN, cmd = new SqlCommand("UPDATE KHACH_HANG SET TrangThai=@trangthaikh WHERE CMND=@cmnd", con, tran); cmd.Parameters.AddWithValue("@trangthaikh", "Checkin"); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); tran.Save("updateGuestState"); cmd.ExecuteNonQuery(); //CẬP NHẬT DS ĐẶT TRƯỚC SANG ĐÃ CHECKIN, cmd = new SqlCommand("UPDATE DAT_TRUOC SET TrangThai=@trangthaidt WHERE CMND=@cmnd AND MaPhong=@maphong", con, tran); cmd.Parameters.AddWithValue("@maphong", lblRoom.Text); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); cmd.Parameters.AddWithValue("@trangthaidt", "Checkin"); tran.Save("updateReserve"); cmd.ExecuteNonQuery(); } else //KHÔNG { //CẬP NHẬT TRẠNG THÁI SANG CHECKIN cmd = new SqlCommand("UPDATE KHACH_HANG SET TrangThai=@trangthaikh WHERE CMND =@cmnd", con, tran); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); cmd.Parameters.AddWithValue("@trangthaikh", "Checkin"); tran.Save("updateGuest"); cmd.ExecuteNonQuery(); } } //KIỂM TRA PHÒNG cmd = new SqlCommand("SELECT GioiHan,SoNguoiHienCo,TinhTrang FROM PHONG WHERE MaPhong=@maphong", con, tran); cmd.Parameters.AddWithValue("@maphong", lblRoom.Text); rdr = cmd.ExecuteReader(); int gioihan = 0; int snhienco = 0; int sndangki = (int)Val(txtPeopleCount.Text.Trim()); string stateRoom = ""; if (rdr.Read()) { gioihan = (int)Val(rdr["GioiHan"].ToString()); snhienco = (int)Val(rdr["SoNguoiHienCo"].ToString()); stateRoom = rdr["TinhTrang"].ToString(); } else { MessageBox.Show("Không tìm được phòng này"); tran.Rollback(); return; } rdr.Close(); //SO SÁNH SỐ NGƯỜI ĐĂNG KÍ VỚI SỐ NGƯỜI GIỚI HẠN if (gioihan == snhienco) { MessageBox.Show("Phòng đầy!!!,Xin chọn phòng khác"); tran.Rollback(); return; } else if (snhienco + sndangki > gioihan) { MessageBox.Show("Đăng kí vượt quá số người qui định " + ((sndangki + snhienco) - gioihan) + " người." + ",Xin chọn phòng khác"); tran.Rollback(); return; } //KIỂM TRA NGƯỜI CHECKIN CHÍNH, LƯU VÀO HOÁ ĐƠN, LƯU GIAO DỊCH if (snhienco == 0 && (stateRoom.Equals("Trống") || stateRoom.Equals("Đặt trước"))) { string idkh = ""; //TÌM KHÁCH HÀNG cmd = new SqlCommand("SELECT CMND FROM KHACH_HANG WHERE CMND=@cmnd", con, tran); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); rdr = cmd.ExecuteReader(); while (rdr.Read()) { idkh = rdr["CMND"].ToString(); } rdr.Close(); if (idkh.Equals("")) { MessageBox.Show("Xảy ra lỗi...,Xin thử lại"); tran.Rollback(); return; } ////KIỂM TRA NGƯỜI DÙNG CÓ ĐANG ĐĂNG NHẬP //cmd = new SqlCommand("SELECT TrangThai FROM NGUOI_DUNG WHERE idND=@find", con, tran); //cmd.Parameters.AddWithValue("@find", HomeForm.Instance().lblIDUser.Text); //rdr = cmd.ExecuteReader(); //if (rdr.Read()) //{ // if (rdr["TrangThai"].Equals("0")) // { // this.Hide(); // LoginForm login = new LoginForm(); // login.Show(); // tran.Rollback(); // return; // } //} //rdr.Close(); //TẠO HOÁ ĐƠN cmd = new SqlCommand("INSERT INTO HOA_DON(idND,CMND,MaPhong,NgayLap) VALUES(@idnd,@cmnd,@maphong,@ngaylap)", con, tran); cmd.Parameters.AddWithValue("@idnd", HomeForm.Instance().lblIDUser.Text); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); cmd.Parameters.AddWithValue("@maphong", lblRoom.Text); cmd.Parameters.AddWithValue("@ngaylap", datetime); tran.Save("insertHD"); cmd.ExecuteNonQuery(); //LẤY ID HD, TẠO CTHD string idhd = ""; cmd.CommandText = "SELECT idHD FROM HOA_DON WHERE MaPhong=@maphong AND CMND=@cmnd ORDER BY NgayLap DESC"; //cmd.Parameters.AddWithValue("@idkh", idkh); //cmd.Parameters.AddWithValue("@trangthaihd", 0); rdr = cmd.ExecuteReader(); if (rdr.Read()) { idhd = rdr["idHD"].ToString(); } rdr.Close(); if (idhd.Equals("")) { MessageBox.Show("Xảy ra lỗi...,Xin thử lại"); tran.Rollback(); return; } cmd.CommandText = "INSERT INTO CT_HOA_DON(idHD,SoNgay,SoNguoi,NgayDangKi,TraTruoc) VALUES(@idhd,@songay,@songuoi,@ngaydangki,@tratruoc)"; cmd.Parameters.AddWithValue("@idhd", idhd); cmd.Parameters.AddWithValue("@songuoi", (int)Val(txtPeopleCount.Text)); cmd.Parameters.AddWithValue("@songay", (int)Val(txtNumOfDay.Text)); cmd.Parameters.AddWithValue("@ngaydangki", datetime); cmd.Parameters.AddWithValue("@tratruoc", decimal.Parse(txtAdvance.Text)); tran.Save("insertCTHD"); cmd.ExecuteNonQuery(); //TẠO GIAO DỊCH cmd.CommandText = "INSERT INTO GIAO_DICH(LoaiDK,CMND,MaPhong,NgayDangKi,SoNguoi,TrangThai) VALUES(@loaidk,@cmnd,@maphong,@ngaydangki,@songuoi,@trangthaigd)"; cmd.Parameters.AddWithValue("@loaidk", 0); //0-người đăng kí chính cmd.Parameters.AddWithValue("@trangthaigd", "Active"); //cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); tran.Save("insertGD"); cmd.ExecuteNonQuery(); //CẬP NHẬT TÌNH TRẠNG PHÒNG cmd.CommandText = "UPDATE PHONG SET TinhTrang=@tinhtrangphong,SoNguoiHienCo+=@snhienco WHERE MaPhong=@maphong"; cmd.Parameters.AddWithValue("@tinhtrangphong", "Đang sử dụng"); //0-người đăng kí chính cmd.Parameters.AddWithValue("@snhienco", 1); tran.Save("updateRoom"); cmd.ExecuteNonQuery(); } //NGƯỜI PHỤ, KHÔNG LƯU HOÁ ĐƠN, CẬP NHẬT PHÒNG, CẬP NHẬT GIAO DỊCH else { string CMND = ""; //TÌM KHÁCH HÀNG cmd = new SqlCommand("SELECT CMND FROM KHACH_HANG WHERE CMND=@cmnd", con, tran); cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim()); rdr = cmd.ExecuteReader(); while (rdr.Read()) { CMND = rdr["CMND"].ToString(); } rdr.Close(); if (CMND.Equals("")) { MessageBox.Show("Xảy ra lỗi...,Xin thử lại"); tran.Rollback(); return; } //TẠO GIAO DỊCH cmd.CommandText = "INSERT INTO GIAO_DICH(LoaiDK,CMND,MaPhong,NgayDangKi,SoNguoi,TrangThai) VALUES(@loaidk,@cmnd,@maphong,@ngaydangki,@songuoi,@trangthaigd)"; cmd.Parameters.AddWithValue("@loaidk", 1); //1-người đăng kí phụ cmd.Parameters.AddWithValue("@trangthaigd", "Active"); cmd.Parameters.AddWithValue("@maphong", lblRoom.Text); cmd.Parameters.AddWithValue("@ngaydangki", datetime); cmd.Parameters.AddWithValue("@songuoi", 1); tran.Save("insertGD"); cmd.ExecuteNonQuery(); //CẬP NHẬT TÌNH TRẠNG PHÒNG cmd.CommandText = "UPDATE PHONG SET TinhTrang=@tinhtrangphong,SoNguoiHienCo+=@snhienco WHERE MaPhong=@maphong"; cmd.Parameters.AddWithValue("@tinhtrangphong", "Đang sử dụng"); //0-người đăng kí chính cmd.Parameters.AddWithValue("@snhienco", 1); tran.Save("updateRoom"); cmd.ExecuteNonQuery(); } tran.Commit(); MessageBox.Show("CHECKIN thành công.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("Commit exeption type: " + ex.GetType()); MessageBox.Show("Commit exeption type: " + ex.StackTrace); MessageBox.Show("Message :" + ex.Message); try { if (tran != null) { tran.Rollback(); } } catch (Exception ex2) { MessageBox.Show("Rollback exeption type :" + ex2.GetType()); MessageBox.Show("Message :" + ex2.Message); } } finally { con.Close(); lblCurrentPeopleCount.Text = loadCurrentPeople(); ReservationListForm.Instance().load_ReservationList(); GuestListForm.Instance().load_GuestList(); RoomListForm.Instance().load_RoomList(); } } } }
/// <summary> /// Creates a save point with the specified name. /// </summary> /// <param name="savePoint">The save point.</param> public void Save(string savePoint) { _currentTransaction.Save(savePoint); }
public void removeItemSQL(string id) { try { using (con = new SqlConnection(cs)) { con.Open(); using (tran = con.BeginTransaction(IsolationLevel.Serializable)) { try { //LẤY CMND KHÁCH HÀNG cmd = new SqlCommand("SELECT CMND FROM GIAO_DICH WHERE MaPhong=@maphong AND TrangThai=@trangthai AND LoaiDK=@loaidk", con, tran); cmd.Parameters.AddWithValue("@maphong", ServiceForm.Instance().lblRoom.Text); cmd.Parameters.AddWithValue("@trangthai", "Active"); cmd.Parameters.AddWithValue("@loaidk", 0); rdr = cmd.ExecuteReader(); string cmnd = ""; if (rdr.Read()) { cmnd = rdr["CMND"].ToString(); } else { MessageBox.Show("Không tìm thấy CMND khách hàng"); return; } rdr.Close(); //LẤY ID HOÁ ĐƠN cmd = new SqlCommand("SELECT idHD FROM HOA_DON WHERE CMND=@cmnd AND MaPhong=@maphong ORDER BY NgayLap DESC", con, tran); cmd.Parameters.AddWithValue("@cmnd", cmnd); cmd.Parameters.AddWithValue("@maphong", ServiceForm.Instance().lblRoom.Text); rdr = cmd.ExecuteReader(); string idhd = ""; if (rdr.Read()) { idhd = rdr["idHD"].ToString(); } else { MessageBox.Show("Không tìm thấy hoá đơn!!!"); return; } rdr.Close(); cmd = new SqlCommand("DELETE FROM DS_DICH_VU WHERE idHD=@idhd AND idDV=@iddv", con, tran); cmd.Parameters.AddWithValue("@idHD", idhd); cmd.Parameters.AddWithValue("@idDV", id); tran.Save("deleteDSDV"); cmd.ExecuteNonQuery(); tran.Commit(); } catch (Exception ex) { MessageBox.Show("Commit exeption type: " + ex.GetType()); MessageBox.Show("Message :" + ex.Message); try { if (tran != null) { tran.Rollback(); } } catch (Exception ex2) { MessageBox.Show("Rollback exeption type :" + ex2.GetType()); MessageBox.Show("Message :" + ex2.Message); } } finally { con.Close(); } } } } catch (Exception ex) { MessageBox.Show(ex.Message); throw; } }