/// <summary>
/// Builds the update sql.
/// </summary>
/// <param name="updateSql">The update sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildUpdateSql(UpdateSql updateSql)
{
var query = updateSql.WhereQuery;
var result = new ParmsSqlResult();
var whereSqlResult = query != null?BuildQuery(query) : new ParmsSqlResult();
var tableName = TableUtlis.GetTableName(updateSql.Table);
var doc = updateSql.UpdateObj.As <IRecord>();
var sql = $"update {_dbProvider.FormatFieldName(tableName)} set ";
var fieldList = new List <string>();
var parms = new List <IDbDataParameter>(whereSqlResult.Parameters);
foreach (var item in doc.Keys.ToList())
{
var fieldStr = string.Empty;
if (_dbOption.IsUseParamers)
{
var param = _dbProvider.GetDbDataParameter();
param.ParameterName = $"{_dbProvider.GetParamsSymbol()}{item}";
param.Value = doc[item];
fieldStr = $"{_dbProvider.FormatFieldName(item)}={param.ParameterName}";
parms.Add(param);
}
else
{
fieldStr = $"{_dbProvider.FormatFieldName(item)}={_dbProvider.GetObjectType(doc[item])}";
}
fieldList.Add(fieldStr);
}
sql = $"{sql}{fieldList.Join(",")} {whereSqlResult.SQL}";
result.SQL = sql;
result.Parameters.AddRange(parms);
return(result);
}
/// <summary>
/// Builds the delete sql.
/// </summary>
/// <param name="deleteSql">The delete sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildDeleteSql(DeleteSql deleteSql)
{
var query = deleteSql.WhereQuery;
var result = new ParmsSqlResult();
var whereSqlResult = query != null?BuildQuery(query) : new ParmsSqlResult();
var tableName = TableUtlis.GetTableName(deleteSql.Table);
var sql = $"delete from {_dbProvider.FormatFieldName(tableName)} {whereSqlResult.SQL}";
result.SQL = sql;
result.Parameters.AddRange(whereSqlResult.Parameters);
return(result);
}
/// <summary>
/// Builds the from.
/// </summary>
/// <param name="hasSelectExpression">If true, has select expression.</param>
/// <param name="subQueryTypes"></param>
/// <returns>A ParmsSqlResult.</returns>
protected virtual SqlParam BuildFrom(bool hasSelectExpression, IEnumerable <BaseSubQueryType> subQueryTypes)
{
var r = new SqlParam();
var stringBuilder = new StringBuilder();
if (subQueryTypes.Count() == 0)
{
return(r);
}
if (!hasSelectExpression)
{
stringBuilder.Append(AddWhiteSpace("select * from"));
}
var typeObjList = subQueryTypes.Select(t => t.GetSubQueryType()).ToList();
var typeList = typeObjList.OfType <Type>();
if (typeList.Any())//当前查询返回的类型
{
r.Type = typeList.First();
}
var tableNames = typeList.Select(t => _dbProvider.FormatFieldName(TableUtlis.GetTableName(t)));
stringBuilder.Append(AddWhiteSpace(string.Join(',', tableNames)));
var queryList = typeObjList.OfType <IQuery>();
foreach (var item in queryList)
{
var t_r = BuildSubQuery(item);//处理子查询
r.Type = t_r.Type;
r.GroupDic.AddRange(t_r.GroupDic);
r.TableTypeDic.AddRange(t_r.TableTypeDic);
r.Params.AddRange(t_r.Params);
//如果Type为Null 表示没有只有条件查询语句
if (r.Type == null)
{
stringBuilder.Append(AddWhiteSpace(t_r.SQL));
}
else
{
var tableKey = r.Type.GetHashCode();
r.TempTable.Add(tableKey);
stringBuilder.Append($"({t_r.SQL}) {tableKey}");
}
}
r.SQL = AddWhiteSpace(stringBuilder.ToString().Trim());
//if (!hasSelectExpression)
// r.SQL = AddWhiteSpace($"select * from {string.Join(",", tables)}");
//else
// r.SQL = AddWhiteSpace(string.Join(",", tables));
return(r);
}
/// <summary>
/// Builds the insert sql.
/// </summary>
/// <param name="insertSql">The insert sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildInsertSql(InsertSql insertSql)
{
var result = new ParmsSqlResult();
var doc = EntityUtil.AsTableRecord(insertSql.Table);
var tableName = TableUtlis.GetTableName(insertSql.Table.GetType());
var sql = $"insert into {_dbProvider.FormatFieldName(tableName)}";
var pams = new List <IDbDataParameter>();
var fields = new List <string>();
var valueList = new List <string>();
foreach (var item in doc.Keys.ToList())
{
if (_dbOption.IsUseParamers)
{
var param = _dbProvider.GetDbDataParameter();
param.ParameterName = Guid.NewGuid().ToString();
param.Value = doc[item];
if (param.Value != null)
{
fields.Add($"{_dbProvider.FormatFieldName(item)}");
pams.Add(param);
}
}
else
{
var t_value = _dbProvider.GetObjectType(doc[item]);
if (t_value != null)
{
valueList.Add(t_value);
fields.Add($"{_dbProvider.FormatFieldName(item)}");
}
}
}
if (_dbOption.IsUseParamers)
{
sql = $"{sql}({fields.Join(",")}) values({pams.Join(",", t => t.ParameterName)})";
}
else
{
sql = $"{sql}({fields.Join(",")}) values({valueList.Join(",")})";
}
result.SQL = sql;
result.Parameters.AddRange(pams);
return(result);
}
/// <summary>
/// Creates the table.
/// </summary>
/// <param name="createTableSql">The create table sql.</param>
/// <returns>An ISqlResult.</returns>
protected virtual ISqlResult BuildCreateTable(CreateTableSql createTableSql)
{
var type = createTableSql.TableType;
var r = new ParmsSqlResult();
var typeInfo = type.GetTypeInfo();
var props = typeInfo.GetRuntimeProperties();
var columSqls = new List <string>();
var tableName = TableUtlis.GetTableName(type);
var keys = new List <string>();
foreach (var item in props)
{
var pType = item.PropertyType.Name;
var isNullType = false;
if (item.PropertyType.Name == typeof(Nullable <>).Name)
{
isNullType = true;
pType = item.PropertyType.GenericTypeArguments[0].Name;
}
var aType = _typeMap.GetSqlType(pType);
var columSql = $"{item.Name} {aType}";
var isSequence = item.GetCustomAttribute(typeof(SequenceAttribute), true) != null;
if (isSequence)
{
columSql = $"SequenceId int AUTO_INCREMENT";
columSqls.Add(columSql);
continue;
}
else if (item.Name == "Id")
{
columSql = $"Id nvarchar(36)";
columSqls.Add(columSql);
continue;
}
var igAttribute = item.GetCustomAttribute(typeof(IngoreAttribute), true);
if (igAttribute != null)
{
continue;
}
if (item.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute columnAttribute)
{
if (string.IsNullOrWhiteSpace(columnAttribute.Name))
{
columSql = columSql.Replace(item.Name, columnAttribute.Name);
}
if (pType == nameof(TypeCode.Double) || pType == nameof(TypeCode.Single))
{
var length = columnAttribute.Length == -1 ? 15 : columnAttribute.Length;
columSql = $"{columSql}({length},6)";
}
else if (pType == nameof(TypeCode.DateTime) || pType == nameof(TypeCode.Byte))
{
columSql = $"{columSql}";
}
else if (pType == typeof(Guid).Name)
{
columSql = $"{columSql}";
}
else
{
var length = columnAttribute.Length == -1 ? 255 : columnAttribute.Length;
columSql = $"{columSql}({length})";
}
}
else
{
if (pType == nameof(TypeCode.Double) || pType == nameof(TypeCode.Single))
{
columSql = $"{columSql}(15,6)";
}
else if (pType == nameof(TypeCode.DateTime) || pType == nameof(TypeCode.Byte) || pType == nameof(TypeCode.Int32))
{
columSql = $"{columSql}";
}
else if (pType == typeof(Guid).Name)
{
columSql = $"{columSql}(36)";
}
else
{
columSql = $"{columSql}({255})";
}
}
if (item.GetCustomAttribute(typeof(KeyAttribute), true) is KeyAttribute _)
{
keys.Add(item.Name);
}
var isNotNullAttribute = item.GetCustomAttribute(typeof(NotNullAttribute), true);
if (item.PropertyType == typeof(string) && isNotNullAttribute == null)//如果是string类型默认是可null类型
{
isNullType = true;
}
else if (isNotNullAttribute != null && isNullType)
{
throw new NotSupportedException("可空类型不能被NotNull标记");
}
else if (!isNullType)
{
columSql = $"{columSql} not null";
}
columSqls.Add(columSql);
}
if (keys.Count == 0)
{
columSqls.Add($"PRIMARY KEY ( Id )");
}
else if (keys.Count == 1)
{
columSqls.Add($"PRIMARY KEY ( {keys[0]} )");
}
else
{
throw new NotSupportedException("暂时不支持联合主键，请使用联合唯一索引代替");
}
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("当前TabelName的值为null 无法创建表");
}
var sql = $@"create table {tableName}({string.Join(",", columSqls)})";
r.SQL = sql;
return(r);
}