/// <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))); }