public static string GetSelectColumnSql(this ISelectProperties selectProperties, int id = 0, params string[] properties)
{
StringBuilder columnNames = new StringBuilder();
if (properties != null && properties.Length > 0)
{
foreach (var property in properties)
{
columnNames.Append(property + ",");
}
}
else
{
var propertiesAll = selectProperties.GetType().GetProperties();
if (propertiesAll != null)
{
foreach (var property in propertiesAll)
{
if (property.IgnoreSelectProperty(selectProperties, id) || property.IsPropertyUsedByFrameWork())
{
continue;
}
string columnName = property.GetPropertyAliasName(id);
columnNames.Append(columnName + ",");
}
}
}
if (columnNames.Length > 1)
{
columnNames.Length = columnNames.Length - 1;
}
return(columnNames.ToString());
}
public static string GetInSqlServerPageListSelectSql(this ISelectProperties selectProperties, int id = 0, string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
string result = string.Empty;
StringBuilder builder = new StringBuilder();
var selectColumnNames = GetSelectColumnSql(selectProperties, id, properties);
var headersAndFooters = selectProperties.GetStatisticHeaderAndFooterSql(id);
string header = string.Empty; string footer = string.Empty;
if (headersAndFooters.ContainsKey(StatisticsType.Header))
{
header = headersAndFooters[StatisticsType.Header];
}
if (headersAndFooters.ContainsKey(StatisticsType.Footer))
{
footer = headersAndFooters[StatisticsType.Footer];
}
var tableName = selectProperties.GetTableName(tableIndex);
var collectionLimit = selectProperties.GetSelectCollectionLimit(id);
var row_number = string.Format(PageListSql.PageListSqlServer1_0, string.IsNullOrEmpty(footer) ? " order by getdate() " : footer);
var row_numberColumn = string.Format(PageListSql.PageListSqlServer1, row_number);
builder.AppendLine(string.Format("Select {3} {0} {1} From {2} ", collectionLimit, selectColumnNames.ToString(), tableName, row_numberColumn));
var matchedKeys = selectProperties.GetInMatchedKeyNameAndValues(id, true, listsIn?.ToArray());
builder.AppendJoin(" ", matchedKeys);
builder.AppendLine(sqlAppend);
builder.Append(footer);
result = builder.ToString();
//result = string.Format(PageListSql.PageListSqlServer, PageListSql.PageListSqlServerTopSize0, innerSql);
return(result);
}
public static long GetTotalCount(this ISelectProperties selectProperties, IDbConnection dbConnection, int id = 0,
string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
string totalSql = selectProperties.GetCountSql(id, tableIndex, listsIn, sqlAppend, properties);
string sTotal = dbConnection.ExecuteScalar(totalSql, selectProperties)?.ToString();
long total;
long.TryParse(sTotal, out total);
return(total);
}
public static PageList <T> SelectPageListPropertiesByKey <T>(this ISelectProperties selectProperties, int pageIndex, int pageSize, int id = 0,
string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
try
{
if (selectProperties == null)
{
return(default(PageList <T>));
}
string sqlCount = string.Empty;
using (IDbConnection dbConnection = selectProperties.GetDBConnection(id))
{
dbConnection.Open();
var dbType = selectProperties.GetDBServerType(id);
string sql = string.Empty;
if (dbType == DBServerType.MySql)
{
string sqlPageLimit = string.Format(" Limit {0},{1}", (pageIndex - 1) * pageSize, pageSize);
sql = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
sql += sqlPageLimit;
}
else if (dbType == DBServerType.Oracle)
{
string sqlPrepare = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
sql = string.Format(PageListSql.PageListOracle, sqlPrepare, (pageIndex * pageSize).ToString(), ((pageIndex - 1) * pageSize).ToString());
}
else if (dbType == DBServerType.SqlServer)
{
string sqlPrepare = selectProperties.GetInSqlServerPageListSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
int iOrderBy = sqlPrepare.LastIndexOf("Order By");
string orderBySql = string.Empty;
if (iOrderBy > 0)
{
orderBySql = sqlPrepare.Substring(iOrderBy);
sqlPrepare = sqlPrepare.Substring(0, iOrderBy);
}
sql = string.Format(PageListSql.PageListSqlServer, pageSize.ToString(), sqlPrepare, pageIndex.ToString()) + " " + orderBySql;
}
long total = GetTotalCount(selectProperties, dbConnection, id, tableIndex, listsIn, sqlAppend, properties);
var list = dbConnection.Query <T>(sql, selectProperties).ToList();
PageList <T> result = new PageList <T> {
ListItems = list, PageIndex = pageIndex, PageSize = pageSize, TotalCount = total, TotalPage = total / pageSize + (total % pageSize == 0 ? 0 : 1)
};
return(result);
}
}
catch (Exception ex)
{ throw ex; }
}
public static string GetCountSql(this ISelectProperties selectProperties, int id = 0, string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
string result = string.Empty;
StringBuilder builder = new StringBuilder();
var selectColumnNames = "count(1)";
var tableName = selectProperties.GetTableName(tableIndex);
builder.AppendLine(string.Format("Select {0} From {1} ", selectColumnNames.ToString(), tableName));
var matchedKeys = selectProperties.GetInMatchedKeyNameAndValues(id, true, listsIn?.ToArray());
builder.AppendJoin(" ", matchedKeys);
builder.AppendLine(sqlAppend);
result = builder.ToString();
return(result);
}
public static string GetSelectSql(this ISelectProperties selectProperties, int id = 0, string tableIndex = null, string sqlAppend = "", params string[] properties)
{
string result = string.Empty;
StringBuilder builder = new StringBuilder();
var selectColumnNames = GetSelectColumnSql(selectProperties, id, properties);
var tableName = selectProperties.GetTableName(tableIndex);
var collectionLimit = selectProperties.GetSelectCollectionLimit(id);
builder.AppendLine(string.Format("Select {0} {1} From {2} ", collectionLimit, selectColumnNames.ToString(), tableName));
var matchedKeys = selectProperties.GetMatchedKeyNameAndValues(id);
builder.AppendJoin(" ", matchedKeys);
builder.AppendLine(sqlAppend);
result = builder.ToString();
return(result);
}
public static string GetSelectCollectionLimit(this ISelectProperties selectProperties, int id = 0)
{
string result = string.Empty;
if (selectProperties == null)
{
return(result);
}
var selectAttribute = selectProperties.GetCustomerAttributes <SelectAttribute>()?.Find(w => w.ID == id);
if (selectAttribute != null)
{
result = selectAttribute.CollectionLimit;
}
return(result);
}
public static List <T> SelectPropertiesExists <T>(this ISelectProperties selectProperties, int id = MatchedID.SelectExists, string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
try
{
if (selectProperties == null)
{
return(default(List <T>));
}
using (IDbConnection dbConnection = selectProperties.GetDBConnection(id))
{
dbConnection.Open();
var sql = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
//Logger.Info(sql);
return(dbConnection.Query <T>(sql, selectProperties).ToList());
}
}
catch (Exception ex)
{ throw ex; }
}
public static List <T> SelectPropertiesBySql <T>(this ISelectProperties selectProperties, string sql, bool needParameters = false, int id = MatchedID.SelectBySql)
{
try
{
if (selectProperties == null)
{
return(default(List <T>));
}
using (IDbConnection dbConnection = selectProperties.GetDBConnection(id))
{
dbConnection.Open();
if (needParameters)
{
return(dbConnection.Query <T>(sql, selectProperties).ToList());
}
else
{
return(dbConnection.Query <T>(sql).ToList());
}
}
}
catch (Exception ex)
{ throw ex; }
}
public static List <T> SelectPropertiesByKey <T>(this ISelectProperties selectProperties, int id = 0, string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
try
{
if (selectProperties == null)
{
return(default(List <T>));
}
string connectionKey = selectProperties.GetConnectionKey();
string connectionString = AppSetting.GetConfig(connectionKey);
using (IDbConnection dbConnection = selectProperties.GetDBConnection(id))
{
dbConnection.Open();
var sql = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
//Logger.Info(sql);
//if (listsIn == null)
return(dbConnection.Query <T>(sql, selectProperties).ToList());
//else
// return dbConnection.Query<T>(sql).ToList();
}
}
catch (Exception ex)
{ throw ex; }
}
public static Dictionary <StatisticsType, Dictionary <string, List <StatisticsParameter> > > GetHeadersAndFooters(this ISelectProperties statisticsProperties, int id = MatchedID.Statistics)
{
Dictionary <StatisticsType, Dictionary <string, List <StatisticsParameter> > > result = new Dictionary <StatisticsType, Dictionary <string, List <StatisticsParameter> > >();
var propertiesAll = statisticsProperties.GetType().GetProperties();
if (propertiesAll != null)
{
foreach (var property in propertiesAll)
{
if (property == null || property.IsPropertyUsedByFrameWork())
{
continue;
}
var statisticsAll = property.GetCustomAttributes(typeof(StatisticsAttribute), true)?.Select(w => (StatisticsAttribute)w).Where(w => w.ID == id);
foreach (var statistics in statisticsAll)
{
if (statistics != null)
{
StatisticsType statisticsType = statistics.SType;
string column = statistics.Column;
string command = statistics.Command;
var extra = statistics.CommandExtra;
var sort = statistics.Sort;
if (!result.ContainsKey(statisticsType))
{
result.Add(statisticsType, new Dictionary <string, List <StatisticsParameter> >());
}
if (result.ContainsKey(statisticsType))
{
var valueKey = result[statisticsType];
var columnAliasName = property.Name;
if (string.IsNullOrEmpty(column))
{
column = property.Name;
}
if (valueKey == null || !valueKey.ContainsKey(command))
{
result[statisticsType].Add(command, new List <StatisticsParameter> {
new StatisticsParameter {
CommandText = column + " " + extra, ColumnAliasName = columnAliasName, Sort = sort
}
});
}
else
{
result[statisticsType][command].Add(new StatisticsParameter
{
CommandText = column + " " + extra,
ColumnAliasName = columnAliasName,
Sort = sort
});
}
}
}
}
}
}
return(result);
}
public static Dictionary <StatisticsType, string> GetStatisticHeaderAndFooterSql(this ISelectProperties statisticsProperties, int id = MatchedID.Statistics)
{
Dictionary <StatisticsType, string> result = new Dictionary <StatisticsType, string>();
string headers = string.Empty;
string footers = string.Empty;
var headersAndFooters = statisticsProperties.GetHeadersAndFooters(id);
if (headersAndFooters == null)
{
return(result);
}
result.Add(StatisticsType.Header, GetHeaders(headersAndFooters));
result.Add(StatisticsType.Footer, GetFooters(headersAndFooters));
return(result);
}
