private void buttonSave_Click(object sender, EventArgs e)
{
if (textBoxID.Text == "")
{
textBoxID.Focus();
return;
}
else if (textBoxName.Text == "")
{
textBoxName.Focus();
return;
}
if (ClassConnection.count == 1)
{
ClassConnection.connectionDatabase();
String Sql = @"SELECT * FROM tbcategory WHERE N_category = '" + textBoxName.Text + "'";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
if (DataReader.HasRows)
{
MessageBox.Show("มีข้อมูลซ้ำกรุณาตรวจสอบ", "ตรวจสอบ");
return;
}
else
{
DataReader.Close();
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
try
{
ClassConnection.connectionDatabase();
string SqlInsert = @"INSERT INTO tbcategory(Id_category,N_category)
VALUES(@a,@b)";
ClassConnection.command = new OleDbCommand(SqlInsert, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("a", textBoxID.Text);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("บันทึกข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
}
else if (ClassConnection.count == 2)
{
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
try
{
ClassConnection.connectionDatabase();
string SqlUpdate = @"UPDATE tbcategory SET N_category =@b WHERE Id_category = @id";
ClassConnection.command = new OleDbCommand(SqlUpdate, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถแก้ไขข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("แก้ไขข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
buttonSave.Enabled = false;
buttonCancel.Enabled = false;
buttonAdd.Enabled = true;
buttonEdit.Enabled = true;
buttonDelete.Enabled = true;
ClearData();
ShowData();
ClassConnection.count = 0;
EnabledFalse();
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (textBoxID.Text == "")
{
textBoxID.Focus();
return;
}
else if (textBoxName.Text == "")
{
textBoxName.Focus();
return;
}
else if (textboxDesc.Text == "")
{
textBoxName.Focus();
return;
}
else if (comboBox1.Text == "")
{
comboBox1.Select();
return;
}
else if (textboxSeller.Text == "")
{
textBoxPriceBuy.Focus();
return;
}
else if (textBoxPriceBuy.Text == "")
{
textBoxPriceBuy.Focus();
return;
}
else if (textBoxNum.Text == "")
{
textBoxNum.Focus();
return;
}
if (ClassConnection.count == 1)
{
ClassConnection.connectionDatabase();
string Sql = @"SELECT * FROM tbproduct WHERE N_product = '" + textBoxName.Text + "'";
ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
if (DataReader.HasRows)
{
MessageBox.Show("มีข้อมูลซ้ำกรุณาตรวจสอบ", "ตรวจสอบ");
return;
}
else
{
DataReader.Close();
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();//คนแรกให้เข้าคนที่สองเข้ามาซ้ำไม่ได้
try
{
ClassConnection.connectionDatabase();
string SqlInsert = @"INSERT INTO tbproduct(Id_product, N_product, N_product_desc, Id_category, N_seller, pricebuy ,unit)
VALUES(@a,@b,@c,@d,@e,@f,@g)";
ClassConnection.command = new OleDbCommand(SqlInsert, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("a", textBoxID.Text);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
ClassConnection.command.Parameters.AddWithValue("c", textboxDesc.Text);
ClassConnection.command.Parameters.AddWithValue("d", _idcat);
ClassConnection.command.Parameters.AddWithValue("e", textboxSeller.Text);
ClassConnection.command.Parameters.AddWithValue("f", textBoxPriceBuy.Text);
ClassConnection.command.Parameters.AddWithValue("g", textBoxNum.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("บันทึกข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}//หาสาเหตุ
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
}
else if (ClassConnection.count == 2)
{
OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
try
{
ClassConnection.connectionDatabase();
string SqlUpdate = @"UPDATE tbproduct SET N_product =@b, N_product_desc =@c, Id_category =@d, N_seller =@e, pricebuy =@f,unit =@g WHERE Id_product = @id";
ClassConnection.command = new OleDbCommand(SqlUpdate, ClassConnection.connection);
ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
ClassConnection.command.Parameters.AddWithValue("c", textboxDesc.Text);
ClassConnection.command.Parameters.AddWithValue("d", _idcat);
ClassConnection.command.Parameters.AddWithValue("e", textboxSeller.Text);
ClassConnection.command.Parameters.AddWithValue("f", textBoxPriceBuy.Text);
ClassConnection.command.Parameters.AddWithValue("g", textBoxNum.Text);
ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
int result = ClassConnection.command.ExecuteNonQuery();
if (result == -1)
{
MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถแก้ไขข้อมูลได้", "ตรวจสอบ");
tr.Rollback();
}
else
{
MessageBox.Show("แก้ไขข้อมูลแล้ว", "ตรวจสอบ");
tr.Commit();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message.ToString());
tr.Rollback();
}
finally
{
ClassConnection.connectionDatabase();
tr.Dispose();
}
}
buttonSave.Enabled = false;
buttonCancel.Enabled = false;
buttonAdd.Enabled = true;
buttonEdit.Enabled = true;
buttonDelete.Enabled = true;
ClearData();
ShowData();
ClassConnection.count = 0;
EnabledFalse();
}
public bool PlaceOrder(CheckoutModel order)
{
bool bRes = true;
string ConnectionString = ConfigurationManager.ConnectionStrings["ORACLE"].ConnectionString;
OleDbConnection Connection = new OleDbConnection(ConnectionString);
OleDbTransaction Transaction = null;
try
{
Connection.Open();
Transaction = Connection.BeginTransaction();
string sql1 = "select top 1 OrderNo from Orders order by OrderNo desc";
OleDbCommand cmd1 = new OleDbCommand(sql1, Connection);
cmd1.Transaction = Transaction;
object obj = cmd1.ExecuteScalar();
Int64 orderNo = 1;
if (obj != null)
{
orderNo = int.Parse(obj.ToString()) + 1;
}
obj = GetUserId(order.Customer.UserName);
if (obj == null)
{
throw new Exception("Can't find user in database");
}
foreach (var item in order.Cart.CartList)
{
string sql2 = "insert into OrderDetails (OrderNo, ItemNo, Qty) values (" +
":orderNo, :ProdId, :Quantity)";
OleDbCommand cmd2 = new OleDbCommand(sql2, Connection);
DbParameter p1a = new OleDbParameter(":orderNo", OleDbType.BigInt);
p1a.Value = orderNo;
cmd2.Parameters.Add(p1a);
DbParameter p2a = new OleDbParameter(":ProdId", OleDbType.Integer);
p2a.Value = item.ProductID;
cmd2.Parameters.Add(p2a);
DbParameter p3a = new OleDbParameter(":Quantity", OleDbType.Integer);
p3a.Value = item.ProductQuantity;
cmd2.Parameters.Add(p3a);
cmd2.Transaction = Transaction;
int rows1 = cmd2.ExecuteNonQuery();
if (rows1 <= 0)
{
throw new Exception("Couldn't place your details");
}
}
string sql3 = "insert into Orders (OrderNo,UserID, OrderDate) VALUES (" +
":orderNo, :userId, :orderDate)";
OleDbCommand cmd3 = new OleDbCommand(sql3, Connection);
DbParameter p1b = new OleDbParameter(":orderNo", OleDbType.Integer);
p1b.Value = orderNo;
cmd3.Parameters.Add(p1b);
DbParameter p2b = new OleDbParameter(":userId", OleDbType.VarChar, 50);
p2b.Value = obj.ToString();
cmd3.Parameters.Add(p2b);
DbParameter p3b = new OleDbParameter(":orderDate", OleDbType.VarChar, 50);
p3b.Value = System.DateTime.Now.ToString();
cmd3.Parameters.Add(p3b);
cmd3.Transaction = Transaction;
int rows2 = cmd3.ExecuteNonQuery();
if (rows2 <= 0)
{
throw new Exception("Couldn't insert order in database");
}
Transaction.Commit();
}
catch (Exception)
{
bRes = false;
if (Transaction != null)
{
Transaction.Rollback();
}
throw;
}
finally
{
if (Transaction != null)
{
Transaction.Dispose();
}
}
return(bRes);
}
public bool AddProduct(ProductModel product)
{
bool bRes = true;
string ConnectionString = ConfigurationManager.ConnectionStrings["ORACLE"].ConnectionString;
OleDbConnection Connection = new OleDbConnection(ConnectionString);
OleDbTransaction Transaction = null;
Stream stream = null;
FileInfo fileInfo = null;
Byte[] ImageData = null;
try
{
Connection.Open();
Transaction = Connection.BeginTransaction();
string sql1 = "insert into Products (CatID, ProductSDesc, ProductLDesc,"
+ "Price, Inventory) values("
+ ":catID, :ProductSDesc, :ProductLDesc, :Price, :Inventory)";
OleDbCommand cmd1 = new OleDbCommand(sql1, Connection);
DbParameter p1a = new OleDbParameter(":catID", OleDbType.Integer);
p1a.Value = product.CatagoryID;
cmd1.Parameters.Add(p1a);
DbParameter p2a = new OleDbParameter(":ProductSDesc", OleDbType.VarChar, 50);
p2a.Value = product.ShortDesc;
cmd1.Parameters.Add(p2a);
DbParameter p3a = new OleDbParameter(":ProductLDesc", OleDbType.VarChar);
p3a.Value = product.LongDesc;
cmd1.Parameters.Add(p3a);
DbParameter p4a = new OleDbParameter(":Price", OleDbType.Decimal);
p4a.Value = product.Price;
cmd1.Parameters.Add(p4a);
DbParameter p5a = new OleDbParameter(":Inventory", OleDbType.Integer);
p5a.Value = product.Inventory;
cmd1.Parameters.Add(p5a);
cmd1.Transaction = Transaction;
int rows = cmd1.ExecuteNonQuery();
if (rows <= 0)
{
throw new Exception("Couldn't insert product");
}
string sql2 = "select * from Products where ProductId = (select max(ProductId) from products)";
List <DbParameter> PList2 = new List <DbParameter>();
object obj = idataAccess.GetSingleAnswer(sql2, PList2);
string ProdId = obj != null?obj.ToString() : "";
stream = product.Image.ImageFile.InputStream;
fileInfo = new FileInfo(Path.GetFullPath(product.Image.ImageFile.FileName));
ImageData = new Byte[product.Image.ImageFile.ContentLength];
stream.Read(ImageData, 0, product.Image.ImageFile.ContentLength);
string sql3 = "insert into Images(Name, Type, Image, ProductId) values(@Name, @Type, @ImageData, @ProductId)";
OleDbCommand cmd2 = new OleDbCommand(sql3, Connection);
DbParameter p1b = new OleDbParameter(":Name", OleDbType.VarChar, 50);
p1b.Value = fileInfo.Name;
cmd2.Parameters.Add(p1b);
DbParameter p2b = new OleDbParameter(":Type", OleDbType.VarChar, 10);
p2b.Value = fileInfo.Extension;
cmd2.Parameters.Add(p2b);
DbParameter p3b = new OleDbParameter(":ImageData", OleDbType.VarBinary);
p3b.Value = ImageData;
cmd2.Parameters.Add(p3b);
DbParameter p4b = new OleDbParameter(":ProductId", OleDbType.Integer);
p4b.Value = ProdId;
cmd2.Parameters.Add(p4b);
cmd2.Transaction = Transaction;
rows = cmd2.ExecuteNonQuery();
if (rows <= 0)
{
throw new Exception("Couldn't insert product image");
}
Transaction.Commit();
}
catch (Exception)
{
bRes = false;
if (Transaction != null)
{
Transaction.Rollback();
}
throw;
}
finally
{
if (Transaction != null)
{
Transaction.Dispose();
}
Connection.Close();
}
return(bRes);
}
public override void EndTrans()
{
m_trans.Commit();
m_trans.Dispose();
}
protected override void Dispose(bool disposing)
{
source.Dispose();
base.Dispose(disposing);
}
private void button1_Click(object sender, EventArgs e)
{
if (txb_psw.Text == "123456")
{
int result = 0;
string DBPath = System.Environment.CurrentDirectory + "\\CSSDBase.mdb";
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
OleDbConnection conn = new OleDbConnection(constr + DBPath);
OleDbTransaction transaction = null;
try
{
conn.Open();
//执行事务
transaction = conn.BeginTransaction();
OleDbCommand comm = new OleDbCommand();
comm.Connection = conn;
comm.Transaction = transaction;
string str1 = "delete from tb_record";
comm.CommandText = str1;
if (comm.ExecuteNonQuery() < 0)
{
throw new Exception();
}
string str2 = "alter table tb_record alter column ID counter(1,1)";
comm.CommandText = str2;;
if (comm.ExecuteNonQuery() < 0)
{
throw new Exception();
}
//提交事务
transaction.Commit();
result = 1;
conn.Close();
}
catch
{
// 回滚事务
transaction.Rollback();
MessageBox.Show("清除数据失败！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
conn.Close();
conn.Dispose();
transaction.Dispose();
}
if (result == 1)
{
MessageBox.Show("清除数据成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
else
{
MessageBox.Show("密码错误。请重新输入！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
txb_psw.Text = "";
txb_psw.Focus();
}
}
