Published Sunday 20 December 2009
Categories: Projects |

Share this Share on Facebook Share on Twitter Share on Google Plus
Post View Counter 121

On the recently redesigned Virtual Swanage website we thought it would be useful to have a Twitter feed with daily events being submitted once a day from the websites event diary.

We considered manually updating the site every day and adding the current events to twitter but felt this would be a bit repetitive!

The Atlas CMS system we have developed already has Twitter intergration using Twitterizer.Framework (http://code.google.com/p/twitterizer/) which is a .NET Twitter interface which is very easy to use.

[more]

The aim was to make a console application using C# which could run on the same server as the Virtual Swanage website and be able to access the SQL Server database once a day to update Twitter with the days events.

The database is setup with the following SQL script

USE [dbname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE
[dbo].[SiteEvents](
[pkey] [int] IDENTITY(1,1) NOT NULL,
[EventTitle] [varchar](50) NULL,
[EvStart] [datetime] NULL,
[EvEnd] [datetime] NULL,
[EvActive] [tinyint] NULL,
[EvContent] [text] NULL,
[lastupdated] [datetime] NULL,
[updateby] [varchar](50) NULL,
[ItemOwner] [varchar](50) NULL,
[EvLocation] [varchar](250) NULL,
[EvMap] [varchar](250) NULL,
[EvCat] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

This will create the events table in your database.

In Visual Studio (express version is ok) create a new C# console application and add the Twitterizer.Framework as a reference and then enter the following code for your project. Change the username, password and database logins to work with your server.

When you compile and run this application it will update your twitter account with todays events. You can use the Task Scheduler to run this application every day to automatically update your twitter status.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Twitterizer.Framework;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace VsTwitter
{
class Program
{
static DataSet dsEvents = new DataSet();
static void Main(string[] args)
{
try
{
LoadDs();
ProcessDate();
}
catch (Exception e) { Console.WriteLine(e.ToString()); }

}
static void ProcessDate()
{
try
{

DataView evView = new DataView(dsEvents.Tables["SiteEvents"]);
evView.Sort = "EvStart ASC";

foreach (DataRowView myDRV in evView)
{

string tweetstr = "Event: " + myDRV["EventTitle"].ToString().Replace("&", "&").Replace("'", "'");
if (DateTime.Parse(myDRV["EvStart"].ToString()).ToString("HH:mm") != "00:00")
{
tweetstr += " today from " + DateTime.Parse(myDRV["EvStart"].ToString()).ToString("HH:mm");
}
tweetstr += " http://www.virtual-swanage.co.uk/?e=" + myDRV["pkey"].ToString();
//Console.WriteLine(tweetstr);

SendTwitter(tweetstr);

}

evView.Dispose();
}
catch { }

}
static void SendTwitter(string inval)
{
if (inval.Length > 0 && inval.Length < 160)
{
try
{
Twitter t = new Twitter("twitteruser", "twitterpassword");
TwitterStatus newStatus = t.Status.Update(inval);
Console.WriteLine(inval + " : added");
}
catch
{
Console.WriteLine(inval + " : failed");
}
}
}

static void LoadDs()
{
SqlConnection conn = new SqlConnection(@"Server=.\SQLExpress;Database=dbname;User ID=dbuser;Password=dbpass");
conn.Open();
SqlDataAdapter daVBP = new SqlDataAdapter("SELECT EvEnd, EventTitle, pkey, EvStart, EvContent FROM SiteEvents WHERE ((EvStart >= '" + DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00') AND (EvEnd <= '" + DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59')) OR ((EvStart<= '" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND (EvEnd >= '" + DateTime.Now.ToString("yyyy-MM-dd") + "')) AND EvActive = 1", conn);
daVBP.FillSchema(dsEvents, SchemaType.Source, "SiteEvents");
daVBP.Fill(dsEvents, "SiteEvents");
if (daVBP != null)
{
daVBP.Dispose();
}
if (conn != null)
{
conn.Close();
}
}
}
}

Permalink