| public static Create ( DbConnection, connection ) : IDbCommandAsyncExecutor | ||
| connection | DbConnection, | |
| return | IDbCommandAsyncExecutor | |
IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh)
{
var commandText = $"PRAGMA index_list({_tableNode.Name});";
var executor = DbCommandExecutorFactory.Create(_tableNode.Database.Connection);
return(executor.ExecuteReader(new ExecuteReaderRequest(commandText), 128, dataRecord =>
{
var name = dataRecord.GetString(0);
return new IndexNode(_tableNode, name);
}));
}
IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh)
{
const string commandText = @"PRAGMA database_list;";
var executor = DbCommandExecutorFactory.Create(_connection);
return(executor.ExecuteReader(new ExecuteReaderRequest(commandText), 128, dataRecord =>
{
var name = dataRecord.GetString(1);
return new DatabaseNode(_connection, name);
}));
}
private static string GetScript(
SQLiteConnection connection,
string databaseName,
string name)
{
var commandText = $@"
select sql
from {databaseName}.sqlite_master
where name = '{name}'";
var executor = DbCommandExecutorFactory.Create(connection);
var scalar = executor.ExecuteScalar(new CreateCommandRequest(commandText));
var script = (string)scalar;
return(script);
}
public override async Task OpenAsync(CancellationToken cancellationToken)
{
await _sqlConnection.OpenAsync(cancellationToken);
if (!cancellationToken.IsCancellationRequested)
{
const string commandText = @"select @@servername,@@spid
set arithabort on";
var executor = DbCommandExecutorFactory.Create(_sqlConnection);
var item = executor.ExecuteReader(new ExecuteReaderRequest(commandText), 1, dataRecord => new
{
ServerName = dataRecord.GetString(0),
Spid = dataRecord.GetInt16(1)
}).First();
_serverName = item.ServerName;
_spid = item.Spid;
}
}
public static GetTableSchemaResult GetTableSchema(IDbConnection connection, string tableName)
{
var sqlCommandBuilder = new SqlCommandBuilder();
var fourPartName = new DatabaseObjectMultipartName(connection.Database, tableName);
var owner = fourPartName.Schema;
if (owner == null)
{
owner = "dbo";
}
var commandText = string.Format(@"declare @id int
select
@id = o.object_id
from {0}.sys.objects o
join {0}.sys.schemas s
on o.schema_id = s.schema_id
where
s.name = {1}
and o.name = {2}
select
c.name as ColumnName,
c.column_id as ColumnId,
t.name as TypeName,
c.is_nullable as IsNullable,
c.is_computed as IsComputed,
c.default_object_id as DefaultObjectId
from {0}.sys.columns c
join {0}.sys.types t
on c.user_type_id = t.user_type_id
where c.object_id = @id
order by c.column_id
declare @index_id int
select top 1
@index_id = i.index_id
from {0}.sys.indexes i (readpast)
cross apply
(
select count(1) as [Count]
from {0}.sys.index_columns ic (readpast)
join {0}.sys.columns c (readpast)
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where
ic.object_id = i.object_id
and ic.index_id = i.index_id
and c.is_identity = 1
) c
where
i.object_id = @id
and i.is_unique = 1
and i.has_filter = 0
order by c.[Count],i.index_id
if @index_id is null
select @index_id = i.index_id
from {0}.sys.indexes i (readpast)
where
i.object_id = @id
and i.is_unique = 1
select ic.column_id as ColumnId
from {0}.sys.index_columns ic
where
ic.object_id = @id
and ic.index_id = @index_id
order by ic.index_column_id",
sqlCommandBuilder.QuoteIdentifier(fourPartName.Database),
owner.ToNullableNVarChar(),
fourPartName.Name.ToNullableNVarChar());
var executor = DbCommandExecutorFactory.Create(connection);
GetTableSchemaResult getTableSchemaResult = null;
executor.ExecuteReader(new ExecuteReaderRequest(commandText), dataReader =>
{
var columns = dataReader.ReadResult(128, ReadColumn).ToReadOnlyList();
var uniqueIndexColumns = dataReader.ReadNextResult(128, ReadUniqueIndexColumn).ToReadOnlyList();
getTableSchemaResult = new GetTableSchemaResult(columns, uniqueIndexColumns);
});
return(getTableSchemaResult);
}
IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh)
{
SortedDictionary <int, ColumnNode> columnNodes = null;
using (var methodLog = LogFactory.Instance.GetCurrentMethodLog())
{
using (var connection = new SqlConnection(_databaseNode.Databases.Server.ConnectionString))
{
connection.Open();
var cb = new SqlCommandBuilder();
var databaseName = cb.QuoteIdentifier(_databaseNode.Name);
var commandText = $@"select
c.column_id
,c.name
,c.system_type_id
,c.max_length
,c.precision
,c.scale
,c.is_nullable
,t.name as UserTypeName
from {databaseName}.sys.all_columns c
join {databaseName}.sys.types t
on c.user_type_id = t.user_type_id
where c.object_id = {_id}
order by c.column_id
declare @index_id int
select @index_id = i.index_id
from {databaseName}.sys.indexes i
where i.object_id = {_id}
and i.is_primary_key = 1
select ic.column_id
from {databaseName}.sys.index_columns ic
where ic.object_id = {_id}
and ic.index_id = @index_id
select fkc.parent_column_id
from {databaseName}.sys.foreign_key_columns fkc
where fkc.parent_object_id = {_id}
order by fkc.parent_column_id";
methodLog.Write(LogLevel.Trace, "commandText:\r\n{0}", commandText);
var executor = DbCommandExecutorFactory.Create(connection);
executor.ExecuteReader(new ExecuteReaderRequest(commandText), dataReader =>
{
columnNodes = dataReader.ReadResult(128, ToColumnNode).ToSortedDictionary(c => c.Id);
dataReader.NextResult();
while (dataReader.Read())
{
var columnId = dataReader.GetInt32(0);
var columnNode = columnNodes[columnId];
columnNode.IsPrimaryKey = true;
}
dataReader.NextResult();
while (dataReader.Read())
{
var columnId = dataReader.GetInt32(0);
var columnNode = columnNodes[columnId];
columnNode.IsForeignKey = true;
}
});
}
}
return(columnNodes.Values);
}
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();
var sqlStatement = new SqlParser(text);
var tokens = sqlStatement.Tokens;
sqlStatement.FindToken(position, out var previousToken, out var currentToken);
if (currentToken != null)
{
response.StartPosition = currentToken.StartPosition;
response.Length = currentToken.EndPosition - currentToken.StartPosition + 1;
var value = currentToken.Value;
}
else
{
response.StartPosition = position;
response.Length = 0;
}
var sqlObject = sqlStatement.FindSqlObject(previousToken, currentToken);
if (sqlObject != null)
{
string commandText = null;
response.FromCache = false;
switch (sqlObject.Type)
{
case SqlObjectTypes.Table:
commandText = @"
select name
from sqlite_master
where type = 'table'
order by name collate nocase";
break;
case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function:
commandText = @"
select name
from sqlite_master
where type = 'table'
order by name collate nocase";
break;
case SqlObjectTypes.Index:
commandText = @"
select name
from sqlite_master
where type = 'index'
order by name collate nocase";
break;
case SqlObjectTypes.Column:
commandText = $"PRAGMA table_info({sqlObject.ParentName});";
break;
}
if (commandText != null)
{
var executor = DbCommandExecutorFactory.Create(connection.Connection);
response.Items = executor.ExecuteReader(new ExecuteReaderRequest(commandText), 128, dataRecord =>
{
var name = dataRecord.GetStringOrDefault(0);
return((IObjectName) new ObjectName(name));
}).ToList();
}
}
return(response);
}
DataSet IProvider.GetTableSchema(IDbConnection connection, string tableName)
{
var sqlCommandBuilder = new SqlCommandBuilder();
var fourPartName = new DatabaseObjectMultipartName(connection.Database, tableName);
var owner = fourPartName.Schema;
if (owner == null)
{
owner = "dbo";
}
var commandText = string.Format(@"declare @id int
select @id = o.object_id
from {0}.sys.objects o
join {0}.sys.schemas s
on o.schema_id = s.schema_id
where s.name = {1}
and o.name = {2}
select
c.name as ColumnName,
c.colid as ColumnOrdinal,
convert(bit,case when c.cdefault <> 0 then 1 else 0 end) as HasDefault,
convert(bit,c.isnullable) as IsNullable,
convert(bit,case when c.autoval is not null or c.status = 128 then 1 else 0 end) as HasAutomaticValue
from {0}.dbo.syscolumns c
where c.id = @id
order by c.colid
/*select k.colid as ColumnOrdinal
from {0}.dbo.sysobjects o
join {0}.dbo.sysindexes i
on o.name = i.name
join {0}.dbo.sysindexkeys k
on i.id = k.id
and i.indid = k.indid
where o.parent_obj = @id
and xtype = 'PK'
order by k.keyno*/
declare @index_id int
select @index_id = i.index_id
from {0}.sys.indexes i (readpast)
where
i.object_id = @id
and i.is_primary_key = 1
if @index_id is null
begin
select @index_id = i.index_id
from {0}.sys.indexes i (readpast)
where
i.object_id = @id
and i.is_unique = 1
end
select ic.column_id as ColumnOrdinal
from {0}.sys.index_columns ic
where
ic.object_id = @id
and ic.index_id = @index_id
order by ic.index_column_id
",
sqlCommandBuilder.QuoteIdentifier(fourPartName.Database),
owner.ToTSqlNVarChar(),
fourPartName.Name.ToTSqlNVarChar());
Log.Write(LogLevel.Trace, commandText);
var executor = DbCommandExecutorFactory.Create(connection);
DataSet dataSet = null;
executor.Execute(new CreateCommandRequest(commandText), command => dataSet = command.ExecuteDataSet(CancellationToken.None));
return(dataSet);
}