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