Configuration
This is where the application is configured you will need to use your own unique feed and database settings:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SampleClientLibrary;
namespace SampleDatabaseCreatorAndUpdater
{
class Program
{
#region Feed Configuration
private const string APIKey = ""; //insert API key into variable
private const string baseUrl = ""; //insert BaseURL into variable
#endregion
#region Database Configuration
private const string dbHost = "localhost\\SQLEXPRESS";
private const string dbName = "DBSimple";
private const string dbUser = "dnApp";
private const string dbPass = "ppAnd";
private const string dbTableName = "NewsArticles";
private const string dbTableIdentifier = "[" + dbName + "].[dbo].[" + dbTableName + "]";
private static string dbConnectionString = "Data Source=" + dbHost +
";User Id=" + dbUser + ";Password=" + dbPass + ";";
private static SqlConnection dbConnection = new SqlConnection(dbConnectionString);
#endregion
Main Method
This is the main thread from which the application is executed from here we check if the database name already exists on the SQL Server and if not to run the create database method. Following this we use the sample API to return a collection of news items for the feed and pass them to the update database method.
static void Main(string[] args)
{
dbConnection.Open();
//Check if database already exists
string query = "SELECT * from master.dbo.sysdatabases WHERE name='" + dbName + "'";
bool databaseExists = (new SqlCommand { CommandText = query, Connection = dbConnection }.ExecuteScalar() == null) ? false : true;
//only create database if it doesn't exist
if (!databaseExists)
{
createDatabase();
}
ApiContext ac = new ApiContext(APIKey, baseUrl); //create a new feed object using the Api_Key
IEnumerable<newsItem> newsList = ac.News; //return an array of the latest news items from your feed in HTML format
updateDatabase(newsList);//Update news items to database
dbConnection.Close(); //Close Database connection
Console.WriteLine("Operation Completed \n Press any key to continue...");
Console.ReadKey();
}
Database Initialization
This method is executed if the specified database name does not exist and creates the required news articles table as well.
///
<summary>
/// Creates the database and the required table
/// </summary>
private static void createDatabase()
{
//Create Database
string query = "CREATE DATABASE " + dbName;
SqlCommand dbCommand = new SqlCommand(query, dbConnection);
dbCommand.ExecuteNonQuery();
//Create a news articles table in the database
dbCommand.CommandText = "CREATE TABLE " + dbTableIdentifier + "(" +
"[id] int NOT NULL," +
"[headline] varchar(255) NOT NULL, " +
"[text] text NOT NULL, " +
"[publishedDate] datetime NOT NULL, " +
"[photoURL] text NULL, " +
"PRIMARY KEY ([id]));";
dbCommand.Parameters.Add(new SqlParameter("@dbTableName", dbTableName));
dbCommand.ExecuteNonQuery();
}
Database Update
The updateDatabse method recieves a collection of newsList objects and persists them to the database only if the respectice id does not already exist in the database. Parameters are used to ensure all unsafe characters are escaped for the query string.
///
<summary>
/// Database update method, takes a list of newsItems and inserts each newsItem into the
/// database on the condition it doesn't exist already
/// </summary>
private static void updateDatabase(IEnumerable<newsItem> newsList)
{
foreach (newsItem n in newsList)
{
SqlCommand dbCommand = new SqlCommand { Connection = dbConnection };
dbCommand.Parameters.Add(new SqlParameter("@id", n.id));
dbCommand.Parameters.Add(new SqlParameter("@headline", n.headline));
dbCommand.Parameters.Add(new SqlParameter("@text", n.text));
dbCommand.Parameters.Add(new SqlParameter("@publishDate", n.publishDate));
//process photo select default photo and return largeURL for it
object photoURL = DBNull.Value;
photo newsPhotos = n.photos.FirstOrDefault();
if (newsPhotos != null)
{
photo.Instance largePhoto = newsPhotos.Instances.Where(x => x.type == enumeratedTypes.enumPhotoInstanceType.Large).FirstOrDefault();
if (largePhoto != null) photoURL = largePhoto.url;
}
dbCommand.Parameters.Add(new SqlParameter("@photoURL", photoURL));
//insert article into database, but ignore it if it already exists
string query = "IF NOT EXISTS (SELECT * FROM " + dbTableIdentifier + " WHERE id = @id)" +
"INSERT INTO " + dbTableIdentifier +
" Values(@id, @headline, @text, @publishDate, @photoURL)";
dbCommand.CommandText = query;
dbCommand.ExecuteNonQuery();
}
}
}
}