Wednesday, October 3, 2012

Creating Data-driven Subscription Programmatically using C# – Delivery Extension : Email

In my last post I have mentioned I will write a post about creating Data-driven subscription programmatically and this is it. I am creating this subscription on Microsoft SQL Server 2008R2 using the method   CreateDataDrivenSubscription in ReportService2010 web service. In one of my last posts I wrote about Accessing Report Server using Report Server Web Service.

Reporting Services includes an e-mail delivery extension and a file share delivery extension. In here I will be using Email  Delivery Extension.

Before creating the Data-driven subscription make sure SQL Server Agent service is up and running. That’s because what really happens when you create Data-driven subscription is, there will be job created in SQL Server.
Untitled
SQL Server Agent
Without much information, I am pasting the code down here with all the comments. I have added a service reference to ReportService2010 web service.
using System;
using System.Globalization;
using System.Net;
using System.Security.Principal;
using System.Web.Services.Protocols;
using System.Windows.Forms;
using DataDrivenSubscriptionWebApp.wsReportService;

namespace DataDrivenSubscriptionWebApp
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            CreateDataDrivenSubscription();
        }

        private static void CreateDataDrivenSubscription()
        {
            // connecting to report server web service
            var clientCredentials = new NetworkCredential("administrator", "123@com");
            var rs = new ReportingService2010SoapClient();
            if (rs.ClientCredentials != null)
            {
                rs.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation;
                rs.ClientCredentials.Windows.ClientCredential = clientCredentials;
            }
            rs.Open();

            var oTrustedUserHeader = new TrustedUserHeader();

            // report which i am creating data-driven subscription for
            const string report = "/My Reports/Reports/Report1";
            const string description = "Programmatic Data Driven Subscription for Email";

            // Set the extension setting as report server email.
            var settings = new ExtensionSettings {Extension = "Report Server Email"};

            // Set the extension parameter values.
            var extensionParams = new ParameterValueOrFieldReference[8];

            var to = new ParameterFieldReference {ParameterName = "TO", FieldAlias = "EMAIL"}; // Data-driven.
            extensionParams[0] = to;

            var replyTo = new ParameterValue {Name = "ReplyTo", Value = "jaliya.udagedara@gmail.com"};
            extensionParams[1] = replyTo;

            var includeReport = new ParameterValue {Name = "IncludeReport", Value = "False"};
            extensionParams[2] = includeReport;

            var renderFormat = new ParameterValue {Name = "RenderFormat", Value = "HTML4.0"};
            extensionParams[3] = renderFormat;

            var priority = new ParameterValue {Name = "Priority", Value = "NORMAL"};
            extensionParams[4] = priority;

            var subject = new ParameterValue {Name = "Subject", Value = "Subsribed Report"};
            extensionParams[5] = subject;

            var comment = new ParameterValue {Name = "Comment", Value = "Here is the link to your report."};
            extensionParams[6] = comment;

            var includeLink = new ParameterValue {Name = "IncludeLink", Value = "True"};
            extensionParams[7] = includeLink;

            settings.ParameterValues = extensionParams;

            // Create the data source for the delivery query.
            var delivery = new DataSource {Name = ""};
            var dataSourceDefinition = new DataSourceDefinition
                                           {
                                               ConnectString = "Data Source=(local);Initial Catalog=SAMPLE_DB",
                                               CredentialRetrieval = CredentialRetrievalEnum.Store,
                                               Enabled = true,
                                               EnabledSpecified = true,
                                               Extension = "SQL",
                                               ImpersonateUserSpecified = false,
                                               UserName = "sa",
                                               Password = "sa"
                                           };
            delivery.Item = dataSourceDefinition;

            // Create the data set for the delivery query.
            var dataSetDefinition = new DataSetDefinition
                                        {
                                            AccentSensitivitySpecified = false,
                                            CaseSensitivitySpecified = false,
                                            KanatypeSensitivitySpecified = false,
                                            WidthSensitivitySpecified = false
                                        };

            var queryDefinition = new QueryDefinition
                                      {
                                          CommandText = "SELECT EMAIL from RECIPIENT",
                                          CommandType = "Text",
                                          Timeout = 45,
                                          TimeoutSpecified = true
                                      };
            dataSetDefinition.Query = queryDefinition;
            var results = new DataSetDefinition();
            var oServerInfoHeader = new ServerInfoHeader();

            bool changed;
            string[] paramNames;
            oServerInfoHeader = rs.PrepareQuery(oTrustedUserHeader, delivery, dataSetDefinition, out results, out changed,
                                                out paramNames);

            var dataRetrieval = new DataRetrievalPlan {DataSet = results, Item = dataSourceDefinition};

            // Set the event type and match data for the delivery.
            const string eventType = "TimedSubscription";
            const string matchData = "<ScheduleDefinition><StartDateTime>2012-10-01T14:00:00-07:00</StartDateTime><WeeklyRecurrence><WeeksInterval>1</WeeksInterval><DaysOfWeek><Monday>True</Monday><Tuesday>True</Tuesday><Wednesday>True</Wednesday><Thursday>True</Thursday><Friday>True</Friday></DaysOfWeek></WeeklyRecurrence></ScheduleDefinition>";

            //const string eventType = "SnapshotUpdated";
            //const string matchData = null;

            // Set the report parameter values.
            var parameters = new ParameterValueOrFieldReference[1];

            // i am retrieving value EMAIL from database and I am passing that value as my report parameter value
            var reportparam = new ParameterFieldReference {ParameterName = "ReportParameter1", FieldAlias = "EMAIL"}; // Data-driven.
            
            parameters[0] = reportparam;

            try
            {
                string subscriptionId = "";
                oServerInfoHeader = rs.CreateDataDrivenSubscription(oTrustedUserHeader, report, settings, dataRetrieval,
                                                                    description, eventType, matchData, parameters,
                                                                    out subscriptionId);
            }
            catch (SoapException ex)
            {
                MessageBox.Show(ex.Detail.InnerText.ToString(CultureInfo.InvariantCulture));
            }
            rs.Close();
        }
    }
}
Once you run this, you can see that there is Data-driven subscription created under your report.
Untitled1
Manage Report
Untitled2
Subscription
And you can also see a job created under SQL Server Agent in SQL Server Management Studio.
Untitled4
SQL Server Agent
You can manually start a job in SQL Server Agent by running following command in Management Studio query window.
USE msdb 
EXEC sp_start_job @job_name = '025B869B-BA52-4337-BC8B-DC65383EAC7C' --your job id
Hope this helps.

Happy Coding.

Regards,
Jaliya

2 comments:

  1. thank for the information and .net grid tutorial is also helpful dapfor. com

    ReplyDelete
  2. Hi Jaliya,

    I actually tried someting similar to this. The only change I made was to the Dataset.query and some parameters. The subscription runs fine. But when someone opens the subscription to change some extension setting values(TO, CC, BCC), it seems like they are set to empty instead of showing the column names I was using. Is there a fix for this? I just set the query for the dataset by modifying the query property.

    Thank you

    ReplyDelete