Ejemplo n.º 1
0
            public static dynamic DataSource(AutoCompOptions options, string query, string orderBy)
            {
                var connection = new CommonConnection();

                try
                {
                    query = query.Replace(';', ' ');

                    string sqlQuery = GridQueryBuilder <T> .Query(options, query, orderBy, "");


                    DataTable dataTable = connection.GetDataTable(sqlQuery);

                    var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);


                    return(dataList);
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
Ejemplo n.º 2
0
            private static void GetGridPagingQuery(GridOptions options, string query, string orderBy, string condition,
                                                   out StringBuilder gridQuery, out StringBuilder totalQuery, DatabaseType databaseType)
            {
                //string sql = "SELECT * FROM " + tableName;
                // var _connection = new CommonConnection();
                try
                {
                    gridQuery  = new StringBuilder();
                    totalQuery = new StringBuilder();

                    query = query.Replace(';', ' ');

                    string strQuery = options != null
                        ? GridQueryBuilder <T> .Query(options, query, orderBy, condition)
                        : query;

                    if (!string.IsNullOrEmpty(condition))
                    {
                        condition = " WHERE " + condition;
                    }

                    var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : "";

                    if (!string.IsNullOrEmpty(condition1))
                    {
                        if (!string.IsNullOrEmpty(condition))
                        {
                            condition += " And " + condition1;
                        }
                        else
                        {
                            condition = " WHERE " + condition1;
                        }
                    }
                    string tQuery = "";
                    if (databaseType == DatabaseType.SQL)
                    {
                        tQuery = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
                    }
                    else if (databaseType == DatabaseType.Oracle)
                    {
                        tQuery = "SELECT COUNT(*) FROM (" + query + " )" + condition;
                    }

                    gridQuery.Append(strQuery);
                    totalQuery.Append(tQuery);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    //_connection.Close();
                }
            }
Ejemplo n.º 3
0
            public static GridEntity <T> DataSource(GridOptions options, string query, string orderBy, string condition)
            {
                var _connection = new CommonConnection();

                try
                {
                    query = query.Replace(';', ' ');
                    string orderby  = "";
                    string sqlQuery = query;
                    if (options != null)
                    {
                        if (options.pageSize > 0)
                        {
                            sqlQuery = GridQueryBuilder <T> .Query(options, query, orderBy, condition);
                        }
                        else
                        {
                            if (orderBy != "")
                            {
                                if (orderBy.ToLower().Contains("asc") || orderBy.ToLower().Contains("desc"))
                                {
                                    orderby = string.Format(" order by {0}", orderBy);
                                }
                                else
                                {
                                    orderby = string.Format(" order by {0} asc ", orderBy);
                                }
                            }
                        }
                    }
                    else
                    {
                        if (orderBy != "")
                        {
                            if (orderBy.ToLower().Contains("asc") || orderBy.ToLower().Contains("desc"))
                            {
                                orderby = string.Format(" order by {0}", orderBy);
                            }
                            else
                            {
                                orderby = string.Format(" order by {0} asc ", orderBy);
                            }
                        }
                    }

                    if (!string.IsNullOrEmpty(condition))
                    {
                        condition = " WHERE " + condition;
                    }

                    var condition1 = "";
                    if (options != null)
                    {
                        if (options.filter != null)
                        {
                            condition1 = GridQueryBuilder <T> .FilterCondition(options.filter).Trim();
                        }
                    }
                    if (!string.IsNullOrEmpty(condition1))
                    {
                        if (!string.IsNullOrEmpty(condition))
                        {
                            condition += " And " + condition1;
                        }
                        else
                        {
                            condition = " WHERE " + condition1;
                        }
                    }
                    sqlQuery = "SELECT * FROM (" + sqlQuery + " ) As tbl " + condition;

                    DataTable dataTable = _connection.GetDataTable(sqlQuery + orderby);

                    String sqlCount = "";
                    if (_connection.DatabaseType == DatabaseType.SQL)
                    {
                        sqlCount = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
                    }
                    else if (_connection.DatabaseType == DatabaseType.Oracle)
                    {
                        sqlCount = "SELECT COUNT(*) FROM (" + query + " )" + condition;
                    }

                    int totalCount = _connection.GetScaler(sqlCount);
                    var dataList   = (List <T>)ListConversion.ConvertTo <T>(dataTable);
                    var result     = new GridResult <T>().Data(dataList, totalCount);


                    return(result);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    _connection.Close();
                }
            }
Ejemplo n.º 4
0
            public static DataSet GetDataSet(GridOptions options, string query, string orderBy)
            {
                //string sql = "SELECT * FROM " + tableName;
                DataSet gridDataSet = new DataSet();
                var     _connection = new CommonConnection();
                string  condition   = "";

                try
                {
                    query = query.Replace(';', ' ');

                    string sqlQuery = options != null
                        ? GridQueryBuilder <T> .Query(options, query, orderBy, condition)
                        : query;

                    if (!string.IsNullOrEmpty(condition))
                    {
                        condition = " WHERE " + condition;
                    }

                    var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : "";

                    if (!string.IsNullOrEmpty(condition1))
                    {
                        if (!string.IsNullOrEmpty(condition))
                        {
                            condition += " And " + condition1;
                        }
                        else
                        {
                            condition = " WHERE " + condition1;
                        }
                    }

                    DataTable dataTable = _connection.GetDataTable(sqlQuery);
                    gridDataSet.Tables.Add(dataTable);
                    String sqlCount = "";
                    if (_connection.DatabaseType == DatabaseType.SQL)
                    {
                        sqlCount = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
                    }
                    else if (_connection.DatabaseType == DatabaseType.Oracle)
                    {
                        sqlCount = "SELECT COUNT(*) FROM (" + query + " )" + condition;
                    }

                    int        totalCount   = _connection.GetScaler(sqlCount);
                    DataTable  totalCountDt = new DataTable("TotalCount");
                    DataColumn col          = new DataColumn("totalCount");
                    col.DataType = Type.GetType("System.Int32");
                    totalCountDt.Columns.Add(col);
                    DataRow dr = totalCountDt.NewRow();
                    dr["totalCount"] = totalCount;
                    totalCountDt.Rows.Add(dr);

                    //var dataList = (List<T>)ListConversion.ConvertTo<T>(dataTable);
                    //var result = new GridResult<T>().Data(dataList, totalCount);
                    gridDataSet.Tables.Add(totalCountDt);


                    return(gridDataSet);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    _connection.Close();
                }
            }
Ejemplo n.º 5
0
            public static GridEntity <T> DataSourceWithDateQuary(GridOptions options, string query, string orderBy,
                                                                 string condition, string withDateQuary)
            {
                //string sql = "SELECT * FROM " + tableName;
                var _connection = new CommonConnection();

                try
                {
                    query = query.Replace(';', ' ');

                    string sqlQuery = options != null
                        ? GridQueryBuilder <T> .Query(options, query, orderBy, condition)
                        : query;

                    if (!string.IsNullOrEmpty(condition))
                    {
                        condition = " WHERE " + condition;
                    }

                    var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : "";

                    if (!string.IsNullOrEmpty(condition1))
                    {
                        if (!string.IsNullOrEmpty(condition))
                        {
                            condition += " And " + condition1;
                        }
                        else
                        {
                            condition = " WHERE " + condition1;
                        }
                    }

                    if (withDateQuary != "")
                    {
                        sqlQuery = withDateQuary + sqlQuery;
                    }

                    DataTable dataTable = _connection.GetDataTable(sqlQuery);

                    String sqlCount = "";
                    if (_connection.DatabaseType == DatabaseType.SQL)
                    {
                        sqlCount = withDateQuary + " SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
                    }
                    else if (_connection.DatabaseType == DatabaseType.Oracle)
                    {
                        sqlCount = withDateQuary + " SELECT COUNT(*) FROM (" + query + " )" + condition;
                    }

                    int totalCount = _connection.GetScaler(sqlCount);

                    var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);
                    var result   = new GridResult <T>().Data(dataList, totalCount);


                    return(result);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    _connection.Close();
                }
            }