/// <summary>
/// Get value from table by where Clause
/// </summary>
/// <param name="tableName"></param>
/// <param name="arrColsName"></param>
/// <param name="whereClause"></param>
/// <param name="orderBy"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks></remarks>
public static string GetValueFromTable(string tableName, string colName_ToGetValue, string whereClause, string orderBy, ref string errMsg)
{
string strSQL = null;
Int32 arrSize = default(Int32);
Int32 index = default(Int32);
string strCols = null;
DataTable temp = null;
try
{
// with where
if ((whereClause.Trim().Length > 0))
{
// with order
if ((orderBy.Trim().Length > 0))
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " where " + whereClause + " order by " + orderBy;
// without order
}
else
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " where " + whereClause;
}
// without where
}
else
{
// with order by
if ((orderBy.Trim().Length > 0))
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " order by " + orderBy;
// without order by
}
else
{
strSQL = " select " + colName_ToGetValue + " from " + tableName;
}
}
//===============
temp = SQLExecute.LoadDataFromDB(strSQL, tableName, ref errMsg);
if ((temp.Rows.Count > 0))
{
errMsg = "";
return(temp.Rows[0][colName_ToGetValue].ToString());
}
else
{
errMsg = "No data found";
}
return(string.Empty);
}
catch (Exception ex)
{
return(string.Empty);
}
}
public int capnhap_tt_khachhang(string _tendangnhap, bool _trangthai, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[4];
prms[i] = new SqlParameter("@tendangnhap", _tendangnhap);
i = i + 1;
prms[i] = new SqlParameter("@trangthai", _trangthai);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("dbo.sp_trangthai_khachhang", ref prms, 3, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
public int duyet_hoadon(string _mahoadon, bool _trangthai, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[4];
prms[i] = new SqlParameter("@mahoadon", _mahoadon);
i = i + 1;
prms[i] = new SqlParameter("@trangthai", _trangthai);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("dbo.sp_duyet_hoadon", ref prms, 3, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
public DataTable getall_danhthu_para(DateTime _ngaydat, int _pageIndex, int _pageSize, ref int _rowCount, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
SqlParameter[] prms = new SqlParameter[5];
prms[i] = new SqlParameter("@ngaydat", _ngaydat);
i = i + 1;
prms[i] = new SqlParameter("@PageNumber", _pageIndex);
i = i + 1;
prms[i] = new SqlParameter("@PageSize", _pageSize);
i = i + 1;
prms[i] = new SqlParameter("@outrowcount", SqlDbType.Int, 3);
prms[i].Direction = ParameterDirection.Output;
DataTable tbl = SQLExecute.LoadDataFromDBPage("[dbo].[sp_getall_danhthu_para]", ref prms, 4, ref errMsg, ref _rowCount);
return(tbl);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// return number row of table from selecting
/// </summary>
/// <param name="tableName"></param>
/// <param name="whereClause"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks></remarks>
public static int GetNumberRowInTable(string tableName, string whereClause, ref string errMsg)
{
string strSQL = null;
DataTable temp = null;
// with where
if ((whereClause.Trim().Length > 0))
{
strSQL = " select count(*) AS numberRow from " + tableName + " where " + whereClause;
// without where
}
else
{
strSQL = " select count(*) AS numberRow from " + tableName;
}
//===============
temp = SQLExecute.LoadDataFromDB(strSQL, tableName, ref errMsg);
if ((string.IsNullOrEmpty(errMsg)))
{
return(Convert.ToInt32(temp.Rows[0][0]));
}
else
{
return(-1);
// error
}
return(-1);
// error
}
public int save_Size(Size vsize, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[4];
prms[i] = new SqlParameter("@MaSize", vsize.Masize);
i = i + 1;
prms[i] = new SqlParameter("@Size", vsize.Size1);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("[dbo].[sp_save_Size]", ref prms, 3, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
public int update_soluong_sp(int _masp, int _ma_size, int _soluong, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[5];
prms[i] = new SqlParameter("@MaSP", _masp);
i = i + 1;
prms[i] = new SqlParameter("@MaSize", _ma_size);
i = i + 1;
prms[i] = new SqlParameter("@SoLuong", _soluong);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("[dbo].[sp_update_soluong_sp]", ref prms, 4, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
public int Login_khachhang(bool _laadmin, string _username, string _password, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[5];
prms[i] = new SqlParameter("@laadmin", _laadmin);
i = i + 1;
prms[i] = new SqlParameter("@username", _username);
i = i + 1;
prms[i] = new SqlParameter("@password", _password);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("dbo.sp_login_khachhang", ref prms, 4, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
public hoadon getby_hoadon(string _mahodon, ref string errMsg)
{
try
{
string sqlstr = "select * from hoadon where mahoadon like N'" + _mahodon + "'";
IDataReader dr = SQLExecute.getIDataReader(sqlstr, ref errMsg);
hoadon obj = new hoadon();
obj = (hoadon)CBO.FillObject(dr, typeof(hoadon));
return(obj);
}
catch (Exception ex)
{
throw ex;
}
}
public Entities.SanPham getby_sanpham(string _masanpham, ref string errMsg)
{
try
{
string sqlstr = "select * from sanpham where MaSP like N'" + _masanpham + "'";
IDataReader dr = SQLExecute.getIDataReader(sqlstr, ref errMsg);
Entities.SanPham obj = new Entities.SanPham();
obj = (Entities.SanPham)CBO.FillObject(dr, typeof(Entities.SanPham));
return(obj);
}
catch (Exception ex)
{
throw ex;
}
}
public Size getby_size_ID(string _ma_size, ref string errMsg)
{
try
{
string sqlstr = "select * from Size where MaSize like N'" + _ma_size + "'";
IDataReader dr = SQLExecute.getIDataReader(sqlstr, ref errMsg);
Size obj = new Size();
obj = (Size)CBO.FillObject(dr, typeof(Size));
return(obj);
}
catch (Exception ex)
{
throw ex;
}
}
public SanPham_Size getby_sanpham_size(int _masp, int _ma_size, ref string errMsg)
{
try
{
string sqlstr = "select * from SanPham_Size where MaSize = " + _ma_size + "and MaSP =" + _masp;
IDataReader dr = SQLExecute.getIDataReader(sqlstr, ref errMsg);
SanPham_Size obj = new SanPham_Size();
obj = (SanPham_Size)CBO.FillObject(dr, typeof(SanPham_Size));
return(obj);
}
catch (Exception ex)
{
throw ex;
}
}
public KhachHang getby_tk_khachhang(string _tentaikhoan, ref string errMsg)
{
try
{
string sqlstr = "select * from khachhang where tendangnhap like N'" + _tentaikhoan + "'";
IDataReader dr = SQLExecute.getIDataReader(sqlstr, ref errMsg);
KhachHang obj = new KhachHang();
obj = (KhachHang)CBO.FillObject(dr, typeof(KhachHang));
return(obj);
}
catch (Exception ex)
{
throw ex;
}
}
public SanPham getby_sanpham_ID(string _ma_sp, ref string errMsg)
{
try
{
string sqlstr = "select * from [dbo].[SanPham] where [MaSP] like N'" + _ma_sp + "'";
IDataReader dr = SQLExecute.getIDataReader(sqlstr, ref errMsg);
SanPham obj = new SanPham();
obj = (SanPham)CBO.FillObject(dr, typeof(SanPham));
return(obj);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable getall_chitiet_hd(string _mahoadon, ref string errMsg)
{
try
{
SqlParameter[] prms = new SqlParameter[1];
prms[0] = new System.Data.SqlClient.SqlParameter("@mahoadon", _mahoadon);
DataTable dtTon_VatTu = null;
dtTon_VatTu = SQLExecute.LoadDataFromDB("[dbo].[sp_getall_chitiet_hd]", "tmpData", ref errMsg, CommandType.StoredProcedure, prms, 1);
return(dtTon_VatTu);
}
catch (Exception ex)
{
errMsg = ex.Message;
throw ex;
}
}
public int sp_save_sanpham(int _masp, string _tensp, decimal _giamua, decimal _giaban, int _loaisp, int _chude, string _thongtin, int _gioitinh, DateTime _ngaynhap, string _hinhanh, int _masize, int _soluong, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[14];
prms[i] = new SqlParameter("@MaSP", _masp);
i = i + 1;
prms[i] = new SqlParameter("@TenSP", _tensp);
i = i + 1;
prms[i] = new SqlParameter("@GiaMua", _giamua);
i = i + 1;
prms[i] = new SqlParameter("@GiaBan", _giaban);
i = i + 1;
prms[i] = new SqlParameter("@LoaiSP", _loaisp);
i = i + 1;
prms[i] = new SqlParameter("@ChuDe", _chude);
i = i + 1;
prms[i] = new SqlParameter("@ThongTin", _thongtin);
i = i + 1;
prms[i] = new SqlParameter("@GioiTinh", _gioitinh);
i = i + 1;
prms[i] = new SqlParameter("@NgayNhapHang", _ngaynhap);
i = i + 1;
prms[i] = new SqlParameter("@HinhAnh", _hinhanh);
i = i + 1;
prms[i] = new SqlParameter("@MaSize", _masize);
i = i + 1;
prms[i] = new SqlParameter("@SoLuong", _soluong);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("[dbo].[sp_save_sanpham]", ref prms, 13, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
public int save_khachhang(KhachHang vkhachhang, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[12];
prms[i] = new SqlParameter("@khachhangid", vkhachhang.MaKH);
i = i + 1;
prms[i] = new SqlParameter("@tendangnhap", vkhachhang.Tendangnhap);
i = i + 1;
prms[i] = new SqlParameter("@matkhau", vkhachhang.Matkhau);
i = i + 1;
prms[i] = new SqlParameter("@hoten", vkhachhang.Hoten);
i = i + 1;
prms[i] = new SqlParameter("@gioitinh", vkhachhang.Gioitinh);
i = i + 1;
prms[i] = new SqlParameter("@diachi", vkhachhang.Diachi);
i = i + 1;
prms[i] = new SqlParameter("@emai", vkhachhang.Email);
i = i + 1;
prms[i] = new SqlParameter("@sodienthoai", vkhachhang.Sodienthoai);
i = i + 1;
prms[i] = new SqlParameter("@laadmin", vkhachhang.Laadmin);
i = i + 1;
prms[i] = new SqlParameter("@trangthai", vkhachhang.Trangthai);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("dbo.sp_save_khachhang", ref prms, 11, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
/// <summary>
/// return : true if exist data when execute sql string
/// return false if not exist
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
/// <remarks></remarks>
public static bool CheckExistDataBySQL_clause(string strSQL)
{
try
{
string errMsg = string.Empty;
DataTable dt = SQLExecute.LoadDataFromDB(strSQL, "tem", ref errMsg);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
}
public int save_tintuc(tintuc vtintuc, ref string errMsg)
{
try
{
errMsg = "";
int i = 0;
int result = 0;
SqlParameter[] prms = new SqlParameter[11];
prms[i] = new SqlParameter("@matintuc", vtintuc.Matintuc);
i = i + 1;
prms[i] = new SqlParameter("@teude", vtintuc.Teude);
i = i + 1;
prms[i] = new SqlParameter("@mota", vtintuc.Mota);
i = i + 1;
prms[i] = new SqlParameter("@noidung", vtintuc.Noidung);
i = i + 1;
prms[i] = new SqlParameter("@anh", vtintuc.Anh);
i = i + 1;
prms[i] = new SqlParameter("@nguoidang", vtintuc.Nguoidang);
i = i + 1;
prms[i] = new SqlParameter("@nguoitao", vtintuc.Nguoitao);
i = i + 1;
prms[i] = new SqlParameter("@trangthai", vtintuc.Trangthai);
i = i + 1;
prms[i] = new SqlParameter("@ngaydang", vtintuc.Ngaydang);
i = i + 1;
prms[i] = new SqlParameter("@outreturn", SqlDbType.Int, 1);
prms[i].Direction = ParameterDirection.Output;
result = SQLExecute.ExcuteSPOutput("[dbo].[sp_save_tintuc]", ref prms, 10, ref errMsg, "@outreturn");
return(result);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(0);
}
}
/// <summary>
/// ' get data from table by where caluse....
/// return : datatable type
/// </summary>
/// <param name="tableName"></param>
/// <param name="arrColsName"></param>
/// <param name="whereClause"></param>
/// <param name="orderBy"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks></remarks>
///
public static DataTable GetDataFromTable(string tableName, Array arrColsName, string whereClause, string orderBy, ref string errMsg)
{
string strSQL = null;
int arrSize;
int index;
string strCols = null;
DataTable temp = null;
arrSize = arrColsName.Length;
// with where
if ((whereClause.Trim().Length > 0))
{
// with order
if ((orderBy.Trim().Length > 0))
{
if (arrSize == 1)
{
strSQL = " select " + arrColsName.GetValue(0).ToString() + " from " + tableName + " where " + whereClause + " order by " + orderBy;
}
else
{
strCols = string.Empty;
for (index = 0; index <= arrSize - 2; index++)
{
//strCols = strCols + arrColsName(index).ToString() + ",";
strCols = strCols + arrColsName.GetValue(index).ToString() + ",";
}
//strCols = strCols + arrColsName(arrSize - 1).ToString();
strCols = strCols + arrColsName.GetValue(arrSize - 1).ToString();
strSQL = " select " + strCols + " from " + tableName + " where " + whereClause + " order by " + orderBy;
}
// without order
}
else
{
if (arrSize == 1)
{
strSQL = " select " + arrColsName.GetValue(0).ToString() + " from " + tableName + " where " + whereClause;
}
else
{
strCols = string.Empty;
for (index = 0; index <= arrSize - 2; index++)
{
strCols = strCols + arrColsName.GetValue(index).ToString() + ",";
}
strCols = strCols + arrColsName.GetValue(arrSize - 1).ToString();
strSQL = " select " + strCols + " from " + tableName + " where " + whereClause;
}
}
// without where
}
else
{
// with order by
if ((orderBy.Trim().Length > 0))
{
if (arrSize == 1)
{
strSQL = " select " + arrColsName.GetValue(0).ToString() + " from " + tableName + " order by " + orderBy;
}
else
{
strCols = string.Empty;
for (index = 0; index <= arrSize - 2; index++)
{
strCols = strCols + arrColsName.GetValue(index).ToString() + ",";
}
strCols = strCols + arrColsName.GetValue(arrSize - 1).ToString();
strSQL = " select " + strCols + " from " + tableName + " order by " + orderBy;
}
// without order by
}
else
{
if (arrSize == 1)
{
strSQL = " select " + arrColsName.GetValue(0).ToString() + " from " + tableName;
}
else
{
strCols = string.Empty;
for (index = 0; index <= arrSize - 2; index++)
{
strCols = strCols + arrColsName.GetValue(index).ToString() + ",";
}
strCols = strCols + arrColsName.GetValue(arrSize - 1).ToString();
strSQL = " select " + strCols + " from " + tableName;
}
}
}
//===============
temp = SQLExecute.LoadDataFromDB(strSQL, tableName, ref errMsg);
return(temp);
}
/// <summary>
/// Dung de get 1 gia tri kieu datetime trong 1 bang
/// Chu y : Truong du lieu lay ra phai la kieu datetime
/// </summary>
/// <param name="tableName"></param>
/// <param name="colName_ToGetValue"></param>
/// <param name="whereClause"></param>
/// <param name="orderBy"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks></remarks>
public static Nullable <DateTime> GetDateValueFromTable(string tableName, string colName_ToGetValue, string whereClause, string orderBy, ref string errMsg)
{
string strSQL = null;
Int32 arrSize = default(Int32);
Int32 index = default(Int32);
string strCols = null;
DataTable temp = null;
// with where
if ((whereClause.Trim().Length > 0))
{
// with order
if ((orderBy.Trim().Length > 0))
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " where " + whereClause + " order by " + orderBy;
// without order
}
else
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " where " + whereClause;
}
// without where
}
else
{
// with order by
if ((orderBy.Trim().Length > 0))
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " order by " + orderBy;
// without order by
}
else
{
strSQL = " select " + colName_ToGetValue + " from " + tableName;
}
}
temp = SQLExecute.LoadDataFromDB(strSQL, tableName, ref errMsg);
if ((temp.Rows.Count > 0))
{
errMsg = "";
if (Convert.IsDBNull(temp.Rows[0][colName_ToGetValue]))
{
return(null);
}
else
{
return(DateTime.Parse(temp.Rows[0][colName_ToGetValue].ToString()));
}
}
else
{
errMsg = "No data found !";
}
return(null);
}
/// <summary>
/// Get list of value from Table
/// </summary>
/// <param name="tableName"></param>
/// <param name="colName_ToGetValue"></param>
/// <param name="whereClause"></param>
/// <param name="orderBy"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks></remarks>
public static List <string> GetListOf_ValuesFromTable(string tableName, string colName_ToGetValue, string whereClause, string orderBy, ref string errMsg)
{
string strSQL = null;
Int32 arrSize = default(Int32);
Int32 index = default(Int32);
string strCols = null;
DataTable temp = null;
try
{
// with where
if ((whereClause.Trim().Length > 0))
{
// with order
if ((orderBy.Trim().Length > 0))
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " where " + whereClause + " order by " + orderBy;
// without order
}
else
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " where " + whereClause;
}
// without where
}
else
{
// with order by
if ((orderBy.Trim().Length > 0))
{
strSQL = " select " + colName_ToGetValue + " from " + tableName + " order by " + orderBy;
// without order by
}
else
{
strSQL = " select " + colName_ToGetValue + " from " + tableName;
}
}
//===============
temp = SQLExecute.LoadDataFromDB(strSQL, tableName, ref errMsg);
if ((temp.Rows.Count > 0))
{
errMsg = "";
List <string> lstValue = new List <string>();
int i = 0;
for (i = 0; i <= temp.Rows.Count - 1; i++)
{
lstValue.Add(temp.Rows[i][colName_ToGetValue].ToString());
}
return(lstValue);
}
else
{
errMsg = "No data found";
}
return(null);
}
catch (Exception ex)
{
return(null);
}
}