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)); }