public static bool UpdateProductInfo(SanPham sanpham)
{
CSQL objSQL = new CSQL(DatabaseProduct.connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
return(false);
}
// input param
SqlParameter prmID = new SqlParameter("@ID", SqlDbType.Int);
prmID.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmID);
SqlParameter prmTenSanPham = new SqlParameter("@Tensp", SqlDbType.NVarChar, 50);
prmTenSanPham.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmTenSanPham);
SqlParameter prmGia = new SqlParameter("@Gia", SqlDbType.Float);
prmGia.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmGia);
SqlParameter prmMoTaSP = new SqlParameter("@MoTaSP ", SqlDbType.NVarChar, 100);
prmMoTaSP.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmMoTaSP);
SqlParameter prmAnh = new SqlParameter("@Anh ", SqlDbType.NVarChar, 100);
prmAnh.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmAnh);
// output param
SqlParameter Result = new SqlParameter("@Message", SqlDbType.NVarChar, 100);
Result.Direction = ParameterDirection.Output;
Result.DbType = DbType.String;
objSQL.Command.Parameters.Add(Result);
//set value~
prmID.Value = sanpham.ID;
prmTenSanPham.Value = sanpham.Tensp;
prmGia.Value = sanpham.Gia;
prmMoTaSP.Value = sanpham.MoTaSP;
prmAnh.Value = sanpham.Anh;
objSQL.ExecuteSP("PRODUCT_UPDATE");
if (Result.Value.ToString() == "Cập nhật thành công")
{
return(true);
}
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
return(false);
}
// get SanPham for UpdateSP
public static SanPham GetProductInfo(int ID)
{
CSQL objSQL = new CSQL(connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
throw new Exception("Không thể kết nối");
}
// input param
SqlParameter prmId = new SqlParameter("@Id", SqlDbType.Int);
prmId.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmId);
//set value~
prmId.Value = ID;
SqlDataReader reader = objSQL.GetDataReaderFromSP("Lay_Thongtin_SanPham");
var currentSanPham = new SanPham();
if (reader.Read())
{
currentSanPham.ID = ID;
if (!reader.IsDBNull(reader.GetOrdinal("Tensp")))
{
currentSanPham.Tensp = reader.GetString(reader.GetOrdinal("Tensp"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Gia")))
{
currentSanPham.Gia = reader.GetDouble(reader.GetOrdinal("Gia"));
}
if (!reader.IsDBNull(reader.GetOrdinal("MoTaSP")))
{
currentSanPham.MoTaSP = reader.GetString(reader.GetOrdinal("MoTaSP"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Anh")))
{
currentSanPham.Anh = reader.GetString(reader.GetOrdinal("Anh"));
}
return(currentSanPham);
}
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
finally
{
objSQL._CloseConnection();
}
return(null);
}
public static string Insert_Product(SanPham sanpham)
{
CSQL objSQL = new CSQL(DatabaseProduct.connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
return("Open Connection false");
}
SqlParameter prmTenSP = new SqlParameter("@TenSP", SqlDbType.NVarChar, 50);
prmTenSP.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmTenSP);
// input param
SqlParameter prmGia = new SqlParameter("@Gia", SqlDbType.Float);
prmGia.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmGia);
SqlParameter prmMoTaSP = new SqlParameter("@MoTaSP", SqlDbType.NVarChar, 50);
prmMoTaSP.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmMoTaSP);
SqlParameter prmAnh = new SqlParameter("@Anh", SqlDbType.NVarChar, 50);
prmMoTaSP.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmAnh);
// output param
SqlParameter Result = new SqlParameter("@MESS", SqlDbType.NVarChar, 50);
Result.Direction = ParameterDirection.Output;
Result.DbType = DbType.String;
objSQL.Command.Parameters.Add(Result);
prmTenSP.Value = sanpham.Tensp;
prmGia.Value = sanpham.Gia;
prmMoTaSP.Value = sanpham.MoTaSP;
prmAnh.Value = sanpham.Anh;
objSQL.ExecuteSP("Tao_SanPham");
return(Result.Value.ToString());
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
return("Create Product Failed");
}
//
// public static string connectionString = @"Data Source=DESKTOP-A16NIVL;Initial Catalog=CBTT;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;";
//create customer
//get company list
public static List <SanPham> Lay_DS_SanPham()
{
CSQL objSQL = new CSQL(connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
throw new Exception("Không thể kết nối");
}
// input param
SqlDataReader reader = objSQL.GetDataReaderFromSP("Lay_DS_SanPham");
var DsSP = new List <SanPham>();
SanPham sp;
while (reader.Read())
{
try //chiennd edit 18/03/2019
{
sp = new SanPham()
{
ID = reader.GetInt32(reader.GetOrdinal("ID")),
Tensp = reader.GetString(reader.GetOrdinal("Tensp")),
Gia = reader.GetDouble(reader.GetOrdinal("Gia")),
MoTaSP = reader.GetString(reader.GetOrdinal("MoTaSP")),
Anh = reader.GetString(reader.GetOrdinal("Anh"))
};
DsSP.Add(sp);
}
catch (Exception ex) { }
}
return(DsSP);
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
finally
{
objSQL._CloseConnection();
}
return(new List <SanPham>());
}
/// <summary>
/// Nếu đăng nhập và lấy các thông tin thành công thì return true
/// </summary>
/// <param name="User"></param>
/// <returns></returns>
public static string User_Login(LoginViewModel User)
{
//Test offline
//HttpContext.Current.Session[SessionValue.AccountType] = SessionValue.Admin;
//return true;
CSQL objSQL = new CSQL(DatabaseProduct.connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
return("Không thể kết nối");
}
// input param
SqlParameter prmUsername = new SqlParameter("@Username", SqlDbType.VarChar, 50);
prmUsername.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmUsername);
// input param
SqlParameter prmPassword = new SqlParameter("@Password", SqlDbType.VarChar, 32);
prmPassword.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmPassword);
// output param
SqlParameter Result = new SqlParameter("@MESS", SqlDbType.NChar, 50);
Result.Direction = ParameterDirection.Output;
Result.DbType = DbType.String;
objSQL.Command.Parameters.Add(Result);
//set value~
prmUsername.Value = User.UserName;
prmPassword.Value = User.Password;
objSQL.ExecuteSP("SHOP_LOGIN");
return(Result.Value.ToString());
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
return("Không thể đăng nhập");
}
public static bool DeleteProduct(int ID)
{
CSQL objSQL = new CSQL(DatabaseProduct.connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
throw new Exception("Không thể kết nối");
}
SqlParameter prmID = new SqlParameter("@ID", SqlDbType.Int);
prmID.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmID);
prmID.Value = ID;
SqlParameter Result = new SqlParameter("@Message", SqlDbType.NChar, 50);
Result.Direction = ParameterDirection.Output;
Result.DbType = DbType.String;
objSQL.Command.Parameters.Add(Result);
if (!objSQL.ExecuteSP("DELETE_PRODUCT")) //xóa không thành công
{
return(false);
}
if (Result.Value.ToString() == "Xóa thành công")
{
return(true);
}
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
return(false);
}
/// <summary>
/// Lấy Toàn bộ thông tin của User trong DataBase
/// </summary>
/// <param name="User">User name và pass lấy từ view</param>
/// <returns> Users nếu là admin , hoặc customer </returns>
public static Object GetUser(LoginViewModel User)
{
CSQL objSQL = new CSQL(DatabaseProduct.connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
throw new Exception("Không thể kết nối");
}
// input param
SqlParameter prmUsername = new SqlParameter("@Username", SqlDbType.VarChar, 50);
prmUsername.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmUsername);
// input param
SqlParameter prmPassword = new SqlParameter("@Password", SqlDbType.VarChar, 32);
prmPassword.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmPassword);
// output param
SqlParameter Result = new SqlParameter("@MESS", SqlDbType.NChar, 50);
Result.Direction = ParameterDirection.Output;
Result.DbType = DbType.String;
objSQL.Command.Parameters.Add(Result);
//set value~
prmUsername.Value = User.UserName;
prmPassword.Value = User.Password;
SqlDataReader reader = objSQL.GetDataReaderFromSP("LOGIN_INFO_GET");
//Nếu là admin thì sẽ lấy những thông tin của Addmin
if (HttpContext.Current.Session[ApplicationConfig.AccountType] == ApplicationConfig.Admin)
{
var currentUser = new Users();
if (reader.Read())
{
currentUser.UserName = User.UserName;
currentUser.Password = User.Password.HashMD5();
if (!reader.IsDBNull(reader.GetOrdinal("FullName")))
{
currentUser.FullName = reader.GetString(reader.GetOrdinal("FullName"));
}
currentUser.Active = reader.GetInt32(reader.GetOrdinal("Active"));
if (!reader.IsDBNull(reader.GetOrdinal("Phone")))
{
currentUser.Phone = reader.GetString(reader.GetOrdinal("Phone"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Email")))
{
currentUser.Email = reader.GetString(reader.GetOrdinal("Email"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Note")))
{
currentUser.Note = reader.GetString(reader.GetOrdinal("Note"));
}
if (!reader.IsDBNull(reader.GetOrdinal("RegionID")))
{
currentUser.RegionID = reader.GetInt32(reader.GetOrdinal("RegionID"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CreateDate")))
{
currentUser.CreateDate = reader.GetDateTime(reader.GetOrdinal("CreateDate"));
}
// if (!reader.IsDBNull(reader.GetOrdinal("Symbol")))
// {
// currentUser.Symbol = reader.GetString(reader.GetOrdinal("Symbol"));
// }
// if (!reader.IsDBNull(reader.GetOrdinal("Exchange")))
// {
// currentUser.Exchange = reader.GetString(reader.GetOrdinal("Exchange"));
// }
return(currentUser);
}
}
if (HttpContext.Current.Session[ApplicationConfig.AccountType] == ApplicationConfig.Customer)
{
var currentUser = new Customer();
if (reader.Read())
{
currentUser.UserName = User.UserName;
currentUser.Password = User.Password;
if (!reader.IsDBNull(reader.GetOrdinal("FullName")))
{
currentUser.FullName = reader.GetString(reader.GetOrdinal("FullName"));
}
currentUser.Active = reader.GetInt32(reader.GetOrdinal("Active"));
if (!reader.IsDBNull(reader.GetOrdinal("Phone")))
{
currentUser.Phone = reader.GetString(reader.GetOrdinal("Phone"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Email")))
{
currentUser.Email = reader.GetString(reader.GetOrdinal("Email"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CC")))
{
currentUser.CC = reader.GetString(reader.GetOrdinal("CC"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Note")))
{
currentUser.Note = reader.GetString(reader.GetOrdinal("Note"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CompanyID")))
{
currentUser.CompanyID = reader.GetInt32(reader.GetOrdinal("CompanyID"));
}
if (!reader.IsDBNull(reader.GetOrdinal("CreateDate")))
{
currentUser.CreateDate = reader.GetDateTime(reader.GetOrdinal("CreateDate"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Symbol")))
{
currentUser.Symbol = reader.GetString(reader.GetOrdinal("Symbol"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Exchange")))
{
currentUser.Exchange = reader.GetString(reader.GetOrdinal("Exchange"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Name")))
{
currentUser.CompanyName = reader.GetString(reader.GetOrdinal("Name"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Expert")))
{
currentUser.Expert = reader.GetString(reader.GetOrdinal("Expert"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ExpertFullName")))
{
currentUser.ExpertFullName = reader.GetString(reader.GetOrdinal("ExpertFullName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ExpertEmail")))
{
currentUser.ExpertEmail = reader.GetString(reader.GetOrdinal("ExpertEmail"));
}
if (!reader.IsDBNull(reader.GetOrdinal("ExpertPhone")))
{
currentUser.ExpertPhone = reader.GetString(reader.GetOrdinal("ExpertPhone"));
}
if (!reader.IsDBNull(reader.GetOrdinal("RegionID")))
{
currentUser.RegionID = reader.GetInt32(reader.GetOrdinal("RegionID"));
}
return(currentUser);
}
}
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
finally
{
objSQL._CloseConnection();
}
return(null);
}
public static string Insert_User(Users user)
{
CSQL objSQL = new CSQL(DatabaseProduct.connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
return("Open Connection false");
}
// input param
SqlParameter prmUsername = new SqlParameter("@Username", SqlDbType.VarChar, 50);
prmUsername.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmUsername);
// input param
SqlParameter prmPassword = new SqlParameter("@Password", SqlDbType.VarChar, 32);
prmPassword.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmPassword);
SqlParameter prmFullname = new SqlParameter("@FullName", SqlDbType.NVarChar, 50);
prmUsername.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmFullname);
SqlParameter prmEmail = new SqlParameter("@Email", SqlDbType.VarChar, 100);
prmEmail.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmEmail);
SqlParameter prmCCEmail = new SqlParameter("@CC", SqlDbType.VarChar, 100);
prmCCEmail.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmCCEmail);
SqlParameter prmPhone = new SqlParameter("@Phone", SqlDbType.NVarChar, 50);
prmPhone.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmPhone);
SqlParameter prmNote = new SqlParameter("@Note", SqlDbType.NVarChar, 50);
prmNote.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmNote);
SqlParameter prmActive = new SqlParameter("@Active", SqlDbType.Int, 50);
prmActive.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmActive);
SqlParameter prmRegionID = new SqlParameter("@RegionID", SqlDbType.Int, 50);
prmRegionID.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmRegionID);
//SqlParameter prmCompanyID = new SqlParameter("@CompanyID", SqlDbType.Int, 50);
//prmUsername.Direction = ParameterDirection.Input;
//objSQL.Command.Parameters.Add(prmCompanyID);
// output param
SqlParameter Result = new SqlParameter("@MESS", SqlDbType.NVarChar, 50);
Result.Direction = ParameterDirection.Output;
Result.DbType = DbType.String;
objSQL.Command.Parameters.Add(Result);
//set value~
prmUsername.Value = user.UserName;
prmPassword.Value = user.Password.HashMD5();
prmEmail.Value = user.Email;
prmCCEmail.Value = user.CC;
prmFullname.Value = user.FullName;
prmPhone.Value = user.Phone;
prmNote.Value = user.Note;
prmActive.Value = user.Active;
prmRegionID.Value = user.RegionID;
objSQL.ExecuteSP("CBTT_USER_CREATE");
return(Result.Value.ToString());
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
return("Create User Failed");
}
public static List <SanPham> GetListProduct(ListProductViewModel viewModel)
{
CSQL objSQL = new CSQL(DatabaseProduct.connectionString);
try
{
if (objSQL._OpenConnection() == false)
{
throw new Exception("Không thể kết nối");
}
// input param
if (viewModel.Tensp != null)
{
SqlParameter prmTensp = new SqlParameter("@Tensp", SqlDbType.NVarChar, 50);
prmTensp.Direction = ParameterDirection.Input;
objSQL.Command.Parameters.Add(prmTensp);
prmTensp.Value = viewModel.Tensp;
}
SqlDataReader reader = objSQL.GetDataReaderFromSP("LIST_PRODUCT_GET");
var ListProduct = new List <SanPham>();
SanPham SP;
while (reader.Read())
{
try
{
SP = new SanPham();
if (!reader.IsDBNull(reader.GetOrdinal("ID")))
{
SP.ID = reader.GetInt32(reader.GetOrdinal("ID"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Tensp")))
{
SP.Tensp = reader.GetString(reader.GetOrdinal("Tensp"));
}
if (!reader.IsDBNull(reader.GetOrdinal("Gia")))
{
SP.Gia = reader.GetDouble(reader.GetOrdinal("Gia"));
}
if (!reader.IsDBNull(reader.GetOrdinal("MoTaSP")))
{
SP.MoTaSP = reader.GetString(reader.GetOrdinal("MoTaSP"));
}
ListProduct.Add(SP);
}
catch (Exception ex) {
ex.ToString();
}
}
return(ListProduct);
}
catch (Exception ex)
{
LogFile.Error(ex.ToString()); // Ghi thông tin ra file
}
finally
{
objSQL._CloseConnection();
}
return(new List <SanPham>());
}
