public SqlDataReader ExecuteReaderQuery(TTDictionaryQuery SQLDictionery, object objEntity) { SqlDataReader drResult = null; try { GetConnection(); if (!MySqlConnection.GetConnection.isConnectionOpen) { sqlCon.Open(); } sqlCMD.Parameters.Clear(); StringBuilder SQLQuery = new StringBuilder(); SQLQuery.Append(SQLDictionery.SelectPart).AppendLine(); SQLQuery.Append(SQLDictionery.TablePart).AppendLine(); if (SQLDictionery.GroupPart.Trim() != "") { SQLQuery.Append(SQLDictionery.GroupPart).AppendLine(); } if (SQLDictionery.HavingPart.Trim() != "") { SQLQuery.Append(SQLDictionery.HavingPart).AppendLine(); } if (TTPagination.isPageing) { SQLQuery.Append(string.Format("{0} {1}", SQLDictionery.OrderPart, HelperMethod.GetPageingString(SQLDictionery.OrderPart, objEntity))).AppendLine(); } else { SQLQuery.Append(SQLDictionery.OrderPart).AppendLine(); } sqlCMD.CommandType = CommandType.Text; sqlCMD.CommandText = SQLQuery.ToString(); SqlDataReader dr = sqlCMD.ExecuteReader(); if (dr.HasRows) { drResult = dr; TTPagination.RecordCount = PagerRecordCount(SQLDictionery, null, objEntity); } } catch (Exception) { throw; } finally { if (sqlCon != null) { if (sqlCon.State == ConnectionState.Open) { sqlCon.Close(); MySqlConnection.GetConnection.CloseConnection(sqlCon); } } } return(drResult); }
private long PagerRecordCount(TTDictionaryQuery SQLDictionery, TTDictionary parDictionery, object objEntity) { long lngResult = 0; StringBuilder sbCount = new StringBuilder(); SqlCommand sqlCMDCount = new SqlCommand(); sqlCMDCount.Connection = MySqlConnection.GetConnection.GetDBConnection(); sbCount.Append("select total_count = COUNT(*) ").AppendLine(); sbCount.Append(SQLDictionery.TablePart).AppendLine(); if (parDictionery != null) { if (parDictionery.Count > 0) { sqlCMDCount.Parameters.Clear(); CommonMSSQL.AddParameter(sqlCMDCount, parDictionery, objEntity); sbCount.Append(HelperMethod.GetParameter(parDictionery, sbCount)).AppendLine(); if (SQLDictionery.ParameterPart != null) { sbCount.Append(SQLDictionery.ParameterPart.Replace("WHERE", "AND")); } } else { sbCount.Append(SQLDictionery.ParameterPart); } } else { sbCount.Append(SQLDictionery.ParameterPart); } if (SQLDictionery.GroupPart != null) { sbCount.Append(SQLDictionery.GroupPart).AppendLine(); } if (SQLDictionery.HavingPart != null) { sbCount.Append(SQLDictionery.HavingPart).AppendLine(); } sqlCMDCount.CommandText = sbCount.ToString(); object objCount = sqlCMDCount.ExecuteScalar(); if (objCount != null) { lngResult = objCount == DBNull.Value ? 0 : Convert.ToInt64(objCount); TTPagination.PageCount = (long)Math.Ceiling((double)lngResult / TTPagination.PageSize); } sqlCMDCount.Connection.Close(); sqlCMDCount.Dispose(); return(lngResult); }
public SqlDataReader ExecuteReaderQuery(TTDictionaryQuery SQLDictionery, TTDictionary parDictionery, object objEntity) { SqlDataReader drResult = null; try { GetConnection(); MySqlConnection.GetConnection.OpenConnection(sqlCon); sqlCMD.Parameters.Clear(); CommonMSSQL.AddParameter(sqlCMD, parDictionery, objEntity); StringBuilder SQLQuery = new StringBuilder(); SQLQuery.Append(SQLDictionery.SelectPart).AppendLine(); SQLQuery.Append(SQLDictionery.TablePart).AppendLine(); if (parDictionery.Count > 0) { SQLQuery.Append(HelperMethod.GetParameter(parDictionery, SQLQuery)).AppendLine(); } if (!string.IsNullOrEmpty(SQLDictionery.ParameterPart)) { if (SQLDictionery.ParameterPart.Trim() != "") { SQLQuery.Append(SQLDictionery.ParameterPart).AppendLine(); } } if (SQLDictionery.GroupPart != null) { SQLQuery.Append(SQLDictionery.GroupPart).AppendLine(); } if (SQLDictionery.HavingPart != null) { SQLQuery.Append(SQLDictionery.HavingPart).AppendLine(); } if (TTPagination.isPageing) { if (TTPagination.PageSize != -1) { SQLQuery.Append(string.Format("{0} {1}", SQLDictionery.OrderPart, HelperMethod.GetPageingString(SQLDictionery.OrderPart, objEntity))).AppendLine(); } } else { if (SQLDictionery.OrderPart != null) { SQLQuery.Append(SQLDictionery.OrderPart).AppendLine(); } } sqlCMD.CommandTimeout = 0; sqlCMD.CommandType = CommandType.Text; sqlCMD.CommandText = SQLQuery.ToString(); SqlDataReader dr = sqlCMD.ExecuteReader(); TTPagination.RecordCount = PagerRecordCount(SQLDictionery, parDictionery, objEntity); drResult = dr; } catch (Exception) { throw; } finally { TTPagination.isPageing = false; } return(drResult); }