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