/// <summary> /// Deletes the index. /// </summary> /// <param name="deleteIndexSql">The delete index sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildDeleteIndex(DeleteIndexSql deleteIndexSql) { var r = new ParmsSqlResult(); r.SQL = $"drop index {deleteIndexSql.IndexName} on {deleteIndexSql.TableName}"; return(r); }
/// <summary> /// Renames the index sql. /// </summary> /// <param name="renameIndexSql">The rename index sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult RenameIndexSql(RenameIndexSql renameIndexSql) { var r = new ParmsSqlResult(); r.SQL = $"ALTER TABLE {renameIndexSql.TableName} RENAME INDEX {renameIndexSql.OldIndexName} TO {renameIndexSql.NewIndexName}"; return(r); }
/// <summary> /// Creates the index. /// </summary> /// <param name="createIndexSql">The create index sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildCreateIndex(CreateIndexSql createIndexSql) { var r = new ParmsSqlResult(); r.SQL = $"create {createIndexSql.SqlIndex} {createIndexSql.IndexName} on {createIndexSql.TableName}({string.Join(",", createIndexSql.ColumnNames)})"; return(r); }
/// <summary> /// Builds the column names. /// </summary> /// <param name="columsNamesSql">The colums names sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildColumnNames(ColumsNamesSql columsNamesSql) { var r = new ParmsSqlResult(); r.SQL = $"select column_name from information_schema.columns where table_schema='{columsNamesSql.Database}' and table_name='{columsNamesSql.TableName}'"; return(r); }
/// <summary> /// Builds the all table name. /// </summary> /// <param name="allTableNamesSql">The all table names sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildAllTableName(AllTableNamesSql allTableNamesSql) { var result = new ParmsSqlResult(); result.SQL = $"select table_name from information_schema.tables where table_schema='{allTableNamesSql.Database}'"; return(result); }
/// <summary> /// Builds the all database. /// </summary> /// <param name="allDatabaseSql">The all database sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildAllDatabase(AllDatabaseSql allDatabaseSql) { var result = new ParmsSqlResult(); result.SQL = "select schema_name from information_schema.schemata"; return(result); }
/// <summary> /// Builders the string sql. /// </summary> /// <param name="stringsql">The stringsql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuilderStringSql(StringSql stringsql) { var sqlResult = new ParmsSqlResult(); sqlResult.SQL = stringsql.ToString(); return(sqlResult); }
/// <summary> /// Builds the delete database. /// </summary> /// <param name="deleteDatabaseSql">The delete database sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildDeleteDatabase(DeleteDatabaseSql deleteDatabaseSql) { var result = new ParmsSqlResult(); result.SQL = $"drop database {deleteDatabaseSql.Database}"; return(result); }
/// <summary> /// Builds the create database. /// </summary> /// <param name="createDatabaseSql">The create database sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildCreateDatabase(CreateDatabaseSql createDatabaseSql) { var result = new ParmsSqlResult(); result.SQL = $"create database {createDatabaseSql.Database}"; return(result); }
/// <summary> /// Updates the table name. /// </summary> /// <param name="updateTableNameSql">The update table name sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildUpdateTableName(UpdateTableNameSql updateTableNameSql) { var r = new ParmsSqlResult(); r.SQL = $"alter table {updateTableNameSql.OldName} rename {updateTableNameSql.NewName}"; return(r); }
/// <summary> /// Builds the count for database. /// </summary> /// <param name="countDatabaseSql">The count database sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildCountForDatabase(CountDatabaseSql countDatabaseSql) { var result = new ParmsSqlResult(); result.SQL = $"SELECT count(1) FROM information_schema.SCHEMATA where SCHEMA_NAME='{countDatabaseSql.Database}'"; return(result); }
/// <summary> /// Builds the colums count. /// </summary> /// <param name="countColumsSql">The count colums sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildColumnsCount(CountColumsSql countColumsSql) { var r = new ParmsSqlResult(); r.SQL = $"select COUNT(1) from information_schema.columns WHERE table_schema='{countColumsSql.Database}' and table_name = '{countColumsSql.TableName}' and column_name = '{countColumsSql.ColumnsName}'"; return(r); }
/// <summary> /// Gets the table count. /// </summary> /// <param name="countTableSql">The count table sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildTableCount(CountTableSql countTableSql) { var r = new ParmsSqlResult(); r.SQL = $"SELECT count(1) FROM information_schema.TABLES WHERE table_name ='{countTableSql.TableName}'"; return(r); }
/// <summary> /// Builds the delete column. /// </summary> /// <param name="deleteColumsSql">The delete colums sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildDeleteColumn(DeleteColumsSql deleteColumsSql) { var r = new ParmsSqlResult(); r.SQL = $"alter table {deleteColumsSql.TableName} drop column {deleteColumsSql.ColumsName}"; return(r); }
/// <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> /// Deletes the table. /// </summary> /// <param name="deleteTableSql">The delete table sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildDeleteTable(DeleteTableSql deleteTableSql) { var r = new ParmsSqlResult(); r.SQL = $"drop table {deleteTableSql.TableName}"; return(r); }
/// <summary> /// Builds the query. /// </summary> /// <param name="query">The query.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildQuery(IQuery query) { var result = new ParmsSqlResult(); var sql = BuildSubQuery(query); var strBuild = new StringBuilder(sql.SQL); RenameTableType(strBuild, sql.TableTypeDic, sql.TempTable, sql.GroupDic); RenameTempTableType(strBuild, sql.TempTable); result.SQL = strBuild.ToString(); result.Parameters.AddRange(sql.Params); 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 rebane columns name. /// </summary> /// <param name="renameColumnSql">The rename column sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildRenameColumnsName(RenameColumnSql renameColumnSql) { var r = new ParmsSqlResult(); var sqlType = _typeMap.GetSqlType(renameColumnSql.TypeCode.ToString()); if (renameColumnSql.Lentgh < 0) { throw new ArgumentException("长度不能为小于0"); } var lengthStr = GetLengthStr(renameColumnSql.Lentgh, renameColumnSql.TypeCode); r.SQL = $"alter table {renameColumnSql.TableName} change column {renameColumnSql.OldName} {renameColumnSql.NewName} {sqlType}{lengthStr}"; 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> /// Builds the. /// </summary> /// <param name="sqls"></param> /// <returns>A ParmsSqlResult.</returns> public ISqlResult Build(params ISql[] sqls) { var result = new ParmsSqlResult(); var sqlList = new List <string>(); var sqlParam = new SqlParam(); foreach (var item in sqls) { var r = BuildSqlResult(item); sqlList.Add(r.SQL); result.Parameters.AddRange(r.Parameters); } result.SQL = string.Join(';', sqlList); result.Parameters.AddRange(sqlParam.Params); var t_sql = new StringBuilder(result.SQL); RenameParameter(t_sql, result.Parameters); result.SQL = t_sql.ToString().Trim(); return(result); }
/// <summary> /// Builds the add colums. /// </summary> /// <param name="addColumnsSql">The add columns sql.</param> /// <returns>An ISqlResult.</returns> protected virtual ISqlResult BuildAddColumns(AddColumnsSql addColumnsSql) { var r = new ParmsSqlResult(); var sqlType = _typeMap.GetSqlType(addColumnsSql.TypeCode.ToString()); var sql = $"alter table {addColumnsSql.TableName} add column {addColumnsSql.ColumnName} {sqlType}"; if (addColumnsSql.Lentgh < 0) { throw new ArgumentException("长度不能为小于0"); } var lengthStr = GetLengthStr(addColumnsSql.Lentgh, addColumnsSql.TypeCode); sql = $"{sql}{lengthStr}"; if (addColumnsSql.IsNotNull) { sql = $"{sql} not null"; } r.SQL = sql; return(r); }
/// <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); }