/// <summary>
        /// Handle request to get Agent job activities
        /// </summary>
        internal async Task HandleAgentJobsRequest(AgentJobsParams parameters, RequestContext <AgentJobsResult> requestContext)
        {
            await Task.Run(async() =>
            {
                try
                {
                    var result = new AgentJobsResult();
                    ConnectionInfo connInfo;
                    ConnectionServiceInstance.TryFindConnection(
                        parameters.OwnerUri,
                        out connInfo);

                    if (connInfo != null)
                    {
                        var sqlConnection    = ConnectionService.OpenSqlConnection(connInfo);
                        var serverConnection = new ServerConnection(sqlConnection);
                        var fetcher          = new JobFetcher(serverConnection);
                        var filter           = new JobActivityFilter();
                        this.jobs            = fetcher.FetchJobs(filter);
                        var agentJobs        = new List <AgentJobInfo>();
                        if (this.jobs != null)
                        {
                            foreach (var job in this.jobs.Values)
                            {
                                agentJobs.Add(AgentUtilities.ConvertToAgentJobInfo(job));
                            }
                        }
                        result.Success = true;
                        result.Jobs    = agentJobs.ToArray();
                        sqlConnection.Close();
                    }
                    await requestContext.SendResult(result);
                }
                catch (Exception e)
                {
                    await requestContext.SendError(e);
                }
            });
        }
Beispiel #2
0
        /// <summary>
        /// a function which fetches notebooks jobs accessible to the user
        /// </summary>
        /// <param name="connInfo">connectionInfo generated from OwnerUri</param>
        /// <returns>array of agent notebooks</returns>
        public static async Task <AgentNotebookInfo[]> GetAgentNotebooks(ConnectionInfo connInfo)
        {
            AgentNotebookInfo[] result;
            // Fetching all agent Jobs accessible to the user
            var serverConnection = ConnectionService.OpenServerConnection(connInfo);
            var fetcher          = new JobFetcher(serverConnection);
            var filter           = new JobActivityFilter();
            var jobs             = fetcher.FetchJobs(filter);


            Dictionary <Guid, JobProperties> allJobsHashTable = new Dictionary <Guid, JobProperties>();

            if (jobs != null)
            {
                foreach (var job in jobs.Values)
                {
                    allJobsHashTable.Add(job.JobID, job);
                }
            }
            // Fetching notebooks across all databases accessible by the user
            string getJobIdsFromDatabaseQueryString =
                @"
            DECLARE @script AS VARCHAR(MAX)
            SET @script =
            '
            USE [?];
            IF EXISTS 
            (   
                SELECT * FROM INFORMATION_SCHEMA.TABLES 
                WHERE 
                TABLE_SCHEMA = N''notebooks'' 
                AND 
                TABLE_NAME = N''nb_template''
            )
            BEGIN
                SELECT 
                [notebooks].[nb_template].job_id,
                [notebooks].[nb_template].template_id,
                [notebooks].[nb_template].last_run_notebook_error,
                [notebooks].[nb_template].execute_database,
                DB_NAME() AS db_name                            
                FROM [?].notebooks.nb_template
                INNER JOIN
                msdb.dbo.sysjobs 
                ON
                [?].notebooks.nb_template.job_id = msdb.dbo.sysjobs.job_id
            END
            '
            EXEC sp_MSforeachdb @script";
            var     agentNotebooks = new List <AgentNotebookInfo>();
            DataSet jobIdsDataSet  = await ExecuteSqlQueries(connInfo, getJobIdsFromDatabaseQueryString);

            foreach (DataTable templateTable in jobIdsDataSet.Tables)
            {
                foreach (DataRow templateRow in templateTable.Rows)
                {
                    AgentNotebookInfo notebookJob =
                        AgentUtilities.ConvertToAgentNotebookInfo(allJobsHashTable[(Guid)templateRow["job_id"]]);
                    notebookJob.TemplateId           = templateRow["template_id"] as string;
                    notebookJob.TargetDatabase       = templateRow["db_name"] as string;
                    notebookJob.LastRunNotebookError = templateRow["last_run_notebook_error"] as string;
                    notebookJob.ExecuteDatabase      = templateRow["execute_database"] as string;
                    agentNotebooks.Add(notebookJob);
                }
            }
            result = agentNotebooks.ToArray();
            return(result);
        }