private void AddRelation(DataSet dataSet, Relation relation, StringNoCaseDictionary <string> dictTableNames)
{
var parentTableName = dictTableNames.ContainsKey(relation.ParentTableName) ? dictTableNames[relation.ParentTableName] : null;
var childTableName = dictTableNames.ContainsKey(relation.ChildTableName) ? dictTableNames[relation.ChildTableName] : null;
if (!string.IsNullOrWhiteSpace(parentTableName) && !string.IsNullOrWhiteSpace(childTableName))
{
AddRelation(dataSet, relation.RelationName, parentTableName, childTableName,
relation.ParentColumnNames.ToArray(), relation.ChildColumnNames.ToArray());
}
}
public virtual void ListScriptIntellisenseItems(string text, string[] lines, Point caretPosition, ScriptIntellisense intellisense)
{
intellisense.UsePeriodInIntellisense = true;
var script = new SqlScript(text);
string lastConnectionName = null;
SqlScriptCommand command = null;
for (int i = 0; i < script.Commands.Count; i++)
{
if (script.Commands[i].StartLineNumber - 1 > caretPosition.Y)
{
break;
}
command = script.Commands[i];
lastConnectionName = command.GetConnectionName();
}
if (string.IsNullOrWhiteSpace(lastConnectionName) && _SelectedDbConnection == null)
{
return;
}
var itemCaptions = new Dictionary <(ScriptIntellisenseItem.IntellisenseItemType, string), int>();
Connection conn = null;
try
{
if (string.IsNullOrWhiteSpace(lastConnectionName))
{
conn = _SelectedDbConnection.Factory.CreateConnection(_SelectedDbConnection.ConnectionString);
}
else
{
var connections = DBConnections.LoadConnections();
var dbConn = connections.FindConnection(lastConnectionName);
if (dbConn != null)
{
conn = dbConn.GetConnectionFactory()?.CreateConnection(dbConn.ConnectionString);
}
}
if (conn == null)
{
return;
}
try
{
conn.Open();
}
catch (Exception)
{
return;
}
var parts = new List <string>();
var aliases = new StringNoCaseDictionary <TableAlias>();
string dbName = conn.DbConnection.Database;
if (!string.IsNullOrWhiteSpace(command?.Text))
{
var reAlias = new Regex(@"(?i)\b(?:from|join)\s+(?:(?<Database>(\w+|\[.*?]\]|`.*?`|"".*?"")\.)?(?:(?<Schema>(?:\w+|\[.*?]\]|`.*?`|"".*?""))\.))?(?<Table>(?:\w+|\[.*?\]|`.*?`|"".*?""))[ \t]+(?<Alias>\w+)");
var collAliases = reAlias.Matches(command.Text);
if ((collAliases?.Count ?? 0) > 0)
{
foreach (Match matchAlias in collAliases)
{
if (!matchAlias.Success)
{
continue;
}
string alias = matchAlias.Groups["Alias"].Value;
string aliasDatabase = Utils.NullString(Utils.UnquoteString(matchAlias.Groups["Database"].Value));
string aliasSchema = Utils.NullString(Utils.UnquoteString(matchAlias.Groups["Schema"].Value));
string aliasTable = Utils.NullString(Utils.UnquoteString(matchAlias.Groups["Table"].Value));
if (string.IsNullOrWhiteSpace(alias) || string.Compare(alias, "where", true) == 0 ||
string.Compare(alias, "order", true) == 0 || string.Compare(alias, "join", true) == 0 ||
string.Compare(alias, "left", true) == 0 || string.Compare(alias, "right", true) == 0 ||
string.Compare(alias, "inner", true) == 0 || string.Compare(alias, "outer", true) == 0 ||
string.Compare(alias, "full", true) == 0)
{
continue;
}
if (!string.IsNullOrWhiteSpace(aliasTable) && !aliases.ContainsKey(alias))
{
aliases.Add(alias, new TableAlias()
{
Alias = alias, Database = aliasDatabase, Schema = aliasSchema, Table = aliasTable
});
}
}
}
}
string line = caretPosition.Y < (lines?.Length ?? 0) ? lines[caretPosition.Y] : null;
if (!string.IsNullOrWhiteSpace(line))
{
if (caretPosition.X < line.Length)
{
line = line.Substring(0, caretPosition.X);
}
var reParts = new Regex(@"((?<Part>(\w+|\[.*?]\]|`.*?`|"".*?""))\.)*([""`[]?(?<LastPart>\w*))$");
var matchParts = reParts.Match(line);
if (matchParts.Success)
{
foreach (Capture capture in matchParts.Groups["Part"].Captures)
{
parts.Add(capture.Value);
}
}
}
if (parts.Count == 0)
{
AddTablesAndProcedures(intellisense, itemCaptions, conn.DbConnection, null, null);
}
else if (parts.Count == 1 && !string.IsNullOrWhiteSpace(parts[0]))
{
var part0 = Utils.UnquoteString(parts[0]);
if (!string.IsNullOrWhiteSpace(part0))
{
AddTablesAndProcedures(intellisense, itemCaptions, conn.DbConnection, dbName, part0);
AddTablesAndProcedures(intellisense, itemCaptions, conn.DbConnection, part0, null);
AddTableColumns(intellisense, itemCaptions, conn.DbConnection, dbName, null, part0);
if (aliases.ContainsKey(part0))
{
var tableAlias = aliases[part0];
AddTableColumns(intellisense, itemCaptions, conn.DbConnection, tableAlias.Database ?? dbName, tableAlias.Schema, tableAlias.Table);
}
}
}
else if (parts.Count == 2 && !string.IsNullOrWhiteSpace(parts[0]))
{
AddTablesAndProcedures(intellisense, itemCaptions, conn.DbConnection, Utils.UnquoteString(parts[0]), Utils.UnquoteString(parts[1]));
}
else
{
AddTablesAndProcedures(intellisense, itemCaptions, conn.DbConnection, null, null);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
}