private List <SqlTreeDto> GetSqlTree(List <DatabaseEntity> sqlConnDtos, SqlSearchDto searchDto)
{
List <SqlTreeDto> tree = new List <SqlTreeDto>();
foreach (var node in sqlConnDtos)
{
var dto = new SqlTreeDto()
{
isParent = true,
id = "db-" + Guid.NewGuid().ToString().Replace("-", ""),
pId = searchDto.Id,
name = node.DatabaseName,
open = false,
icon = GetIcon("database"),
SqlType = searchDto.SqlType,
SqlConnId = searchDto.SqlConnId,
Database = node.DatabaseName,
};
tree.Add(dto);
tree.Add(CreateTableTree(dto, searchDto));
tree.Add(CreateViewTree(dto, searchDto));
tree.Add(CreateProcedureTree(dto, searchDto));
tree.Add(CreateFunctionTree(dto, searchDto));
}
return(tree);
}
private List <SqlTreeDto> GetSqlTree(List <SqlConnDto> sqlConnDtos, SqlSearchDto searchDto)
{
List <SqlTreeDto> tree = new List <SqlTreeDto>();
foreach (var node in sqlConnDtos)
{
var dto = new SqlTreeDto()
{
isParent = true,
id = "c-" + Guid.NewGuid().ToString().Replace("-", ""),
pId = "root",
name = node.Name,
open = false,
icon = GetIcon(node.SqlType.ToLower()),
SearchType = "GetDatabaseNames",
SqlConnId = node.Id,
SqlType = node.SqlType,
};
tree.Add(dto);
if (node.SqlType.Equals("sqlite", StringComparison.OrdinalIgnoreCase))
{
dto.SearchType = null;
tree.Add(CreateTableTree(dto, searchDto));
tree.Add(CreateViewTree(dto, searchDto));
}
}
return(tree);
}
public async Task <IActionResult> GetSqlTree([FromBody] SqlSearchDto request)
{
var use = await _sqlOnlineApplication.UseDevelopment();
if (use == false)
{
return(LayuiError("系统出了个小差！"));
}
try {
if (string.IsNullOrEmpty(request.SearchType))
{
var connDtos = await _sqlOnlineApplication.GetConnList();
return(LayuiSuccess(GetSqlTree(connDtos, request), null));
}
else if (request.SearchType == "GetDatabaseNames")
{
var databaseEntities = await _sqlOnlineApplication.GetDatabaseNames(request.SqlConnId);
return(LayuiSuccess(GetSqlTree(databaseEntities, request), null));
}
} catch (Exception ex) {
LogUtil.Error(ex);
}
return(LayuiError("系统出了个小差！"));
}
public async Task <IActionResult> GetTableViewProcedureData([FromForm] SqlSearchDto request)
{
var use = await _sqlOnlineApplication.UseDevelopment();
if (use == false)
{
return(Error("系统出了个小差！"));
}
try {
if (request.SearchType.Equals("table", StringComparison.OrdinalIgnoreCase))
{
var tableView = await _sqlOnlineApplication.GetTableInfo(request.SqlConnId, request.Database, request.Schema, request.Search);
return(Success(tableView, null));
}
else if (request.SearchType.Equals("view", StringComparison.OrdinalIgnoreCase))
{
var tableView = await _sqlOnlineApplication.GetViewInfo(request.SqlConnId, request.Database, request.Schema, request.Search);
return(Success(tableView, null));
}
else if (request.SearchType.Equals("proc", StringComparison.OrdinalIgnoreCase))
{
var tableView = await _sqlOnlineApplication.GetProcedureInfo(request.SqlConnId, request.Database, request.Schema, request.Search);
return(Success(tableView, null));
}
} catch (Exception ex) {
LogUtil.Error(ex);
}
return(Error("系统出了个小差！"));
}
public async Task <SqlEditorDto> GetSql_ProcedureAlter(SqlSearchDto request)
{
var connDto = await GetConn(request.AdminId, request.SqlConnId);
var provider = SqlCache.Instance.GetSqlProcedureProvider(connDto.SqlType);
return(provider.GetAlterSql(connDto.ConnectionString, request.Database, request.Schema, request.Search));
}
public async Task <SqlEditorDto> GetSql_New(SqlSearchDto request)
{
var connDto = await GetConn(request.AdminId, request.SqlConnId);
var provider = SqlCache.Instance.GetSqlCommonProvider(connDto.SqlType);
return(provider.GetNew());
}
public async Task <List <DatabaseEntity> > GetDatabaseNames(SqlSearchDto request)
{
var connDto = await GetConn(request.AdminId, request.SqlConnId);
var provider = SqlCache.Instance.GetSqlCommonProvider(connDto.SqlType);
return(provider.GetDatabases(connDto.ConnectionString));
}
public async Task <List <TableColumnEntity> > GetTableColumns(SqlSearchDto request)
{
var connDto = await GetConn(request.AdminId, request.SqlConnId);
var provider = SqlCache.Instance.GetSqlCommonProvider(connDto.SqlType);
return(provider.GetTableColumns(connDto.ConnectionString, request.Database, request.Schema, request.Search));
}
public Task <List <SqlConnDto> > GetConnList(SqlSearchDto request)
{
const string sql = @"select DISTINCT conn.Id,conn.Name,conn.SqlType
from SqlConn conn
inner join SqlConnPass pass on pass.ConnId=conn.Id
inner join SysAdmin_Group g on g.GroupId=pass.AdminGroupId
where conn.IsDelete=0 and pass.IsDelete=0 and g.AdminId=@0 and pass.CanRead=1
order by conn.Name asc
";
var helper = GetReadHelper();
return(helper.Select_Async <SqlConnDto>(sql, request.AdminId));
}
public async Task <IActionResult> GetTableViewProcedure([FromForm] SqlSearchDto request)
{
try {
#region TableViewProcedure
var tables = await _sqlOnlineApplication.GetTables(request.SqlConnId, request.Database);
var views = await _sqlOnlineApplication.GetViews(request.SqlConnId, request.Database);
var procedures = await _sqlOnlineApplication.GetProcedures(request.SqlConnId, request.Database);
var Infos = new List <TableViewProcedure>();
foreach (var table in tables)
{
Infos.Add(new TableViewProcedure()
{
TplTypeId = 1,
Type = "table",
SchemaName = table.SchemaName,
Name = table.TableName,
Comment = table.Comment
});
}
foreach (var view in views)
{
Infos.Add(new TableViewProcedure()
{
TplTypeId = 1,
Type = "view",
SchemaName = view.SchemaName,
Name = view.ViewName,
Comment = view.Comment
});
}
foreach (var procedure in procedures)
{
Infos.Add(new TableViewProcedure()
{
TplTypeId = 2,
Type = "proc",
SchemaName = procedure.SchemaName,
Name = procedure.ProcedureName,
Comment = procedure.Comment
});
}
#endregion
return(LayuiSuccess(Infos, null));
} catch (Exception ex) {
LogUtil.Error(ex);
}
return(LayuiError("系统出了个小差！"));
}
public async Task <IActionResult> GetDatabaseInfos([FromForm] SqlSearchDto request)
{
var use = await _sqlOnlineApplication.UseDevelopment();
if (use == false)
{
return(Error("Not use development"));
}
var list = await _sqlOnlineApplication.GetTableShowColumns(request.SqlConnId, request.Database);
if (list != null)
{
Dictionary <string, StructureModel> dict = new Dictionary <string, StructureModel>();
List <StructureModel> models = new List <StructureModel>();
foreach (var item in list)
{
StructureModel model;
var key = item.SchemaName + "." + item.Name;
if (dict.TryGetValue(key, out model) == false)
{
model = new StructureModel();
if (string.IsNullOrEmpty(item.SchemaName) || item.SchemaName == "dbo" || item.SchemaName == "public")
{
model.Name = item.Name;
}
else
{
model.Name = key;
}
model.Comment = item.Comment;
if (item.Type == "t" || item.Type == "table")
{
model.Type = "t";
}
else
{
model.Type = "v";
}
dict[key] = model;
models.Add(model);
}
model.Items.Add(new StructureItemModel(item));
}
return(Success(models, null));
}
return(Error());
}
private SqlTreeDto CreateFunctionTree(SqlTreeDto dto, SqlSearchDto searchDto)
{
return(new SqlTreeDto()
{
isParent = true,
id = "fs-" + Guid.NewGuid().ToString().Replace("-", ""),
pId = dto.id,
name = "函数",
open = true,
icon = GetIcon("function"),
SqlType = searchDto.SqlType,
SqlConnId = searchDto.SqlConnId,
Database = dto.Database,
SearchType = "GetFunctionNames",
});
}
private SqlTreeDto CreateProcedureTree(SqlTreeDto dto, SqlSearchDto searchDto)
{
return(new SqlTreeDto()
{
isParent = true,
id = "ps-" + Guid.NewGuid().ToString().Replace("-", ""),
pId = dto.id,
name = "存储过程",
open = true,
icon = GetIcon("procedure"),
SqlType = searchDto.SqlType,
SqlConnId = searchDto.SqlConnId,
Database = dto.Database,
SearchType = "GetProcedureNames",
});
}
private SqlTreeDto CreateViewTree(SqlTreeDto dto, SqlSearchDto searchDto)
{
return(new SqlTreeDto()
{
isParent = true,
id = "vs-" + Guid.NewGuid().ToString().Replace("-", ""),
pId = dto.id,
name = "视图",
open = true,
icon = GetIcon("view"),
SqlType = dto.SqlType,
SqlConnId = dto.SqlConnId,
Database = dto.Database,
SearchType = "GetViewNames",
});
}
private SqlTreeDto CreateTableTree(SqlTreeDto dto, SqlSearchDto searchDto)
{
return(new SqlTreeDto()
{
isParent = true,
id = "ts-" + Guid.NewGuid().ToString().Replace("-", ""),
pId = dto.id,
name = "表",
open = true,
icon = GetIcon("table"),
SqlType = dto.SqlType,
SqlConnId = dto.SqlConnId,
Database = dto.Database,
Search = dto.Search,
Schema = dto.Schema,
SearchType = "GetTableNames",
});
}
public async Task <IActionResult> OnGet(int sqlConnId, string databaseName)
{
ConnId = sqlConnId;
DatabaseName = databaseName;
SqlSearchDto search = new SqlSearchDto()
{
AdminId = AdminDto.Id,
SqlConnId = sqlConnId,
Database = databaseName,
};
DatabaseEntities = await _sqlOnlineApplication.GetDatabaseNames(search);
if (string.IsNullOrEmpty(DatabaseName))
{
if (DatabaseEntities.Count > 0)
{
DatabaseName = DatabaseEntities[0].DatabaseName;
}
}
return(Page());
}
private List <SqlTreeDto> GetSqlTree(List <FunctionEntity> sqlConnDtos, SqlSearchDto searchDto)
{
List <SqlTreeDto> tree = new List <SqlTreeDto>();
foreach (var node in sqlConnDtos)
{
var dto = new SqlTreeDto()
{
id = "f-" + Guid.NewGuid().ToString().Replace("-", ""),
pId = searchDto.Id,
name = node.FunctionName,
SqlType = searchDto.SqlType,
SqlConnId = searchDto.SqlConnId,
Database = node.DatabaseName,
Schema = node.SchemaName,
Search = node.FunctionName,
icon = GetIcon("function"),
};
if (searchDto.SqlType.Equals("sqlserver", StringComparison.OrdinalIgnoreCase))
{
if (node.SchemaName != "dbo")
{
dto.name = node.SchemaName + "." + node.FunctionName;
}
}
if (searchDto.SqlType.Equals("PostgreSQL", StringComparison.OrdinalIgnoreCase))
{
if (node.SchemaName != "public")
{
dto.name = node.SchemaName + "." + node.FunctionName;
}
}
tree.Add(dto);
}
return(tree);
}
public async Task <IActionResult> GetOutTableInfo(SqlSearchDto dto)
{
var list = await _sqlOnlineApplication.GetTableShowColumns(AdminDto.Id, dto.SqlConnId, dto.Database);
List <StructureModel> models = new List <StructureModel>();
if (list != null)
{
Dictionary <string, StructureModel> dict = new Dictionary <string, StructureModel>();
foreach (var item in list)
{
StructureModel model;
var key = item.SchemaName + "." + item.Name;
if (dict.TryGetValue(key, out model) == false)
{
model = new StructureModel();
if (string.IsNullOrEmpty(item.SchemaName) || item.SchemaName == "dbo" || item.SchemaName == "public")
{
model.Name = item.Name;
}
else
{
model.Name = key;
}
model.Comment = item.Comment;
if (item.TableType.Trim() == "t" || item.TableType == "BASE TABLE" ||
item.TableType.Equals("table", StringComparison.OrdinalIgnoreCase) ||
item.TableType.Trim().Equals("u", StringComparison.OrdinalIgnoreCase)
)
{
model.Type = "t";
}
else
{
model.Type = "v";
}
dict[key] = model;
models.Add(model);
}
model.Items.Add(new StructureItemModel(item));
}
}
var filePath = MyHostingEnvironment.MapWebRootPath("/_/outTableInfo/index.html");
var html = System.IO.File.ReadAllText(filePath);
StringBuilder stringBuilder = new StringBuilder(html);
stringBuilder.Replace("[[DatabaseName]]", dto.Database);
stringBuilder.Replace("[[data]]", Newtonsoft.Json.JsonConvert.SerializeObject(models));
using (var archive = ZipArchive.Create()) {
var folderPath = MyHostingEnvironment.MapWebRootPath("/_/outTableInfo/");
archive.AddEntry("css/dbs.css", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, "css/dbs.css")).OpenRead(), true);
archive.AddEntry("css/global.css", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, "css/global.css")).OpenRead(), true);
archive.AddEntry("img/dbs/bg.gif", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, "img/dbs/bg.gif")).OpenRead(), true);
archive.AddEntry("img/dbs/icon-proc.gif", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, "img/dbs/icon-proc.gif")).OpenRead(), true);
archive.AddEntry("img/dbs/icon-table.gif", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, "img/dbs/icon-table.gif")).OpenRead(), true);
archive.AddEntry("img/dbs/icon-view.gif", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, "img/dbs/icon-view.gif")).OpenRead(), true);
archive.AddEntry(@"js\layer\skin\default\icon.png", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\layer\skin\default\icon.png")).OpenRead(), true);
archive.AddEntry(@"js\layer\skin\default\icon-ext.png", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\layer\skin\default\icon-ext.png")).OpenRead(), true);
archive.AddEntry(@"js\layer\skin\default\layer.css", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\layer\skin\default\layer.css")).OpenRead(), true);
archive.AddEntry(@"js\layer\skin\default\loading-0.gif", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\layer\skin\default\loading-0.gif")).OpenRead(), true);
archive.AddEntry(@"js\layer\skin\default\loading-1.gif", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\layer\skin\default\loading-1.gif")).OpenRead(), true);
archive.AddEntry(@"js\layer\skin\default\loading-2.gif", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\layer\skin\default\loading-2.gif")).OpenRead(), true);
archive.AddEntry(@"js\layer\layer.js", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\layer\layer.js")).OpenRead(), true);
archive.AddEntry(@"js\clipboard.js", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\clipboard.js")).OpenRead(), true);
archive.AddEntry(@"js\doT.min.js", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\doT.min.js")).OpenRead(), true);
archive.AddEntry(@"js\jquery.min.js", new System.IO.FileInfo(System.IO.Path.Combine(folderPath, @"js\jquery.min.js")).OpenRead(), true);
archive.AddEntry(@"index.html", new System.IO.MemoryStream(Encoding.UTF8.GetBytes(stringBuilder.ToString())), true);
using (var ms = new System.IO.MemoryStream()) {
archive.SaveTo(ms);
return(File(ms.ToArray(), "application/zip", dto.Database + "_tableInfo.zip"));
}
}
}
public async Task <IActionResult> OnGet(string action, int id, int sqlConnId, string database, string schema, string name)
{
ConnId = sqlConnId;
DatabaseName = database;
SqlSearchDto search = new SqlSearchDto()
{
AdminId = AdminDto.Id,
SqlConnId = sqlConnId,
Database = database,
Schema = schema,
Search = name
};
SqlEditorDto r = null;
if (action == "new")
{
r = await _sqlOnlineApplication.GetSql_New(search);
}
else if (action == "TableSelect100")
{
r = await _sqlOnlineApplication.GetSql_TableSelect100(search);
}
else if (action == "TableInsertSql")
{
r = await _sqlOnlineApplication.GetSql_TableInsert(search);
}
else if (action == "TableUpdateSql")
{
r = await _sqlOnlineApplication.GetSql_TableUpdate(search);
}
else if (action == "TableDeleteSql")
{
r = await _sqlOnlineApplication.GetSql_TableDelete(search);
}
else if (action == "ViewSelect100")
{
r = await _sqlOnlineApplication.GetSql_ViewSelect100(search);
}
else if (action == "ViewCreateSql")
{
r = await _sqlOnlineApplication.GetSql_ViewCreate(search);
}
else if (action == "ViewAlterSql")
{
r = await _sqlOnlineApplication.GetSql_ViewAlter(search);
}
else if (action == "FunctionSelectSql")
{
r = await _sqlOnlineApplication.GetSql_FunctionSelectSql(search);
}
else if (action == "FunctionCreateSql")
{
r = await _sqlOnlineApplication.GetSql_FunctionCreate(search);
}
else if (action == "FunctionAlterSql")
{
r = await _sqlOnlineApplication.GetSql_FunctionAlter(search);
}
else if (action == "ProcedureExecuteSql")
{
r = await _sqlOnlineApplication.GetSql_ProcedureExecuteSql(search);
}
else if (action == "ProcedureCreateSql")
{
r = await _sqlOnlineApplication.GetSql_ProcedureCreate(search);
}
else if (action == "ProcedureAlterSql")
{
r = await _sqlOnlineApplication.GetSql_ProcedureAlter(search);
}
else if (action == "openDoc")
{
var sqlDoc = await _sqlOnlineApplication.GetSelfSqlDoc(AdminDto.Id, id);
if (sqlDoc == null)
{
return(Redirect(UrlSetting.NotFoundUrl));
}
await _sqlOnlineApplication.OpenSqlDoc(AdminDto.Id, id);
DocId = sqlDoc.SqlDocId;
Title = sqlDoc.Title;
SqlString = sqlDoc.Content;
DatabaseName = sqlDoc.DatabaseName;
ConnId = sqlDoc.SqlConnId;
search.SqlConnId = sqlDoc.SqlConnId;
r = _sqlOnlineApplication.GetSql_New(sqlDoc.SqlType);
}
else if (action == "openDocTemp")
{
var sqlDoc = await _sqlOnlineApplication.GetSelfSqlDocTemp(AdminDto.Id, id);
if (sqlDoc == null)
{
return(Redirect(UrlSetting.NotFoundUrl));
}
TempId = sqlDoc.Id;
DocId = sqlDoc.SqlDocId;
Title = sqlDoc.Title;
SqlString = sqlDoc.Content;
DatabaseName = sqlDoc.DatabaseName;
ConnId = sqlDoc.SqlConnId;
search.SqlConnId = sqlDoc.SqlConnId;
r = _sqlOnlineApplication.GetSql_New(sqlDoc.SqlType);
}
else if (action == "openDocShare")
{
var sqlDoc = await _sqlOnlineApplication.GetShareSqlDoc(AdminDto.Id, id);
if (sqlDoc == null)
{
return(Redirect(UrlSetting.NotFoundUrl));
}
Title = sqlDoc.Title;
SqlString = sqlDoc.Content;
DatabaseName = sqlDoc.DatabaseName;
ConnId = sqlDoc.SqlConnId;
search.SqlConnId = sqlDoc.SqlConnId;
r = _sqlOnlineApplication.GetSql_New(sqlDoc.SqlType);
}
if (r == null)
{
return(Redirect(UrlSetting.NotFoundUrl));
}
JsMode = r.JsMode;
if (string.IsNullOrEmpty(SqlString))
{
SqlString = r.SqlString;
}
DatabaseEntities = await _sqlOnlineApplication.GetDatabaseNames(search);
if (string.IsNullOrEmpty(DatabaseName))
{
if (DatabaseEntities.Count > 0)
{
DatabaseName = DatabaseEntities[0].DatabaseName;
}
}
return(Page());
}
public Task <SqlEditorDto> GetSql_TableSelect100(SqlSearchDto request)
{
return(GetSql_TableSelect100(request.AdminId, request.SqlConnId, request.Database, request.Schema, request.Search));
}