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