HomeSupportSave into a Local Database via ASP.NET + SQL Express [Tutorial]

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

[Jump to top]

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();
}

[Jump to top]

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();
}

[Jump to top]

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();
}
}
}
}

[Jump to top]