/// <summary>
/// 得到where语句
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
internal virtual string GetWhereString(QueryPageFilter filters)
{
StringBuilder builder = new StringBuilder();
int index = 0;
if (filters.Filters != null && filters.Filters.Length > 0)
{
foreach (var item in filters.Filters)
{
if (index != 0)
{
builder.Append(" " + filters.FilterAndOrType.ToString() + " ");
}
string str = string.Empty;
if (item is QueryFilter)
{
str = GetSqlSign(item as QueryFilter);
}
if (item is AndOrQueryFilter)
{
str = GetSqlSign(item as AndOrQueryFilter);
}
builder.Append(str);
index++;
}
}
if (index == 0)
{
return("1=1");
}
return(builder.ToString());
}
public Dictionary <string, object>[] GetQueryResultN(QueryPageFilter filter)
{
string tableName1 = filter.TableName;
if (TableToTableNameDic != null && TableToTableNameDic.ContainsKey(filter.TableName))
{
tableName1 = TableToTableNameDic[filter.TableName];
}
if (!DataBaseKyFieldTableDic.ContainsKey(tableName1))
{
throw new Exception("缺少表或视图：" + tableName1 + "！请联系开发人员！");
}
filter.TableName = tableName1;
var result = GetQueryResultFromDB(filter);
if (filter.IsReturnCount != true)
{
if (result.Result != null)
{
var list = ConvertDataTable(result.Result);
return(list.ToArray());
}
}
return(null);
}
/// <summary>
/// 得到值
/// </summary>
/// <param name="fieldValue"></param>
/// <returns></returns>
internal virtual string GetFieldValue(object fieldValue)
{
if (QueryPageFilter.IsSqlFilter(fieldValue) == true)
{
throw new Exception("数据中含SQL注入，请误使用！");
}
if (fieldValue is string)
{
string str = fieldValue.ToString();
if (str.IndexOf("'") > -1)
{
str = str.Replace("'", "''");
}
return(string.Format("'{0}'", str));
}
if (fieldValue is DBNull || fieldValue is DateTime || fieldValue is Guid)
{
return(string.Format("'{0}'", fieldValue.ToString()));
}
if (fieldValue is Boolean)
{
bool t = (Boolean)fieldValue;
return(t ? 1.ToString() : 0.ToString());
}
else
{
if (fieldValue == null)
{
return(null);
}
return(fieldValue.ToString());
}
}
/// <summary>
/// 得到group by语句
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
internal virtual string GetGroupByStrings(QueryPageFilter filters)
{
string fields = string.Empty;
if (filters != null && filters.GroupByFieldNames != null && filters.GroupByFieldNames.Length > 0)
{
StringBuilder builder = new StringBuilder();
for (int i = 0; i < filters.GroupByFieldNames.Length; i++)
{
builder.Append(filters.GroupByFieldNames[i]);
if (i != filters.GroupByFieldNames.Length - 1)
{
builder.Append(",");
}
}
fields = builder.ToString();
}
var groupBy = string.Empty;
if (!string.IsNullOrEmpty(fields))
{
groupBy = "group by " + fields;
}
return(groupBy);
}
/// <summary>
/// 得到order by语句
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
internal virtual string GetOrderByString(QueryPageFilter filters)
{
string orderBy = string.Empty;
if (filters != null && filters.OrderFieldNames != null && filters.OrderFieldNames.Length > 0)
{
StringBuilder builder = new StringBuilder();
for (int i = 0; i < filters.OrderFieldNames.Length; i++)
{
builder.Append(filters.OrderFieldNames[i]);
if (filters.OrderByType == SQLOrderBy.Desc)
{
builder.Append(" desc ");
}
else
{
builder.Append(" asc ");
}
if (i != filters.OrderFieldNames.Length - 1)
{
builder.Append(",");
}
}
orderBy = " order by " + builder.ToString();
}
return(orderBy);
}
public void UpdateObjects(Dictionary <string, object> objs, QueryPageFilter filter)
{
string tableName1 = filter.TableName;
if (TableToTableNameDic != null && TableToTableNameDic.ContainsKey(filter.TableName))
{
tableName1 = TableToTableNameDic[filter.TableName];
}
if (!DataBaseKyFieldTableDic.ContainsKey(tableName1))
{
throw new Exception("缺少表或视图：" + tableName1 + "！请联系开发人员！");
}
filter.TableName = tableName1;
string whereStr = GetWhereString(filter);
StringBuilder fields = new StringBuilder();
foreach (var item in objs)
{
SqlField field;
var typeName = DicTableToTableNameFields[filter.TableName][item.Key];
if (typeName == "datetime" && item.Value != null)
{
var date = DateTime.Parse(item.Value.ToString());
field = new SqlField(item.Key, date, CurrPrividerType);
}
else
{
field = new SqlField(item.Key, item.Value, CurrPrividerType);
}
if (fields.Length > 0)
{
fields.Append(",");
}
fields.Append(field.GetKeyEqualsValueString());
}
var updateSql = string.Format("update {0} set {1} where {2} ", tableName1, fields.ToString(), whereStr);
var conn = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
int count = DBClassHelper.Execute(updateSql, conn);
conn.Close();
}
/// <summary>
/// 得到查询字段
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
internal virtual string GetQueryFields(QueryPageFilter filters)
{
if (filters.ReturnFieldNames == null || filters.ReturnFieldNames.Length == 0)
{
return(GetFieldFromTable(filters.TableName));
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < filters.ReturnFieldNames.Length; i++)
{
if (builder.Length > 0)
{
builder.Append(",");
}
else
{
builder.Append(filters.ReturnFieldNames[i]);
}
}
return(builder.ToString());
}
public void DeleteObjects(QueryPageFilter filters)
{
string tableName1 = filters.TableName;
if (TableToTableNameDic != null && TableToTableNameDic.ContainsKey(filters.TableName))
{
tableName1 = TableToTableNameDic[filters.TableName];
}
if (!DataBaseKyFieldTableDic.ContainsKey(tableName1))
{
throw new Exception("缺少表或视图：" + tableName1 + "！请联系开发人员！");
}
filters.TableName = tableName1;
string whereStr = GetWhereString(filters);
string strSql = string.Empty;
strSql = "delete from " + filters.TableName + " where " + whereStr;
var conn = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
int count = DBClassHelper.Execute(strSql, conn);
conn.Close();
}
/*
* public override void ExecuteSqlList(List<string> sqlList)
* {
* var sqls = sqlList.Where(m => string.IsNullOrEmpty(m) == false).ToList();
* var conn = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
* System.Data.IDbCommand cmd = conn.CreateCommand();
* { // <-------------------
* try
* {
* foreach (var item in sqls)
* {
* cmd.CommandText = item;
* cmd.ExecuteNonQuery();
* }
* cmd.Dispose();
* }
* catch
* {
* cmd.Dispose();
* throw; // <-------------------
* }
* finally
* {
* conn.Close();
* }
* }
* }
*/
/*
* public override void UpdateSystemTable(string TableName, List<Column> colomns)
* {
* string def = string.Empty;
* var builder = new StringBuilder();
* var builder2 = new StringBuilder();
* var descBuilder = new StringBuilder();
* if (TableToTableNameDic.ContainsKey(TableName))
* {
* int i = 1;
* foreach (var item in colomns)
* {
* i++;
* string len = "";
* string isNullStr;
* if (item.Precision == 0) len = "";
* else if (item.Precision == -1) len = "(MAX)";
* else len = string.Format("({0})", item.Precision);
*
* if (item.TypeName == "varchar" && string.IsNullOrEmpty(len)) len = "(50)";
* if (item.Default != null && !string.IsNullOrEmpty(item.Default))//修改数据库默认值，删除约束，添加约束（添加常量默认值必须加‘ ’；例 'GAAJ'）
* {
* if (item.Default == "'auto'")
* {
*
* //String sql2 = string.Format(@" ALTER TABLE {0} ADD {1} INT IDENTITY (1,1);", TableName, item.ColumnName);
* //builder2.AppendLine(sql2);
* continue;
*
* }
* else
* {
* def = "((" + item.Default + "))";
* string sql2 = string.Format(@"IF EXISTS(select a.name as 用户表,b.name as 字段名,d.name as 字段默认值约束 from sysobjects a
* inner join syscolumns b on (a.id=b.id)inner join syscomments c on ( b.cdefault=c.id )
* inner join sysobjects d on (c.id=d.id)
* where a.name='{0}'and b.name='{1}')
* declare @tablename{2} varchar(30)
* declare @fieldname{2} varchar(50)
* declare @sql{2} varchar(300)
*
* set @tablename{2}='{0}'
* set @fieldname{2}='{1}'
* set @sql{2}=''
* select @sql{2}=@sql{2}+'
* alter table ['+a.name+'] drop constraint ['+d.name+']'
* from sysobjects a
* join syscolumns b on a.id=b.id
* join syscomments c on b.cdefault=c.id
* join sysobjects d on c.id=d.id
* where a.name=@tablename{2} and b.name=@fieldname{2}
* exec(@sql{2})", TableName, item.ColumnName, i);
*
* string sql3 = string.Format(@" ALTER TABLE {0} ADD DEFAULT {1} FOR {2} WITH VALUES", TableName, def, item.ColumnName);
*
* builder2.AppendLine(sql2 + sql3);
* }
* }
* if (item.IsPrimaryKey == true)
* {
* isNullStr = "NOT NULL";
* }
* else
* {
* isNullStr = "NULL";
* }
* string sql = string.Format(
* @" if exists(select * from syscolumns where id=object_id('{0}') and name='{1}')
* alter table {0} alter column {1} {2}{3} {4} ; else alter table {0} add {1} {2}{3} {4} ", TableName, item.ColumnName, item.TypeName, len, isNullStr);
* var str = string.Format("if exists(SELECT c.name,p.value FROM sys.extended_properties p ,sys.columns c " +
* "WHERE p.major_id=OBJECT_ID('{0}') and p.major_id=c.object_id and p.minor_id=c.column_id and c.name = '{2}') " +
* "EXECUTE sp_updateextendedproperty N'MS_Description', '{1}', N'user', N'dbo', N'table', N'{0}', N'column', N'{2}';" +
* "else EXECUTE sp_addextendedproperty N'MS_Description', '{1}', N'user', N'dbo', N'table', N'{0}', N'column', N'{2}';", TableName, item.Label, item.ColumnName);
* builder.AppendLine(sql);
* descBuilder.AppendLine(str);
* }
* }
* else
* {
* var keyCol = colomns.Where(m => m.IsPrimaryKey == true).FirstOrDefault();
* if (keyCol == null)
* throw new Exception("缺少主键！");
* builder.AppendLine(string.Format("CREATE TABLE {0} (", TableName));
* foreach (var item in colomns)
* {
* string defs = string.Empty;
* if (item.Default != null)
* {
* defs = " DEFAULT((" + item.Default + "))";
* }
* string len = "";
* if (item.Precision == 0) len = "";
* else if (item.Precision == -1) len = "(MAX)";
* else len = string.Format("({0})", item.Precision);
*
* if (item.TypeName == "varchar" && string.IsNullOrEmpty(len)) len = "(50)";
* if (item.Default != null && item.Default == "'auto'")
* {
* builder.AppendLine(string.Format("{0} INT IDENTITY (1,1),", item.ColumnName));
* }
* else
* {
* builder.AppendLine(string.Format("{0} {1}{2} {3} null {4},", item.ColumnName, item.TypeName, len, (item.IsNull ? "" : " not "), defs));
* }
*
* var str = string.Format("EXECUTE sp_addextendedproperty N'MS_Description', '{1}', N'user', N'dbo', N'table', N'{0}', N'column', N'{2}'", TableName, item.Label, item.ColumnName);
* descBuilder.AppendLine(str);
* }
* builder.AppendLine(string.Format(" constraint PK_{0} primary key clustered ({1})); ", TableName, keyCol.ColumnName));
* }
* // builder.AppendLine(descBuilder.ToString());
* var strSql = builder.ToString();
* var descSql = descBuilder.ToString();
* var defSql = builder2.ToString();
* if (!string.IsNullOrEmpty(strSql))
* {
* var conn = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
* int count = DBClassHelper.Execute(strSql, conn);
* if (!string.IsNullOrEmpty(descSql))
* {
* DBClassHelper.Execute(descSql, conn);
* }
* if (!string.IsNullOrEmpty(defSql))
* {
* DBClassHelper.Execute(defSql, conn);
* }
* GetSqlTableInfo();
* conn.Close();
* }
* }
*/
internal override FilterQueryResult GetQueryResultFromDB(QueryPageFilter filters)
{
FilterQueryResult result = new FilterQueryResult();
string whereStr = GetWhereString(filters);
string orderBy = GetOrderByString(filters);
string groupBy = GetGroupByStrings(filters);
var fields = GetQueryFields(filters);
string sql = "select count(*) from " + filters.TableName + " where " + whereStr;
string strSql = string.Empty;
if (filters.IsPage == true)
{
var index = filters.PageSize * (filters.PageIndex - 1);
if (index < 0)
{
index = 0;
}
var keyField = m_DataBaseKyFieldTableDic[filters.TableName];
var allFieldsArr = m_DicTableToTableNameFields[filters.TableName];
var queryFieldsArr = fields.Replace(" ", "").Split(',');
if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") == "*" && allFieldsArr.ContainsKey("ID"))
{
keyField = "ID";
}
if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") == "*" && !allFieldsArr.ContainsKey("ID"))
{
keyField = queryFieldsArr.First();
}
if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") != "*" && queryFieldsArr.Contains("ID"))
{
keyField = "ID";
}
if (string.IsNullOrEmpty(keyField) && fields.Replace(" ", "") != "*" && !queryFieldsArr.Contains("ID"))
{
keyField = queryFieldsArr.First();
}
if (string.IsNullOrEmpty(orderBy))
{
orderBy = " order by " + keyField + " ASC ";
}
else
{
orderBy = orderBy + "," + keyField + " ASC ";
}
var pageSize = filters.PageSize;
if (pageSize == 0)
{
pageSize = 1;
}
//分页查询优化，FZP
strSql = string.Format("select {0} from {1} where {2} {3} offset {4} rows fetch next {5} rows only", fields, filters.TableName, whereStr, orderBy, index, pageSize);
}
else
{
strSql = "select " + fields + " from " + filters.TableName + " where " + whereStr + groupBy + orderBy;
}
var conn = DBClassHelper.OpenConnect(CurrConnectionString, SqlHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
var tableCount = DBClassHelper.ExecuteQueryToDataTable(sql, conn);
int count = int.Parse(tableCount.Rows[0][0].ToString());
if (filters.IsReturnCount == false)
{
var table = DBClassHelper.ExecuteQueryToDataTable(strSql, conn);
result.Result = table;
result.Result.TableName = filters.TableName;
}
result.TotalCount = count;
conn.Close();
return(result);
}