GetCompletionResponse IProvider.GetCompletion(ConnectionBase connection, IDbTransaction transaction, string text, int position) { var response = new GetCompletionResponse { FromCache = false }; var sqlStatement = new SqlParser(text); var tokens = sqlStatement.Tokens; sqlStatement.FindToken(position, out var previousToken, out var currentToken); if (currentToken != null) { var parts = new IdentifierParser(new StringReader(currentToken.Value)).Parse(); var lastPart = parts.Last(); var lastPartLength = lastPart != null ? lastPart.Length : 0; response.StartPosition = currentToken.EndPosition - lastPartLength + 1; response.Length = lastPartLength; } else { response.StartPosition = position; response.Length = 0; } var sqlObject = sqlStatement.FindSqlObject(previousToken, currentToken); if (sqlObject != null) { var statements = new List <string>(); switch (sqlObject.Type) { case SqlObjectTypes.Database: statements.Add(SqlServerObject.GetDatabases()); break; case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function: { var nameParts = new IdentifierParser(new StringReader(sqlObject.Name ?? string.Empty)).Parse().ToList(); var name = new DatabaseObjectMultipartName(connection.Database, nameParts); switch (nameParts.Count) { case 0: case 1: statements.Add(SqlServerObject.GetDatabases()); statements.Add(SqlServerObject.GetTables(name.Database, new[] { "BASE TABLE", "SYSTEM VIEW" })); break; case 2: statements.Add(SqlServerObject.GetTables(name.Database, new[] { "BASE TABLE", "SYSTEM VIEW" })); break; } } break; case SqlObjectTypes.Column: { var nameParts = new IdentifierParser(new StringReader(sqlObject.ParentName ?? string.Empty)).Parse().ToList(); var name = new DatabaseObjectMultipartName(connection.Database, nameParts); statements.Add(SqlServerObject.GetColumns(name.Database, name.Name)); } break; } var objectNames = new List <IObjectName>(); var executor = DbCommandExecutorFactory.Create(connection.Connection); foreach (var statement in statements) { var items = executor.ExecuteReader(new ExecuteReaderRequest(statement), 128, dataRecord => new ObjectName(null, dataRecord.GetString(0))); objectNames.AddRange(items); } response.Items = objectNames; } return(response); }
GetCompletionResponse IProvider.GetCompletion(ConnectionBase connection, IDbTransaction transaction, string text, int position) { var response = new GetCompletionResponse { FromCache = false }; List <IObjectName> array = null; var sqlStatement = new SqlParser(text); var tokens = sqlStatement.Tokens; sqlStatement.FindToken(position, out var previousToken, out var currentToken); if (currentToken != null) { var parts = new IdentifierParser(new StringReader(currentToken.Value)).Parse().ToList(); var lastPart = parts.Count > 0 ? parts.Last() : null; var lastPartLength = lastPart != null ? lastPart.Length : 0; response.StartPosition = currentToken.EndPosition - lastPartLength + 1; response.Length = lastPartLength; var value = currentToken.Value; if (value.Length > 0 && value[0] == '@') { if (value.IndexOf("@@") == 0) { array = _keyWords.Where(k => k.StartsWith(value)).Select(keyWord => (IObjectName) new NonSqlObjectName(keyWord)).ToList(); } else { var list = new SortedList <string?, object>(); for (var i = 0; i < tokens.Count; i++) { var token = tokens[i]; var keyWord = token.Value; if (keyWord != null && keyWord.Length >= 2 && keyWord.IndexOf(value) == 0 && keyWord != value) { if (!list.ContainsKey(token.Value)) { list.Add(token.Value, null); } } } array = list.Keys.Select(keyWord => (IObjectName) new NonSqlObjectName(keyWord)).ToList(); } } } else { response.StartPosition = position; response.Length = 0; } if (array == null) { var sqlObject = sqlStatement.FindSqlObject(previousToken, currentToken); string commandText = null; if (sqlObject != null) { DatabaseObjectMultipartName name; int i; switch (sqlObject.Type) { case SqlObjectTypes.Database: commandText = SqlServerObject.GetDatabases(); break; case SqlObjectTypes.Table: case SqlObjectTypes.View: case SqlObjectTypes.Function: case SqlObjectTypes.Table | SqlObjectTypes.View: case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function: { name = new DatabaseObjectMultipartName(connection.Database, sqlObject.Name); var nameParts = sqlObject.Name != null ? new IdentifierParser(new StringReader(sqlObject.Name)).Parse().ToList() : null; var namePartsCount = nameParts != null ? nameParts.Count : 0; var statements = new List <string>(); switch (namePartsCount) { case 0: case 1: { statements.Add(SqlServerObject.GetDatabases()); statements.Add(SqlServerObject.GetSchemas()); var objectTypes = sqlObject.Type.ToObjectTypes(); statements.Add(SqlServerObject.GetObjects("dbo", objectTypes)); } break; case 2: if (nameParts[0] != null) { statements.Add(SqlServerObject.GetSchemas(nameParts[0])); var objectTypes = sqlObject.Type.ToObjectTypes(); statements.Add(SqlServerObject.GetObjects(nameParts[0], objectTypes)); } break; case 3: { if (nameParts[0] != null && nameParts[1] != null) { var objectTypes = sqlObject.Type.ToObjectTypes(); statements.Add(SqlServerObject.GetObjects(nameParts[0], nameParts[1], objectTypes)); } } break; } commandText = statements.Count > 0 ? string.Join("\r\n", statements) : null; } break; case SqlObjectTypes.Column: name = new DatabaseObjectMultipartName(connection.Database, sqlObject.ParentName); string[] owners; if (name.Schema != null) { owners = new[] { name.Schema } } ; else { owners = new[] { "dbo", "sys" } }; var sb = new StringBuilder(); for (i = 0; i < owners.Length; i++) { if (i > 0) { sb.Append(','); } sb.AppendFormat("'{0}'", owners[i]); } var ownersString = sb.ToString(); commandText = string.Format(@"declare @schema_id int select top 1 @schema_id = s.schema_id from [{0}].sys.schemas s where s.name in({1}) if @schema_id is not null begin declare @object_id int select @object_id = o.object_id from [{0}].sys.all_objects o where o.name = '{2}' and o.schema_id = @schema_id and o.type in('S','U','TF','V') if @object_id is not null begin select name from [{0}].sys.all_columns c where c.object_id = @object_id order by column_id end end", name.Database, ownersString, name.Name); break; case SqlObjectTypes.Procedure: name = new DatabaseObjectMultipartName(connection.Database, sqlObject.Name); if (name.Schema == null) { name.Schema = "dbo"; } commandText = string.Format(@"select s.name ,o.name from [{0}].sys.objects o join [{0}].sys.schemas s on o.schema_id = s.schema_id where o.type in('P','X') order by 1", name.Database); break; case SqlObjectTypes.Trigger: commandText = "select name from sysobjects where xtype = 'TR' order by name"; break; case SqlObjectTypes.Value: var items = sqlObject.ParentName.Split('.'); i = items.Length - 1; var columnName = items[i]; string tableNameOrAlias = null; if (i > 0) { i--; tableNameOrAlias = items[i]; } if (tableNameOrAlias != null) { var contains = sqlStatement.Tables.TryGetValue(tableNameOrAlias, out var tableName); if (contains) { string where; var tokenIndex = previousToken.Index + 1; if (tokenIndex < tokens.Count) { var token = tokens[tokenIndex]; var tokenValue = token.Value; var indexofAny = tokenValue.IndexOfAny(new[] { '\r', '\n' }); if (indexofAny >= 0) { tokenValue = tokenValue.Substring(0, indexofAny); } string?like; if (tokenValue.Length > 0) { if (tokenValue.Contains('%')) { like = tokenValue; } else { like = tokenValue + '%'; } } else { like = "%"; } where = $"where {columnName} like N'{like}'"; } else { where = null; } commandText = $@"select distinct {columnName} from ( select top 1000 {columnName} from {tableName} (readpast) {where} ) t"; } } break; } } if (commandText != null) { Log.Write(LogLevel.Trace, "commandText:\r\n{0}", commandText); var list = new List <IObjectName>(); try { if (connection.State != ConnectionState.Open) { connection.OpenAsync(CancellationToken.None).Wait(); } var executor = connection.Connection.CreateCommandExecutor(); executor.ExecuteReader(new ExecuteReaderRequest(commandText, null, transaction), dataReader => { while (true) { var fieldCount = dataReader.FieldCount; while (dataReader.Read()) { string schemaName; string objectName; if (fieldCount == 1) { schemaName = null; objectName = dataReader[0].ToString(); } else { schemaName = dataReader.GetStringOrDefault(0); objectName = dataReader.GetString(1); } list.Add(new ObjectName(schemaName, objectName)); } if (!dataReader.NextResult()) { break; } } }); } catch { } array = list; } } response.Items = array; return(response); }