Пример #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();
                }
            }
Пример #2
0
            public static List <T> DataSource(string query)
            {
                var returnList = new List <T>();
                var connection = new CommonConnection();

                try
                {
                    DataTable dataTable = connection.GetDataTable(query);

                    var data = (List <T>)ListConversion.ConvertTo <T>(dataTable);
                    if (data != null)
                    {
                        return(data);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
                return(returnList);
            }
        private static int GetVoucherLastNo(string prifix, DateTime vDate)
        {
            string sql  = string.Format(@"Select  isnull(MAX(Number),0)Number from (
            Select VoucharNo, substring(VoucharNo,1,2)Prefix,
            substring(VoucharNo,4,6)MonthYear,
            Convert(int,substring(VoucharNo,11,6))Number ,
            substring(VoucharNo,11,6)IncNumber,
            convert(date,substring(VoucharNo,4,4)+'-'+substring(VoucharNo,8,2)+'-01')vDate
            from VoucharMaster) T where vDate='{1}'
            and Prefix='{0}' ", prifix, vDate.ToString("yyyy-MM-01"));
            var    conn = new CommonConnection();
            var    lNo  = conn.GetScaler(sql);

            conn.Close();
            return(lNo);
        }
Пример #4
0
        public static DataTable DataTable(string query)
        {
            var connection = new CommonConnection();

            try
            {
                DataTable dataTable = connection.GetDataTable(query);

                return(dataTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Пример #5
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();
                }
            }
Пример #6
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();
                }
            }
Пример #7
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();
                }
            }