/// <summary>
/// executes sql queries required by other agent notebook helper functions
/// </summary>
/// <param name="connInfo">connectionInfo generated from OwnerUri</param>
/// <param name="sqlQuery">actual sql query to be executed</param>
/// <param name="queryParameters">sql parameters required by the query</param>
/// <param name="targetDatabase">the database on which the query will be executed</param>
/// <returns></returns>
public static async Task <DataSet> ExecuteSqlQueries(
ConnectionInfo connInfo,
string sqlQuery,
List <SqlParameter> queryParameters = null,
string targetDatabase = null)
{
DataSet result = new DataSet();
string originalConnectionDatabase = connInfo.ConnectionDetails.DatabaseName;
if (!string.IsNullOrWhiteSpace(targetDatabase))
{
connInfo.ConnectionDetails.DatabaseName = targetDatabase;
}
using (SqlConnection connection = new SqlConnection(ConnectionService.BuildConnectionString(connInfo.ConnectionDetails)))
{
await connection.OpenAsync();
using (SqlCommand sqlQueryCommand = new SqlCommand(sqlQuery, connection))
{
if (queryParameters != null)
{
sqlQueryCommand.Parameters.AddRange(queryParameters.ToArray());
}
SqlDataAdapter sqlCommandAdapter = new SqlDataAdapter(sqlQueryCommand);
sqlCommandAdapter.Fill(result);
}
}
connInfo.ConnectionDetails.DatabaseName = originalConnectionDatabase;
return(result);
}
internal static SqlConnection GetSqlConnection(ConnectionInfo connInfo)
{
try
{
// increase the connection timeout to at least 30 seconds and and build connection string
// enable PersistSecurityInfo to handle issues in SMO where the connection context is lost in reconnections
int? originalTimeout = connInfo.ConnectionDetails.ConnectTimeout;
bool?originalPersistSecurityInfo = connInfo.ConnectionDetails.PersistSecurityInfo;
connInfo.ConnectionDetails.ConnectTimeout = Math.Max(30, originalTimeout ?? 0);
connInfo.ConnectionDetails.PersistSecurityInfo = true;
string connectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
connInfo.ConnectionDetails.ConnectTimeout = originalTimeout;
connInfo.ConnectionDetails.PersistSecurityInfo = originalPersistSecurityInfo;
// open a dedicated binding server connection
SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
return(sqlConn);
}
catch (Exception)
{
}
return(null);
}
public static ServerConnection GetServerConnection(ConnectionInfo connection)
{
string connectionString = ConnectionService.BuildConnectionString(connection.ConnectionDetails);
var sqlConnection = new SqlConnection(connectionString);
return(new ServerConnection(sqlConnection));
}
private async Task <Mock <RequestContext <ScriptingResult> > > SendAndValidateScriptRequest(bool isSelectScript)
{
var result = GetLiveAutoCompleteTestObjects();
var requestContext = new Mock <RequestContext <ScriptingResult> >();
requestContext.Setup(x => x.SendResult(It.IsAny <ScriptingResult>())).Returns(Task.FromResult(new object()));
var scriptingParams = new ScriptingParams
{
OwnerUri = ConnectionService.BuildConnectionString(result.ConnectionInfo.ConnectionDetails)
};
if (isSelectScript)
{
scriptingParams.ScriptOptions = new ScriptOptions {
ScriptCreateDrop = "ScriptSelect"
};
List <ScriptingObject> scriptingObjects = new List <ScriptingObject>();
scriptingObjects.Add(new ScriptingObject {
Type = "View", Name = "sysobjects", Schema = "sys"
});
scriptingParams.ScriptingObjects = scriptingObjects;
}
ScriptingService service = new ScriptingService();
await service.HandleScriptExecuteRequest(scriptingParams, requestContext.Object);
return(requestContext);
}
public void BuildConnectionStringWithInvalidAuthType()
{
ConnectionDetails details = TestObjects.GetTestConnectionDetails();
details.AuthenticationType = "NotAValidAuthType";
Assert.Throws <ArgumentException>(() => ConnectionService.BuildConnectionString(details));
}
protected DacFxOperation(ConnectionInfo connInfo)
{
Validate.IsNotNull("connectionInfo", connInfo);
Validate.IsNotNull("connectionDetails", connInfo.ConnectionDetails);
this.ConnInfo = connInfo;
this.ConnectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
this.OperationId = Guid.NewGuid().ToString();
}
private static DbDataReader GetReader(TestResultSet[] dataSet, bool throwOnRead, string query)
{
var info = Common.CreateTestConnectionInfo(dataSet, false, throwOnRead);
var connection = info.Factory.CreateSqlConnection(ConnectionService.BuildConnectionString(info.ConnectionDetails), null);
var command = connection.CreateCommand();
command.CommandText = query;
return(command.ExecuteReader());
}
// helper method to set up a Sql Connection to a database
private SqlConnection SetUpConnection(string name)
{
SqlTestDb testDb = SqlTestDb.CreateNew(TestServerType.OnPrem, false, name);
ConnectionInfo connInfo = LiveConnectionHelper.InitLiveConnectionInfoForDefinition(testDb.DatabaseName);
string connectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
SqlConnection resultConnection = new SqlConnection(connectionString);
resultConnection.Open();
return resultConnection;
}
private string GetConnectionString(ConnectionInfo connInfo, string databaseName)
{
if (connInfo == null)
{
return(null);
}
connInfo.ConnectionDetails.DatabaseName = databaseName;
return(ConnectionService.BuildConnectionString(connInfo.ConnectionDetails));
}
/// <summary>
/// Use a ConnectionInfo item to create a connected binding context
/// </summary>
/// <param name="connInfo"></param>
public virtual string AddConnectionContext(ConnectionInfo connInfo)
{
if (connInfo == null)
{
return(string.Empty);
}
// lookup the current binding context
string connectionKey = GetConnectionContextKey(connInfo);
IBindingContext bindingContext = this.GetOrCreateBindingContext(connectionKey);
if (bindingContext.BindingLock.WaitOne())
{
try
{
bindingContext.BindingLock.Reset();
// increase the connection timeout to at least 30 seconds and and build connection string
// enable PersistSecurityInfo to handle issues in SMO where the connection context is lost in reconnections
int? originalTimeout = connInfo.ConnectionDetails.ConnectTimeout;
bool?originalPersistSecurityInfo = connInfo.ConnectionDetails.PersistSecurityInfo;
connInfo.ConnectionDetails.ConnectTimeout = Math.Max(DefaultMinimumConnectionTimeout, originalTimeout ?? 0);
connInfo.ConnectionDetails.PersistSecurityInfo = true;
string connectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
connInfo.ConnectionDetails.ConnectTimeout = originalTimeout;
connInfo.ConnectionDetails.PersistSecurityInfo = originalPersistSecurityInfo;
// open a dedicated binding server connection
SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
// populate the binding context to work with the SMO metadata provider
ServerConnection serverConn = new ServerConnection(sqlConn);
bindingContext.SmoMetadataProvider = SmoMetadataProvider.CreateConnectedProvider(serverConn);
bindingContext.MetadataDisplayInfoProvider = new MetadataDisplayInfoProvider();
bindingContext.MetadataDisplayInfoProvider.BuiltInCasing =
this.CurrentSettings.SqlTools.IntelliSense.LowerCaseSuggestions.Value
? CasingStyle.Lowercase : CasingStyle.Uppercase;
bindingContext.Binder = BinderProvider.CreateBinder(bindingContext.SmoMetadataProvider);
bindingContext.ServerConnection = serverConn;
bindingContext.BindingTimeout = ConnectedBindingQueue.DefaultBindingTimeout;
bindingContext.IsConnected = true;
}
catch (Exception)
{
bindingContext.IsConnected = false;
}
finally
{
bindingContext.BindingLock.Set();
}
}
return(connectionKey);
}
/// <summary>
/// Wrapper method that calls Resolver.FindCompletions
/// </summary>
/// <param name="objectName"></param>
/// <param name="schemaName"></param>
/// <param name="objectType"></param>
/// <param name="tempFileName"></param>
/// <returns></returns>
internal SmoScriptingOperation InitScriptOperation(string objectName, string schemaName, string objectType)
{
// object that has to be scripted
ScriptingObject scriptingObject = new ScriptingObject
{
Name = objectName,
Schema = schemaName,
Type = objectType
};
// scripting options
ScriptOptions options = new ScriptOptions
{
ScriptCreateDrop = "ScriptCreate",
TypeOfDataToScript = "SchemaOnly",
ScriptStatistics = "ScriptStatsNone",
TargetDatabaseEngineEdition = GetTargetDatabaseEngineEdition(),
TargetDatabaseEngineType = GetTargetDatabaseEngineType(),
ScriptCompatibilityOption = GetScriptCompatibilityOption(),
ScriptExtendedProperties = false,
ScriptUseDatabase = false,
IncludeIfNotExists = false,
GenerateScriptForDependentObjects = false,
IncludeDescriptiveHeaders = false,
ScriptCheckConstraints = false,
ScriptChangeTracking = false,
ScriptDataCompressionOptions = false,
ScriptForeignKeys = false,
ScriptFullTextIndexes = false,
ScriptIndexes = false,
ScriptPrimaryKeys = false,
ScriptTriggers = false,
UniqueKeys = false
};
List <ScriptingObject> objectList = new List <ScriptingObject>();
objectList.Add(scriptingObject);
// create parameters for the scripting operation
ScriptingParams parameters = new ScriptingParams
{
ConnectionString = ConnectionService.BuildConnectionString(this.connectionInfo.ConnectionDetails),
ScriptingObjects = objectList,
ScriptOptions = options,
ScriptDestination = "ToEditor"
};
return(new ScriptAsScriptingOperation(parameters, serverConnection));
}
/// <summary>
/// Handles request to start the scripting operation
/// </summary>
public async Task HandleScriptExecuteRequest(ScriptingParams parameters, RequestContext <ScriptingResult> requestContext)
{
SmoScriptingOperation operation = null;
try
{
// if a connection string wasn't provided as a parameter then
// use the owner uri property to lookup its associated ConnectionInfo
// and then build a connection string out of that
ConnectionInfo connInfo = null;
string accessToken = null;
if (parameters.ConnectionString == null)
{
ScriptingService.ConnectionServiceInstance.TryFindConnection(parameters.OwnerUri, out connInfo);
if (connInfo != null)
{
parameters.ConnectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
accessToken = connInfo.ConnectionDetails.AzureAccountToken;
}
else
{
throw new Exception("Could not find ConnectionInfo");
}
}
if (parameters.FilePath == null)
{
// Create a temporary and random path to handle this operation
parameters.FilePath = Path.GetTempFileName();
}
if (!ShouldCreateScriptAsOperation(parameters))
{
operation = new ScriptingScriptOperation(parameters, accessToken);
}
else
{
operation = new ScriptAsScriptingOperation(parameters, accessToken);
}
operation.PlanNotification += (sender, e) => requestContext.SendEvent(ScriptingPlanNotificationEvent.Type, e).Wait();
operation.ProgressNotification += (sender, e) => requestContext.SendEvent(ScriptingProgressNotificationEvent.Type, e).Wait();
operation.CompleteNotification += (sender, e) => this.SendScriptingCompleteEvent(requestContext, ScriptingCompleteEvent.Type, e, operation, parameters.ScriptDestination);
RunTask(requestContext, operation);
}
catch (Exception e)
{
await requestContext.SendError(e);
}
}
public void ReliableSqlConnectionUsesAzureToken()
{
ConnectionDetails details = TestObjects.GetTestConnectionDetails();
details.UserName = "";
details.Password = "";
string connectionString = ConnectionService.BuildConnectionString(details);
string azureAccountToken = "testAzureAccountToken";
RetryPolicy retryPolicy = RetryPolicyFactory.CreateDefaultConnectionRetryPolicy();
// If I create a ReliableSqlConnection using an azure account token
var reliableConnection = new ReliableSqlConnection(connectionString, retryPolicy, retryPolicy, azureAccountToken);
// Then the connection's azureAccountToken gets set
Assert.Equal(azureAccountToken, reliableConnection.GetUnderlyingConnection().AccessToken);
}
public void ConnectingWithOptionalParametersBuildsConnectionString(string propertyName, object propertyValue, string connectionStringMarker)
{
// Create a test connection details object and set the property to a specific value
ConnectionDetails details = TestObjects.GetTestConnectionDetails();
PropertyInfo info = details.GetType().GetProperty(propertyName);
info.SetValue(details, propertyValue);
// Test that a connection string can be created without exceptions
string connectionString = ConnectionService.BuildConnectionString(details);
Assert.NotNull(connectionString);
Assert.NotEmpty(connectionString);
// Verify that the parameter is in the connection string
Assert.True(connectionString.Contains(connectionStringMarker));
}
/// <summary>
/// Handles request to start the scripting operation
/// </summary>
public async Task HandleScriptExecuteRequest(ScriptingParams parameters, RequestContext <ScriptingResult> requestContext)
{
try
{
// if a connection string wasn't provided as a parameter then
// use the owner uri property to lookup its associated ConnectionInfo
// and then build a connection string out of that
ConnectionInfo connInfo = null;
if (parameters.ConnectionString == null || parameters.ScriptOptions.ScriptCreateDrop == "ScriptSelect")
{
ScriptingService.ConnectionServiceInstance.TryFindConnection(parameters.OwnerUri, out connInfo);
if (connInfo != null)
{
parameters.ConnectionString = ConnectionService.BuildConnectionString(connInfo.ConnectionDetails);
}
else
{
throw new Exception("Could not find ConnectionInfo");
}
}
// if the scripting operation is for SELECT then handle that message differently
// for SELECT we'll build the SQL directly whereas other scripting operations depend on SMO
if (parameters.ScriptOptions.ScriptCreateDrop == "ScriptSelect")
{
RunSelectTask(connInfo, parameters, requestContext);
}
else
{
ScriptingScriptOperation operation = new ScriptingScriptOperation(parameters);
operation.PlanNotification += (sender, e) => requestContext.SendEvent(ScriptingPlanNotificationEvent.Type, e).Wait();
operation.ProgressNotification += (sender, e) => requestContext.SendEvent(ScriptingProgressNotificationEvent.Type, e).Wait();
operation.CompleteNotification += (sender, e) => this.SendScriptingCompleteEvent(requestContext, ScriptingCompleteEvent.Type, e, operation, parameters.ScriptDestination);
RunTask(requestContext, operation);
}
}
catch (Exception e)
{
await requestContext.SendError(e);
}
}
/// <summary>
/// Executes this query asynchronously and collects all result sets
/// </summary>
private async Task ExecuteInternal()
{
// Mark that we've internally executed
hasExecuteBeenCalled = true;
// Don't actually execute if there aren't any batches to execute
if (Batches.Length == 0)
{
return;
}
// Open up a connection for querying the database
string connectionString = ConnectionService.BuildConnectionString(editorConnection.ConnectionDetails);
// TODO: Don't create a new connection every time, see TFS #834978
using (DbConnection conn = editorConnection.Factory.CreateSqlConnection(connectionString))
{
try
{
await conn.OpenAsync();
}
catch (Exception exception)
{
this.HasExecuted = true;
if (QueryConnectionException != null)
{
await QueryConnectionException(exception.Message);
}
return;
}
ReliableSqlConnection sqlConn = conn as ReliableSqlConnection;
if (sqlConn != null)
{
// Subscribe to database informational messages
sqlConn.GetUnderlyingConnection().InfoMessage += OnInfoMessage;
}
try
{
// We need these to execute synchronously, otherwise the user will be very unhappy
foreach (Batch b in Batches)
{
b.BatchStart += BatchStarted;
b.BatchCompletion += BatchCompleted;
b.ResultSetCompletion += ResultSetCompleted;
await b.Execute(conn, cancellationSource.Token);
}
// Call the query execution callback
if (QueryCompleted != null)
{
await QueryCompleted(this);
}
}
catch (Exception)
{
// Call the query failure callback
if (QueryFailed != null)
{
await QueryFailed(this);
}
}
finally
{
if (sqlConn != null)
{
// Subscribe to database informational messages
sqlConn.GetUnderlyingConnection().InfoMessage -= OnInfoMessage;
}
}
// TODO: Close connection after eliminating using statement for above TODO
}
}
public static ServerConnection InitLiveServerConnectionForDefinition(ConnectionInfo connInfo)
{
SqlConnection sqlConn = new SqlConnection(ConnectionService.BuildConnectionString(connInfo.ConnectionDetails));
return(new ServerConnection(sqlConn));
}
private static DbConnection GetConnection(ConnectionInfo info)
{
return(info.Factory.CreateSqlConnection(ConnectionService.BuildConnectionString(info.ConnectionDetails)));
}