CLRTriggers 4

CustomerTrigger.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Weather.com.Client;
using Weather.com.Client.WeatherConditions.Forecasts;
using Weather.com.Client.WeatherConditions.Location;

namespace Mulawa.SqlServer.CLRTriggers
{
    public class Triggers
    {
        [SqlTrigger(Name = "EmployeeTrigger"
            , Target = "dbo.Employee"
            , Event = "FOR INSERT")]
        public static void EmployeeTrigger()
        {
            using (SqlConnection connection = 
                new SqlConnection("context connection = true"))
            {
                
                connection.Open();
                TriggerConfiguration configuration = GetConfiguration(connection);

                if(configuration == null)
                    throw new Exception("Missing Configuration");


                SqlTriggerContext context = SqlContext.TriggerContext;
                if (context.TriggerAction == TriggerAction.Insert)
                {
                    SendWeatherInfoToEmployees(connection, configuration);
                }
            }
        }

        private static void SendWeatherInfoToEmployees(SqlConnection connection
            , TriggerConfiguration configuration)
        {
            List<Employee> employees = GetEmployees(connection);
            WeatherClient weatherClient = new WeatherClient(
                configuration.PartnerId, 
                configuration.LicenseKey);
            foreach (Employee employee in employees)
            {
                string employeeWeatherInfo = string.Empty;
                List<Location> locations = weatherClient.GetLocation(
                    string.Format("{0},{1}", employee.Country, employee.City));
                if (locations != null && locations.Count > 0)
                {
                    ForecastWeatherInfo forecast = weatherClient.GetWeatherForecast(
                        locations[0], 
                        configuration.WeatherForecastDays);
                    foreach (DailyForecast dailyForecast in forecast.DailyForecasts)
                    {
                        employeeWeatherInfo += string.Format(
                            "day: {0}, highest daily temperature {1}{2}"
                            +Environment.NewLine,
                             dailyForecast.Date, 
                             dailyForecast.HighestDailyTemprature,
                             forecast.Units.TemperatureUnit);
                    }
                    SqlContext.Pipe.Send(employeeWeatherInfo);
                    //SmtpClient client = new SmtpClient("SMTP.server.com");
                    //client.Send("[email protected]",
                    //employee.Email,"Weather Forecast",weatherInfo);
                }
                
            }
        }

        private static TriggerConfiguration GetConfiguration(SqlConnection connection)
        {
            TriggerConfiguration configuration = null;
            SqlCommand configurationCommand = connection.CreateCommand();
            configurationCommand.CommandText = @"SELECT PartnerId
                               , LicenseKey
                               , WeatherForecastDays 
                                 FROM dbo.Configuration";
            using(SqlDataReader reader = configurationCommand.ExecuteReader())
            {
                if (reader.Read())
                {
                    configuration = GetConfiguration(reader);
                }
            }

            return configuration;
        }

        private static TriggerConfiguration GetConfiguration(SqlDataReader reader)
        {
            TriggerConfiguration configuration;
            configuration = new TriggerConfiguration();
            configuration.PartnerId = (string)reader["PartnerId"];
            configuration.LicenseKey = (string)reader["LicenseKey"];
            configuration.WeatherForecastDays = (int)reader["WeatherForecastDays"];
            return configuration;
        }

        private static List<Employee> GetEmployees(SqlConnection connection)
        {
            List<Employee> employees = new List<Employee>();
            SqlCommand insertedEmployeesCmd = connection.CreateCommand();
            insertedEmployeesCmd.CommandText = @"SELECT FullName
                            , City
                            , Country
                            , Email 
                            FROM Inserted";
            using(SqlDataReader employeeReader = 
                insertedEmployeesCmd.ExecuteReader())
            {
                while (employeeReader.Read())
                {
                    Employee employee = new Employee();
                    employee.FullName = (string)employeeReader["FullName"];
                    employee.City = (string)employeeReader["City"];
                    employee.Country = (string)employeeReader["Country"];
                    employee.Email = (string)employeeReader["Email"];
                    employees.Add(employee);
                }
            }
            return employees;
        }
    }
    
}

Employee.cs

internal class Employee
{
    public string FullName { get; set; }

    public string City { get; set; }

    public string Country { get; set; }

    public string Email { get; set; }
}

TriggerConfiguration.cs

namespace Mulawa.SqlServer.CLRTriggers
{
    public class TriggerConfiguration
    {
        public string PartnerId { get; set; }

        public string LicenseKey { get; set; }

        public int WeatherForecastDays { get; set; }        
    }
}