Tutorial 3: How to import RSS feeds into MS SQL 2008 (Part 3)

Part 1 | Part 2 | Part 3 | Part 4

In the previous part of this tutorial you created the script tasks and
containers to download RSS feeds and iterate through every XML file. This part
shows the code listing of the earlier created RSS import Script Task inside the
Foreach Loop Container.
Place the following Code listings inside the RSS import Script Task.

Using Directives

Add the following using directives at the top of the C# script.

using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

ScriptMain class

Replace the existing Main() method by the following code. The Main() method is
executed for each XML file in the earlier specified folder. If you are familiar
with ADO.Net programming, notice how different the Connection object is created
and that it's Close() method is never called. The SQL import itself is done by
the AddHeadline() method. It's called for every item in the RSS feed XML file.
The method calls a stored procedure named 'fan.TutorialAddHeadline', which is
described in the next part of this tutorial.

Things you might want to change in the code:

1. Server name: Change 'Data Source=sql1' in the ConnectDB() method to
your server name

2. Database name: Change 'Initial Catalog=CelebrityDB' in the ConnectDB() method
to your db name

private string CurrentFilePath;
private SqlConnection con;

public void Main()
{

      // TODO: Add your code here
      CurrentFilePath = (string)Dts.Variables["CurrentFilePath"].Value;
      try
      {
         ConnectDB();
         ImportRSS(CurrentFilePath);
         Dts.TaskResult = (int)ScriptResults.Success;
     }
     catch (Exception)
     {
         Dts.TaskResult = (int)ScriptResults.Failure;
     }
 }


private void ConnectDB()
{
    ConnectionManager cm = Dts.Connections.Add("ADO.NET");
     cm.ConnectionString =
 "Data Source=sql1;Initial Catalog=CelebrityDB;Integrated Security=SSPI;";
     con = (SqlConnection)cm.AcquireConnection(null);
 }


private void AddHeadline(string pTitle, string pContent,string pPublisher, string pLinkurl)
{
     SqlCommand c = con.CreateCommand();
     c.CommandType = CommandType.StoredProcedure;

     // Enter name of your stored procedure here
     c.CommandText = "fan.TutorialAddHeadline";

     // Change parameter names of  your stored procedure here
     SqlParameter sqlTitle = new SqlParameter("@title", pTitle);
     SqlParameter sqlContent = new SqlParameter("@content", pContent);
     SqlParameter sqlPublisher = new SqlParameter("@publisher", pPublisher);
     SqlParameter sqlLink = new SqlParameter("@linkurl", pLinkurl);

     sqlTitle.Direction = ParameterDirection.Input;
     sqlContent.Direction = ParameterDirection.Input;
     sqlPublisher.Direction = ParameterDirection.Input;
     sqlLink.Direction = ParameterDirection.Input;

     c.Parameters.Add(sqlTitle);
     c.Parameters.Add(sqlContent);
     c.Parameters.Add(sqlPublisher);
     c.Parameters.Add(sqlLink);

     c.ExecuteNonQuery();
}


private void ImportRSS(string filename)
{
    XmlDocument doc = new XmlDocument();
    doc.Load(filename);

    XmlNamespaceManager xnm = new XmlNamespaceManager(doc.NameTable);
    xnm.AddNamespace("dc","http://purl.org/dc/elements/1.1/");

    XmlNodeList itemList = doc.GetElementsByTagName("item");

    XmlNode xcurrent, xtitle, xlink, xdescription, xcreator;
    string title , content, publisher, link;

    for (int i = 0; i < itemList.Count; i++)
    {
         xcurrent = itemList[i];
         xtitle = xcurrent.SelectSingleNode("title");
         xlink = xcurrent.SelectSingleNode("link");
         xdescription = xcurrent.SelectSingleNode("description");
         xcreator = xcurrent.SelectSingleNode("dc:creator", xnm);
         title = ""; content = ""; publisher = ""; link = "";
         if (xtitle != null)
             title = xtitle.InnerText;
        if (xdescription != null)
            content = xdescription.InnerText;
        if (xcreator != null)
            publisher = xcreator.InnerText;     
        if (xlink != null)
            link =  xlink.InnerText;

        AddHeadline(title, content, publisher, link);
     }
 }

Next: Tutorial Part 4

In the next part the stored procedures and tables are created and the imported data is merged using the new MERGE SQL Command.