Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        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);
        }