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