SqlDependency on a stored procedure using Dapper

485 Views Asked by At

Trying to learn SignalR. I have the following piece of code

 using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(@"SELECT [JobID],[Name],[LastExecutionDate],[Status]
           FROM [dbo].[JobInfo]", connection))
            {
                command.Notification = null;

                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (var reader = command.ExecuteReader())
                    return reader.Cast<IDataRecord>()
                        .Select(x => new JobInfo()
                        {
                            JobID = x.GetInt32(0),
                            Name = x.GetString(1),
                            LastExecutionDate = x.GetDateTime(2),
                            Status = x.GetString(3)
                        }).ToList();

            }
        }

Now instead of hardcoding select statement, I have a stored procedure called usp_GetJobs and I am using Dapper.

 var jobs = new List<Job>();

        using (var connection = new SqlConnection(connectionString))
        {
            using (var command = new SqlCommand("[dbo].usp_GetJobs", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                var dependency = new SqlDependency(command);

                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                jobs = connection.Query<Job>("[dbo].usp_GetJobs", null, commandType: CommandType.StoredProcedure).ToList();
            }
            

        }

I am little bit confused about how will the command be dependent on the stored procedure returning jobs. Something is not right. Can someone please help?

0

There are 0 best solutions below