/// <summary>
/// Filters out metadata that is not present in the result set, and matches metadata ordering to resultset.
/// </summary>
public static EditColumnMetadata[] FilterColumnMetadata(EditColumnMetadata[] metaColumns, DbColumnWrapper[] resultColumns)
{
if (metaColumns.Length == 0)
{
return(metaColumns);
}
bool escapeColName = FromSqlScript.IsIdentifierBracketed(metaColumns[0].EscapedName);
Dictionary <string, int> columnNameOrdinalMap = new Dictionary <string, int>(capacity: resultColumns.Length);
for (int i = 0; i < resultColumns.Length; i++)
{
DbColumnWrapper column = resultColumns[i];
string columnName = column.ColumnName;
if (escapeColName && !FromSqlScript.IsIdentifierBracketed(columnName))
{
columnName = ToSqlScript.FormatIdentifier(columnName);
}
columnNameOrdinalMap.Add(columnName, column.ColumnOrdinal ?? i);
}
HashSet <string> resultColumnNames = columnNameOrdinalMap.Keys.ToHashSet();
metaColumns = Array.FindAll(metaColumns, column => resultColumnNames.Contains(column.EscapedName));
foreach (EditColumnMetadata metaCol in metaColumns)
{
metaCol.Ordinal = columnNameOrdinalMap[metaCol.EscapedName];
}
Array.Sort(metaColumns, (x, y) => (Comparer <int> .Default).Compare(x.Ordinal, y.Ordinal));
return(metaColumns);
}
public void DecodeMultipartIdentifierTest(string input, string[] output)
{
// If: I decode the input
string[] decoded = FromSqlScript.DecodeMultipartIdentifier(input);
// Then: The output should match what was expected
Assert.Equal(output, decoded);
}
/// <summary>
/// Generates a edit-ready metadata object using SMO
/// </summary>
/// <param name="connection">Connection to use for getting metadata</param>
/// <param name="objectName">Name of the object to return metadata for</param>
/// <param name="objectType">Type of the object to return metadata for</param>
/// <returns>Metadata about the object requested</returns>
public TableMetadata GetObjectMetadata(DbConnection connection, string schemaName, string objectName, string objectType)
{
// Get a connection to the database for SMO purposes
SqlConnection sqlConn = connection as SqlConnection;
if (sqlConn == null)
{
// It's not actually a SqlConnection, so let's try a reliable SQL connection
ReliableSqlConnection reliableConn = connection as ReliableSqlConnection;
if (reliableConn == null)
{
// If we don't have connection we can use with SMO, just give up on using SMO
return(null);
}
// We have a reliable connection, use the underlying connection
sqlConn = reliableConn.GetUnderlyingConnection();
}
// Connect with SMO and get the metadata for the table
ServerConnection serverConnection;
if (sqlConn.AccessToken == null)
{
serverConnection = new ServerConnection(sqlConn);
}
else
{
serverConnection = new ServerConnection(sqlConn, new AzureAccessToken(sqlConn.AccessToken));
}
Server server = new Server(serverConnection);
Database database = server.Databases[sqlConn.Database];
TableViewTableTypeBase smoResult;
switch (objectType.ToLowerInvariant())
{
case "table":
Table table = string.IsNullOrEmpty(schemaName) ? new Table(database, objectName) : new Table(database, objectName, schemaName);
table.Refresh();
smoResult = table;
break;
case "view":
View view = string.IsNullOrEmpty(schemaName) ? new View(database, objectName) : new View(database, objectName, schemaName);
view.Refresh();
smoResult = view;
break;
default:
throw new ArgumentOutOfRangeException(nameof(objectType), SR.EditDataUnsupportedObjectType(objectType));
}
if (smoResult == null)
{
throw new ArgumentOutOfRangeException(nameof(objectName), SR.EditDataObjectMetadataNotFound);
}
// Generate the edit column metadata
List <ColumnMetadata> editColumns = new List <ColumnMetadata>();
for (int i = 0; i < smoResult.Columns.Count; i++)
{
Column smoColumn = smoResult.Columns[i];
// The default value may be escaped
string defaultValue = smoColumn.DefaultConstraint == null
? null
: FromSqlScript.UnwrapLiteral(smoColumn.DefaultConstraint.Text);
ColumnMetadata column = new ColumnMetadata
{
DefaultValue = defaultValue,
EscapedName = ToSqlScript.FormatIdentifier(smoColumn.Name),
Ordinal = i
};
editColumns.Add(column);
}
// Only tables can be memory-optimized
Table smoTable = smoResult as Table;
bool isMemoryOptimized = smoTable != null && smoTable.IsMemoryOptimized;
// Escape the parts of the name
string[] objectNameParts = { smoResult.Schema, smoResult.Name };
string escapedMultipartName = ToSqlScript.FormatMultipartIdentifier(objectNameParts);
return(new TableMetadata
{
Columns = editColumns.ToArray(),
EscapedMultipartName = escapedMultipartName,
IsMemoryOptimized = isMemoryOptimized,
});
}
/// <summary>
/// Generates a edit-ready metadata object using SMO
/// </summary>
/// <param name="connection">Connection to use for getting metadata</param>
/// <param name="objectNamedParts">Split and unwrapped name parts</param>
/// <param name="objectType">Type of the object to return metadata for</param>
/// <returns>Metadata about the object requested</returns>
public EditTableMetadata GetObjectMetadata(DbConnection connection, string[] objectNamedParts, string objectType)
{
Validate.IsNotNull(nameof(objectNamedParts), objectNamedParts);
if (objectNamedParts.Length <= 0)
{
throw new ArgumentNullException(nameof(objectNamedParts), SR.EditDataMetadataObjectNameRequired);
}
if (objectNamedParts.Length > 2)
{
throw new InvalidOperationException(SR.EditDataMetadataTooManyIdentifiers);
}
// Get a connection to the database for SMO purposes
SqlConnection sqlConn = connection as SqlConnection;
if (sqlConn == null)
{
// It's not actually a SqlConnection, so let's try a reliable SQL connection
ReliableSqlConnection reliableConn = connection as ReliableSqlConnection;
if (reliableConn == null)
{
// If we don't have connection we can use with SMO, just give up on using SMO
return(null);
}
// We have a reliable connection, use the underlying connection
sqlConn = reliableConn.GetUnderlyingConnection();
}
// Connect with SMO and get the metadata for the table
ServerConnection serverConnection;
if (sqlConn.AccessToken == null)
{
serverConnection = new ServerConnection(sqlConn);
}
else
{
serverConnection = new ServerConnection(sqlConn, new AzureAccessToken(sqlConn.AccessToken));
}
Server server = new Server(serverConnection);
Database db = new Database(server, sqlConn.Database);
TableViewTableTypeBase smoResult;
switch (objectType.ToLowerInvariant())
{
case "table":
smoResult = objectNamedParts.Length == 1
? new Table(db, objectNamedParts[0]) // No schema provided
: new Table(db, objectNamedParts[1], objectNamedParts[0]); // Schema provided
break;
case "view":
smoResult = objectNamedParts.Length == 1
? new View(db, objectNamedParts[0]) // No schema provided
: new View(db, objectNamedParts[1], objectNamedParts[0]); // Schema provided
break;
default:
throw new ArgumentOutOfRangeException(nameof(objectType), SR.EditDataUnsupportedObjectType(objectType));
}
// A bug in SMO makes it necessary to call refresh to attain certain properties (such as IsMemoryOptimized)
smoResult.Refresh();
if (smoResult.State != SqlSmoState.Existing)
{
throw new ArgumentOutOfRangeException(nameof(objectNamedParts), SR.EditDataObjectNotFound);
}
// Generate the edit column metadata
List <EditColumnMetadata> editColumns = new List <EditColumnMetadata>();
for (int i = 0; i < smoResult.Columns.Count; i++)
{
Column smoColumn = smoResult.Columns[i];
string defaultValue = null;
try
{
// The default value may be escaped
defaultValue = smoColumn.DefaultConstraint == null
? null
: FromSqlScript.UnwrapLiteral(smoColumn.DefaultConstraint.Text);
}
catch (PropertyCannotBeRetrievedException)
{
// This exception will be thrown when the user doesn't have view definition privilege,
// we can ignore it and use null as the default value;
}
EditColumnMetadata column = new EditColumnMetadata
{
DefaultValue = defaultValue,
EscapedName = ToSqlScript.FormatIdentifier(smoColumn.Name),
Ordinal = i,
IsHierarchyId = smoColumn.DataType.SqlDataType == SqlDataType.HierarchyId,
};
editColumns.Add(column);
}
// Only tables can be memory-optimized
Table smoTable = smoResult as Table;
bool isMemoryOptimized = false;
// TODO: Remove IsSupported check once SMO fixes broken IsMemoryOptimized scenario (TFS #10871823)
if (smoTable != null)
{
isMemoryOptimized = smoTable.IsSupportedProperty("IsMemoryOptimized") && smoTable.IsMemoryOptimized;
}
// Escape the parts of the name
string[] objectNameParts = { smoResult.Schema, smoResult.Name };
string escapedMultipartName = ToSqlScript.FormatMultipartIdentifier(objectNameParts);
return(new EditTableMetadata
{
Columns = editColumns.ToArray(),
EscapedMultipartName = escapedMultipartName,
IsMemoryOptimized = isMemoryOptimized
});
}
public static string[] GetEditTargetName(EditInitializeParams initParams)
{
return(initParams.SchemaName != null
? new [] { initParams.SchemaName, initParams.ObjectName }
: FromSqlScript.DecodeMultipartIdentifier(initParams.ObjectName));
}
public void UnescapeTest(string input, string output)
{
Assert.Equal(output, FromSqlScript.UnwrapLiteral(input));
}
public void DecodeMultipartIdentifierFailTest(string input)
{
// If: I decode an invalid input
// Then: It should throw an exception
Assert.Throws <FormatException>(() => FromSqlScript.DecodeMultipartIdentifier(input));
}
public void BracketedIdentifierTest(string input, bool output)
{
Assert.Equal(output, FromSqlScript.IsIdentifierBracketed(input));
}
