public bool Insert(Catelog c)
{
bool isSuccess = false;
MySqlConnection conn = db.makeConn();
try
{
conn.Open();
string sql = "insert into FAMILIAS (NAME, DESCRIPTION, STATUS, DTO, SHOP_ID) Values (@NAME, @DESCRIPTION, @STATUS, @DTO, @SHOP_ID)";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@NAME", c.Name);
cmd.Parameters.AddWithValue("@DESCRIPTION", c.Description);
cmd.Parameters.AddWithValue("@STATUS", c.Status);
cmd.Parameters.AddWithValue("@DTO", c.Dto);
cmd.Parameters.AddWithValue("@SHOP_ID", c.ShopId);
int row = cmd.ExecuteNonQuery();
if (row > 0)
{
isSuccess = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
//Update
public bool Update(Catelog c)
{
bool isSuccess = false;
MySqlConnection conn = db.makeConn();
try
{
conn.Open();
//NAME, DESCRIPTION, STATUS, DTO, SHOP_ID
string sqlShop = "UPDATE FAMILIAS SET NAME=@NAME, DESCRIPTION=@DESCRIPTION, STATUS=@STATUS, DTO=@DTO WHERE ID=@ID";
MySqlCommand cmd = new MySqlCommand(sqlShop, conn);
cmd.Parameters.AddWithValue("@NAME", c.Name);
cmd.Parameters.AddWithValue("@DESCRIPTION", c.Description);
cmd.Parameters.AddWithValue("@STATUS", c.Status);
cmd.Parameters.AddWithValue("@DTO", c.Dto);
cmd.Parameters.AddWithValue("@ID", c.Id);
int row = cmd.ExecuteNonQuery();
if (row > 0)
{
isSuccess = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (permission())
{
Catelog c = new Catelog();
c = getCatelogsValues();
string validation = ValidateData(c, "add");
if (validation == "OK")
{
bool success = c.Insert(c);
if (success)
{
clear();
RefreshData();
MessageBox.Show("Catelog Inserted Successfully!");
}
else
{
MessageBox.Show("Error!");
}
}
else
{
MessageBox.Show(validation);
}
}
}
private void Update_Click(object sender, EventArgs e)
{
if (permission())
{
Catelog c = new Catelog();
if (CatelogIdtxt.Text != "")
{
c = getCatelogsValues();
c.Id = Convert.ToInt32(CatelogIdtxt.Text);
string validation = ValidateData(c, "update");
if (validation == "OK")
{
bool success = c.Update(c);
if (success)
{
clear();
RefreshData();
MessageBox.Show("Catelog Updated Successfully!");
}
else
{
MessageBox.Show("Error! in Updated Catelog");
}
}
else
{
MessageBox.Show(validation);
}
}
else
{
MessageBox.Show("Data not Exist!");
}
}
}
public DataTable Search(Catelog c)
{
MySqlConnection conn = db.makeConn();
DataTable dt = new DataTable();
string search = "";
if (c.Name != "")
{
search += " AND NAME like '%" + c.Name + "%'";
}
if (c.Description != "")
{
search += " AND DESCRIPTION like '%" + c.Description + "%'";
}
if (c.Dto > 0)
{
search += " AND DTO like '%" + c.Dto + "%'";
}
if (c.Status != "")
{
search += " AND STATUS like '%" + c.Status + "%'";
}
try
{
if (search != "")
{
string sql = selectData;
sql += search;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@SHOP_ID", c.ShopId);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
conn.Open();
adp.Fill(dt);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dt);
}
private Catelog getCatelogsValues()
{
Catelog cate = new Catelog();
cate.Name = catelogNametxt.Text;
cate.Description = catelogDestxt.Text;
if (catelogDto.Text != "")
{
cate.Dto = Convert.ToSingle(catelogDto.Text);
}
if (CatelogIdtxt.Text != "")
{
cate.Id = Convert.ToInt32(CatelogIdtxt.Text);
}
cate.Status = catelogStatus.Text;
return(cate);
}
//string label, string keyword, string orderby
public DataTable Search(string label, string keyword, string orderby)
{
MySqlConnection conn = db.makeConn();
Catelog c = new Catelog();
DataTable dt = new DataTable();
string search = "";
if (label != "" && keyword != "")
{
search += " AND " + label + " like '%" + keyword + "%'";
}
if (label != "" && orderby != "")
{
search += " ORDER BY " + label + " " + orderby;
}
if (label == "" && keyword != "")
{
search += " AND (NAME like '%" + keyword + "%'";
search += " OR DESCRIPTION like '%" + keyword + "%'";
search += " OR DTO like '%" + keyword + "%'";
search += " OR STATUS like '%" + keyword + "%')";
}
try
{
if (search != "")
{
string sql = selectData;
sql += search;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@SHOP_ID", c.ShopId);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
conn.Open();
adp.Fill(dt);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dt);
}
private void Srarch_Click_1(object sender, EventArgs e)
{
if (permission())
{
Catelog c = new Catelog();
c = getCatelogsValues();
string validation = ValidateData(c, "search");
if (validation == "OK")
{
dtCate = c.Search(c);
}
else
{
MessageBox.Show(validation);
}
dtCatelog.DataSource = dtCate;
}
}
private String ValidateData(Catelog c, string operation)
{
string err = "OK";
if (operation == "add" || operation == "update")
{
if (c.Name == "")
{
err = "Name is a required Field.";
}
if (c.Status == "")
{
err = "Status is a required Field.";
}
}
if (operation == "search")
{
if (c.Name == "" && c.Description == "" && c.Dto == 0 && c.Status == "")
{
err = "Write something to perfourm search.";
}
}
return(err);
}
public static void Initialize(NopCommereceCloneDbContext context)
{
context.Database.EnsureCreated();
if (context.Products.Any())
{
return;
}
#region seeds
var admins = new Admin[]
{
new Admin
{
Password = "******",
RegisteredAt = DateTime.Now,
Username = "******"
},
new Admin
{
Password = "******",
RegisteredAt = DateTime.Now,
Username = "******"
}
};
foreach (Admin a in admins)
{
context.Admins.Add(a);
}
context.SaveChanges();
var catelogs = new Catelog[]
{
new Catelog
{
CatelogName = "Electronics",
AddedAt = DateTime.Now,
AdminID = 1
}
};
foreach (var c in catelogs)
{
context.Catelogs.Add(c);
}
context.SaveChanges();
var products = new Product[]
{
new Product
{
Price = 10.00,
ProductName = "Raspberry Pi",
AddedAt = DateTime.Now,
AdminID = 1,
CatelogID = 1,
},
new Product
{
Price = 100.00,
ProductName = "LattePanda",
AddedAt = DateTime.Now,
AdminID = 2,
CatelogID = 1,
},
new Product
{
Price = 22.33,
ProductName = "Xiaomi",
AddedAt = DateTime.Now,
AdminID = 1,
CatelogID = 1,
}
};
foreach (Product p in products)
{
context.Products.Add(p);
}
context.SaveChanges();
#endregion
}
