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); }
public static GridEntity <T> GenericDataSource(GridOptions options, string query, string orderBy, string condition) { var _connection = new CommonConnection(); StringBuilder gridQuery; StringBuilder totalQuery; GetGridPagingQuery(options, query, orderBy, condition, out gridQuery, out totalQuery, _connection.DatabaseType); DataTable dataTable = _connection.GetDataTable(gridQuery.ToString()); int totalCount = _connection.GetScaler(totalQuery.ToString()); var dataList = (List <T>)GenericListGenerator.GetList <T>(dataTable); var result = new GridResult <T>().Data(dataList, totalCount); return(result); }
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(); } }
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(); } }
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(); } }