Using C# .NET to retrieve business data from CitySearch

Setup: Get publisher key and web request url

The CitySearch api requires you to first register as a publisher and get a publisher key. Once you have that, you need to get your url. For this post I’m going to use the following:

https://api.citygridmedia.com/content/places/v2/detail?id=37030996&id_type=cs&client_ip=127.0.0.1&publisher={publisher_key}

Replace {publisher_key} with your key. We’ll have it return xml by default and parse that.

Retrieving the xml and parsing the contents

The container classes:

internal class CitySearchResult
{
	internal string Name { get; set; }
	internal string Address { get; set; }
	internal string City { get; set; }
	internal string State { get; set; }
	internal string Zip { get; set; }
	internal decimal Latitude { get; set; }
	internal decimal Longitude { get; set; }
	internal string DisplayPhone { get; set; }
	internal string DisplayUrl { get; set; }
	internal IEnumerable<string> Markets { get; set; }
	internal IEnumerable<string> Categories { get; set; }
	internal IEnumerable<string> BusinessHours { get; set; }
	internal IEnumerable<CitySearchReview> Reviews { get; set; }
	internal decimal OverallReviewRating { get; set; }
	internal int TotalUserReviews { get; set; }
	internal DateTime LastUpdate { get; set; }
	internal string BusinessOperationStatus { get; set; }
	internal bool Claimed { get; set; }
	internal IEnumerable<string> Geographies { get; set; }
}

internal class CitySearchReview 
{
	internal string ReviewId { get; set; }
	internal string ReviewAuthor { get; set; }
	internal string ReviewText { get; set; }
	internal DateTime ReviewDate { get; set; }
	internal decimal ReviewRating { get; set; }
	internal string ReviewType { get; set; }
}

The extension method:

internal static T TryParseValue<T>(this string s) where T : struct
{
	var method = typeof(T).GetMethod(
		"TryParse",
		new []{typeof(string), typeof(T).MakeByRefType()}
		);
	T result = default(T);
	var parameters = new object[] { s, result };
	method.Invoke(null, parameters);
	return (T)parameters[1];
}

The code:

internal CitySearchResult GetCitySearchReviews(string listingId)
{
	string publisherKey = ""; // Your publisher key
	string url = string.Format(@"https://api.citygridmedia.com/content/places/v2/detail?id={0}&id_type=cs&client_ip=127.0.0.1&publisher={1}", listingId, publisherKey);
	XDocument doc = XDocument.Load(url);
	var locationResults = doc.Root.Element("location");
	var address = locationResults.Element("address");
	var contactInfo = locationResults.Element("contact_info");
	var reviewInfo = locationResults.Element("review_info");
	var categories = locationResults.Elements("categories");
	var reviewResults = locationResults.Element("review_info");
	var reviews = reviewResults.Element("reviews").Elements().Select(e => new CitySearchReview
	{
		ReviewId = e.Element("review_id").Value,
		ReviewAuthor = e.Element("review_author").Value,
		ReviewText = e.Element("review_text").Value,
		ReviewDate = e.Element("review_date").Value.TryParseValue<DateTime>(),
		ReviewRating = e.Element("review_rating").Value.TryParseValue<Decimal>(),
		ReviewType = e.Element("review_type").Value
	});

	return new CitySearchResult
	{
		Name = locationResults.Element("name").Value,
		Address = address.Element("street").Value,
		City = address.Element("city").Value,
		State = address.Element("state").Value,
		Zip = address.Element("postal_code").Value,
		Latitude = address.Element("latitude").Value.TryParseValue<Decimal>(),
		Longitude = address.Element("longitude").Value.TryParseValue<Decimal>(),
		DisplayPhone = contactInfo.Element("display_phone").Value,
		DisplayUrl = contactInfo.Element("display_url").Value,
		Markets = locationResults.Element("markets").Elements().Select(e => e.Value),
		Categories = categories.Elements().Attributes("name").Select(a => a.Value),
		BusinessHours = locationResults.Element("business_hours").Value.Split(new []{"\n"}, StringSplitOptions.RemoveEmptyEntries),
		Reviews = reviews,
		OverallReviewRating = reviewResults.Element("overall_review_rating").Value.TryParseValue<Decimal>(),
		TotalUserReviews = reviewResults.Element("total_user_reviews").Value.TryParseValue<Int32>(),
		LastUpdate = locationResults.Element("last_update_time").Value.TryParseValue<DateTime>(),
		BusinessOperationStatus = locationResults.Element("business_operation_status").Value,
		Claimed = locationResults.Element("claimed").Value.TryParseValue<bool>(),
		Geographies = locationResults.Element("geographies").Elements().Select(e => e.Value)
	};
}

So here we have two container classes: One for the overall data structure and another to hold our actual review data. Using them we retrieve our data with Linq to Xml and return the result.

Wrapping Up

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!

Using C# .NET to retrieve business data from Yellow Pages

Setup: Getting the web request url

First you need to register for yellow pages api account to get an api key, you can do that here. Once you have that, it’s time to get the url we’ll need in order to make a web request.

For this example I’m going to use the following:

http://pubapi.yp.com/search-api/search/devapi/details?listingid=1000662584987&key={apikey}

Replace {apikey} with your own. I’m searching by listing id; this is just one way of searching, and how I’m going to do it in this post.

Retrieving the xml and parsing the contents

The container class:

internal class YellowPagesResult
{
	internal IEnumerable<string> AdditionalTexts { get; set; }
	internal decimal AverageRating { get; set; }
	internal IEnumerable<string> Brands { get; set; }
	internal string BusinessName { get; set; }
	internal IEnumerable<string> Categories { get; set; }
	internal string City { get; set; }
	internal IEnumerable<string> SocialNetworks { get; set; }
	internal string InBusinessSince { get; set; }
	internal decimal Latitude { get; set; }
	internal string LocationDescription { get; set; }
	internal decimal Longitude { get; set; }
	internal IEnumerable<string> Hours { get; set; }
	internal IEnumerable<string> PaymentMethods { get; set; }
	internal string Phone { get; set; }
	internal string PrimaryCategory { get; set; }
	internal int RatingCount { get; set; }
	internal string Slogan { get; set; }
	internal string State { get; set; }
	internal string Street { get; set; }
	internal string Zip { get; set; }
}

The extension method:

internal static T TryParseValue<T>(this string s) where T : struct
{
	var method = typeof(T).GetMethod(
		"TryParse",
		new []{typeof(string), typeof(T).MakeByRefType()}
		);
	T result = default(T);
	var parameters = new object[] { s, result };
	method.Invoke(null, parameters);
	return (T)parameters[1];
}

The code:

internal YellowPagesResult GetYellowPagesReviews(string listingId)
{
	const string apiKey = ""; // your api key
	var listingUrl = string.Format(@"http://pubapi.yp.com/search-api/search/devapi/details?listingid={0}&key={1}", listingId, apiKey);

	XDocument listingDoc;
	using (var client = new WebClient()) 
	{
		client.Headers["Accept"] = "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8";
		client.Headers["User-Agent"] = "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.124 Safari/537.36";

		listingDoc = XDocument.Load(client.OpenRead(listingUrl));
	}
			
	var listingDetails = listingDoc.Root.Element("listingsDetails").Element("listingDetail");
	var additionalTexts = listingDetails.Element("additionalTexts").Elements();
	var brands = listingDetails.Element("brands").Elements();
	var categories = listingDetails.Element("categories").Elements();
	var socialNetworks = listingDetails.Element("features").Elements("socialNetworks").Elements();

	return new YellowPagesResult
	{
		AdditionalTexts = additionalTexts.Select(e => e.Value),
		AverageRating = listingDetails.Element("averageRating").Value.TryParseValue<Decimal>(),
		Brands = brands.Select(e => e.Value),
		BusinessName = listingDetails.Element("businessName").Value,
		Categories = categories.Select(e => e.Value),
		City = listingDetails.Element("city").Value,
		SocialNetworks = socialNetworks.Select(e => e.Value),
		InBusinessSince = listingDetails.Element("inBusinessSince").Value,
		Latitude = listingDetails.Element("latitude").Value.TryParseValue<Decimal>(),
		LocationDescription = listingDetails.Element("locationDescription").Value,
		Longitude = listingDetails.Element("longitude").Value.TryParseValue<Decimal>(),
		Hours = listingDetails.Element("openHours").Value.Split(new []{","}, StringSplitOptions.RemoveEmptyEntries),
		PaymentMethods = listingDetails.Element("paymentMethods").Value.Split(new []{","}, StringSplitOptions.RemoveEmptyEntries),
		Phone =listingDetails.Element("phone").Value,
		PrimaryCategory = listingDetails.Element("primaryCategory").Value,
		RatingCount = listingDetails.Element("ratingCount").Value.TryParseValue<Int32>(),
		Slogan = listingDetails.Element("slogan").Value,
		State = listingDetails.Element("state").Value,
		Street = listingDetails.Element("street").Value,
		Zip = listingDetails.Element("zip").Value
	};
}

So some things to note. Instead of loading the url directly into XDocument, we first have to specify the headers in our request. Using Fiddler, we’re able to retrieve this information by pasting our formatted url into a browser with Fiddler open. And finally we just retrieve the data.

Wrapping Up

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!

Salesforce AggregateResult extension method helper

So I was working with the salesforce api and needed to handle a collection of AggregateResult objects. The technique found here for C# .NET didn’t seem very manageable, so I wrote an extension method to help out.

public static class AggregateResultExtensions
{
    public static T Get<T>(this AggregateResult aggResult, string name)
    {
        var result = aggResult.Any.FirstOrDefault(e => e.LocalName == name);
        return result == null ? default(T) : result.InnerText.Convert<T>();
    }

    public static T Convert<T>(this string str) 
    {
        TypeConverter converter = TypeDescriptor.GetConverter(typeof(T));
        return (T)converter.ConvertFromString(str);
    }
}

Usage (Modified example from above link)

String soqlStr = "SELECT Name, " +
		 "MAX(Amount) MaxAmount, " +
		 "MIN(Amount) MinAmount" +
		 "FROM Opportunity " +
		 "GROUP BY Name";

qr = binding.query(soqlStr);
if (qr.size > 0)
{
	for (int i = 0; i < qr.records.Length; i++)
	{
		sforce.AggregateResult ar = (AggregateResult)qr.records[i];
		string name = ar.Get<string>("Name");
		decimal maxAmount = ar.Get<decimal>("MaxAmount");
		decimal minAmount = ar.Get<decimal>("MinAmount");
	}
}

Wrapping Up

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!

Using C# .NET to retrieve business data from Yahoo Local

Setup: Create YQL query

Navigate to the yql console and click on the right hand side {local -> local.search}. You can do your own query, but for this post I’m going to use the following one:

select * from local.search where listing_id = '25857783'

What this returns in the console is either a xml or json result, depending on what you choose. I’m going to choose xml.

At the bottom you’ll find your rest query. We’ll use this to retrieve the data using C# .NET.

Retrieving the xml and parsing the contents

Here’s the code to do so:

First the container class to store our data:

internal class YahooLocalResult
{
	internal string Title { get; set; }
	internal string Address { get; set; }
	internal string City { get; set; }
	internal string State { get; set; }
	internal string Phone { get; set; }
	internal decimal Latitude { get; set; }
	internal decimal Longitude { get; set; }
	internal decimal AverageRating { get; set; }
	internal int TotalRatings { get; set; }
	internal int TotalReviews { get; set; }
	internal DateTime LastReviewDate { get; set; }
	internal string LastReviewIntro { get; set; }
	internal decimal Distance { get; set; }
	internal string Url { get; set; }
	internal string ClickUrl { get; set; }
	internal string MapUrl { get; set; }
	internal string BusinessUrl { get; set; }
	internal string BusinessClickUrl { get; set; }
	internal IEnumerable<string> Categories { get; set; }
}

Next an extension method to help retrieve values:

internal static T TryParseValue<T>(this string s) where T : struct
{
	var method = typeof(T).GetMethod(
		"TryParse",
		new []{typeof(string), typeof(T).MakeByRefType()}
		);
	T result = default(T);
	var parameters = new object[] { s, result };
	method.Invoke(null, parameters);
	return (T)parameters[1];
}

Also a method to convert an unix time value to a DateTime:

private DateTime FromUnixTime(long unixTime)
{
	var epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
	return epoch.AddSeconds(unixTime);
}

And finally the code itself:

internal YahooLocalResult GetYahooLocalReviews(string listingId)
{
	string url = string.Format(@"https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20local.search%20where%20listing_id%20%3D%20{0}", listingId);

	XNamespace ns = "urn:yahoo:lcl";
	XDocument doc = XDocument.Load(url);
	var businessResults = doc.Root.Element("results").Element(ns + "Result");
			
	var ratingResults = businessResults.Element(ns + "Rating");
	var categoryResults = businessResults.Element(ns + "Categories");

	return new YahooLocalResult
	{
		Title = businessResults.Element(ns + "Title").Value,
		Address = businessResults.Element(ns + "Address").Value,
		City = businessResults.Element(ns + "City").Value,
		State = businessResults.Element(ns + "State").Value,
		Phone = businessResults.Element(ns + "Phone").Value,
		Latitude = businessResults.Element(ns + "Latitude").Value.TryParseValue<Decimal>(),
		Longitude = businessResults.Element(ns + "Longitude").Value.TryParseValue<Decimal>(),
		AverageRating = ratingResults.Element(ns + "AverageRating").Value.TryParseValue<Decimal>(),
		TotalRatings = ratingResults.Element(ns + "TotalRatings").Value.TryParseValue<Int32>(),
		TotalReviews = ratingResults.Element(ns + "TotalReviews").Value.TryParseValue<Int32>(),
		LastReviewDate = FromUnixTime(ratingResults.Element(ns + "LastReviewDate").Value.TryParseValue<long>()),
		LastReviewIntro = ratingResults.Element(ns + "LastReviewIntro").Value,
		Distance = businessResults.Element(ns + "Distance").Value.TryParseValue<Decimal>(),
		Url = businessResults.Element(ns + "Url").Value,
		ClickUrl = businessResults.Element(ns + "ClickUrl").Value,
		MapUrl = businessResults.Element(ns + "MapUrl").Value,
		BusinessUrl = businessResults.Element(ns + "BusinessUrl").Value,
		BusinessClickUrl = businessResults.Element(ns + "BusinessClickUrl").Value,
		Categories = categoryResults.Elements().Select(e => e.Value)
	};
}

Just a few notes here. I’m using Linq to Xml to keep this as short and simple as possible while still maintaining readability. There are other ways of doing this, I just prefer this method. Essentially we make a web request using our formatted url with the XDocument class, and using Linq to Xml we retrieve our values, store them in a container class, and return that data. An important thing to remember here (as it caused me much frustration) is to remember to include the namespace. Without this these calls will return an error.

Wrapping Up

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!

Bulk insert xml into MS Sql Server table

So I’m going to use the Northwind sample database to show how to bulk insert a xml file into the Northwind table Employees.

Setup

The Employees table has these columns:

The xml I’m inserting looks like this:

I’ve generated this xml by connecting to the Northwind database, selecting all from Employees, and using DataTables WriteXml method.

Performing the operation

Here’s the sql in order to do it:

DECLARE @xml TABLE (x XML)

INSERT @xml
SELECT x
FROM OPENROWSET(BULK 'EmployeesData.xml', SINGLE_BLOB) AS T(x)

INSERT INTO Employees                                                                                                                                       
SELECT
    result.LastName,
	result.FirstName,
	result.Title,
	result.TitleOfCourtesy,
	result.BirthDate,
	result.HireDate,
	result.[Address],
	result.City,
	result.Region,
	result.PostalCode,
	result.Country,
	result.HomePhone,
	result.Extension,
	result.Photo,
	result.Notes,
	result.ReportsTo,
	result.PhotoPath 
FROM @xml
CROSS APPLY (
   SELECT
       LastName = z.value('LastName[1]', 'nvarchar(20)'),
	   FirstName = z.value('FirstName[1]', 'nvarchar(10)'),
	   Title = z.value('Title[1]', 'nvarchar(30)'),
	   TitleOfCourtesy = z.value('TitleOfCourtesy[1]', 'nvarchar(25)'),
	   BirthDate = z.value('BirthDate[1]', 'datetime'),
	   HireDate = z.value('HireDate[1]', 'datetime'),
	   [Address] = z.value('Address[1]', 'nvarchar(60)'),
	   City = z.value('City[1]', 'nvarchar(15)'),
	   Region = z.value('Region[1]', 'nvarchar(15)'),
	   PostalCode = z.value('PostalCode[1]', 'nvarchar(10)'),
	   Country = z.value('Country[1]', 'nvarchar(15)'),
	   HomePhone = z.value('HomePhone[1]', 'nvarchar(24)'),
	   Extension = z.value('Extension[1]', 'nvarchar(4)'),
	   Photo = z.value('Photo[1]', 'varbinary(max)'),
	   Notes = z.value('Notes[1]', 'nvarchar(max)'),
	   ReportsTo = z.value('ReportsTo[1]', 'int'),
	   PhotoPath = z.value('PhotoPath[1]', 'nvarchar(255)') 
   FROM x.nodes('/DocumentElement/Employees') T(z)
) result

So what we’re basically doing here is using OPENROWSET with the BULK option to read in the xml file. This gives us the data in a single row, and if you removed everything below that call and did a select * from @xml, you’d see the single row with all of the xml in it. You want to make sure the path you pass into OPENROWSET is the correct path to the xml.

Next is the actual insert statement, where by using a cross apply to extract the xml values, we’re able to then perform our insert.

Wrapping Up

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!

Access PostgreSQL through terminal on Mac OS X without password

Postgres! I ran into an issue not too long ago where I had to retrieve data from a postgres database and didn’t have a user to login as. In this post I’m going to go over the necessary steps in order to login to the database without a password and create a new login to use. I’m going to assume you already have postgres installed; I’ve installed it via Homebrew on OS X Yosemite 10.10.3, PostgreSQL 9.4.4.

Locate the pg_hba.conf file

In my installation using Homebrew it’s located in:

usr/local/var/postgres

To do a search for it type:

ps aux | grep postgres

Essentially this command lists processes for all users, narrowing the search for those that include postgres, and shows the directory for that process.

Change all authentication methods to trust

Once you find the file, vim into it using the following command:

vim pg_hba.conf

Navigate to the bottom, and change all authentication methods from whatever they are (usually md5) to be trust. By doing this we’re allowing anyone to login to postgres without a password. We’ll change this back after we’re done.

Below are some helpful vim commands for editing the file.

i # Begin to INSERT into the file
Esc # Exit mode
:w # Save the file
:q # Quit the session
:wq # Save and quit

Once done, save and exit.

Reload the server using pg_ctl

Find the directory that holds the pg_ctl script. Using my Homebrew installation of postgres it’s located at:

usr/local/bin

And also find the data directory, using my installation:

usr/local/var/postgres

Then execute the following command:

usr/local/bin/pg_ctl reload -D usr/local/var/postgres

What this does is it tells postgres to read our updates to the pg_hba.conf file without restarting the server.

Login to psql

Finally, you want to login to psql using the following command:

psql postgres

To login as another user, simply enter their username, or just psql.

From here you can enter whatever sql command you’d like.

Create a new user with privileges:

# Create new role
create role new-user-name with password 'new-password' superuser createrole createdb replication login;
# List all users
\du
#Change role password
ALTER ROLE new-user-name WITH PASSWORD 'another-password';

Don’t forget when your done to change trust back to whatever it was before (usually md5), and use pg_ctl reload command to re-signal postgres.

That’s it!

Wrapping Up

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!

Access MySQL through terminal on Mac OS X without password

So sometimes when working with a MySQL database you may have admin rights on the system but not a password to login with. Fortunately, there is a way to access MySQL using terminal on Mac OS X without a password. So in this post I’m going to go through the steps needed in order to achieve this!

  1. Locate your mysql directory, which is located in

        usr/local/mysql
        

    If you’ve installed mysql from http://www.mysql.com/ using .dmg

  2. Stop your mysql server

        sudo usr/local/mysql/support-files/mysql.server stop
        
  3. Start mysql server using mysqld_safe

        sudo usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
        

    We use the & switch here to say we want to continue using this terminal window while it starts the server. If we didn’t specify this, we would have to open a new terminal window. The –skip-grant-tables switch grants us all privileges and allows us to access mysql without a password.

  4. Run mysql using root account
        usr/local/mysql/bin/mysql -u root
        

And that’s it! From here, you can execute whichever commands you need.
First though make sure you use:

FLUSH PRIVILEGES;

to enable changes to accounts.

If you want to change the password for a user:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root123');

Create a user with all privileges (or specify which ones you need)

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

One last thing. When you are you finished, make sure you stop the server and start it again normally using

sudo usr/local/mysql/support-files/mysql.server stop
sudo usr/local/mysql/support-files/mysql.server start

Wrapping Up

This same technique can be applied on windows machines too, first shut down the server, locate the bin directory (using the same installation for windows it would be in c:/Program Files/MySQL/MySQL Server 5.6/bin), execute mysqld.exe with –skip-grant-tables and login with mysql.exe.

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!

Restoring MS SQL Server database backup (.bak) using C# .NET

In this post I’m going to provide code and and a walkthrough on how to restore a MS SQL Server database backup file using C# .NET.

So let’s get started!

Gathering necessary data before performing the restore

So when restoring a sql server database we need specify where to move its .mdf and .ldf files (.mdf is the data file and .ldf is the log file for the backup), because if you’ve taken the backup from a different machine they most likely won’t be in the same location from where it’s from. In any case it doesn’t hurt to specify it in the actual restore command (we’ll get to that later). So in order to do this, we need to find out what those names are. Fortunately there’s a sql query we can execute to find out exactly what the names of the files are!

internal class DatabaseFileList
{
	internal string DataName { get; set; }
	internal string LogName { get; set; }
}

private DatabaseFileList GetDatabaseFileList(string localDatabasePath) 
{
    string connectionString = "Data Source=.;Initial Catalog=master;Integrated Security=True"
				providerName="System.Data.SqlClient";
	using (SqlConnection conn = new SqlConnection(connectionString)) 
	{
		string sqlQuery = @"RESTORE FILELISTONLY FROM DISK = @localDatabasePath";
		conn.Open();
		using (SqlCommand cmd = new SqlCommand(sqlQuery, conn)) 
		{
			cmd.CommandType = CommandType.Text;
			cmd.Parameters.AddWithValue("@localDatabasePath", localDatabasePath);
			using (SqlDataReader reader = cmd.ExecuteReader()) 
			{
				var fileList = new DatabaseFileList();
				while (reader.Read()) 
				{
					string type = reader["Type"].ToString();
					switch (type) 
					{
						case "D":
							fileList.DataName = reader["LogicalName"].ToString();
							break;
						case "L":
							fileList.LogName = reader["LogicalName"].ToString();
							break;
					}
				}
				return fileList;
			}
		}
	}
}

Okay so we have a couple things going on here.

  1. First we’re declaring a container class (DatabaseFileList) to hold our values. There are other ways of returning these values, I just prefer this method as I feel it’s more readable and maintainable.
  2. Next is a method (not wrapped in a class to keep things short) that takes in a string that is a path to the .bak file on our machine.
  3. The connection string is what we use to connect to our database. For the data source I’m using . as an alias for localhost, but in your case this value may be different. Here I’m writing it as a local variable in the method, but I would suggest putting it in the config file and accessing it there.
  4. Using the built-in .NET classes for accessing a database, we perform the operations we need.
  5. When we use the RESTORE command here, we’re specifying FILELISTONLY to say only give us a list of the files associated with the database backup.
  6. Finally with a reader open we look at the type column for each row. If the type is “D”, then we know it’s the .mdf file, or if it’s type “L”, then it’s the .ldf file, as specified here
  7. Then we return our data!

So now that we have the names of our files, it’s time to perform the restoration!

Performing the restoration

private void RestoreDatabase(string localDatabasePath, string fileListDataName, string fileListLogName)
{
    string localDownloadFilePath = "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\";
	Console.WriteLine(string.Format("Restoring database {0}...", localDatabasePath));
	string fileListDataPath = Directory.GetParent(localDownloadFilePath).Parent.FullName + @"\DATA\" + fileListDataName + ".mdf";
	string fileListLogPath = Directory.GetParent(localDownloadFilePath).Parent.FullName + @"\DATA\" + fileListLogName + ".ldf";

	string sql = @"RESTORE DATABASE @dbName FROM DISK = @path WITH RECOVERY,
        MOVE @fileListDataName TO @fileListDataPath,
        MOVE @fileListLogName TO @fileListLogPath";

	using (SqlConnection connection = new SqlConnection(ConnectionString))
	{
		connection.Open();
		using (SqlCommand command = new SqlCommand(sql, connection))
		{
			command.CommandType = CommandType.Text;
			command.CommandTimeout = 7200;
			command.Parameters.AddWithValue("@dbName", fileListDataName);
			command.Parameters.AddWithValue("@path", localDatabasePath);
			command.Parameters.AddWithValue("@fileListDataName", fileListDataName);
			command.Parameters.AddWithValue("@fileListDataPath", fileListDataPath);
			command.Parameters.AddWithValue("@fileListLogName", fileListLogName);
			command.Parameters.AddWithValue("@fileListLogPath", fileListLogPath);

			command.ExecuteNonQuery();
		}
	}
	Console.WriteLine(string.Format("Database restoration complete for {0}.", localDatabasePath));
}

So what’s happening here?

  1. We have a single method here that takes in a path to the backup on our local machine, and the two values we retrieved from the previous code snippet.
  2. Using these values, with an additional new value localDownloadFilePath, we perform our restore operation. This path should be the folder where our backup actually is. In this case, I’ve put it directly in the Backup folder of my copy of Sql Server Express.
  3. Now the directory where we want to put our .mdf / .ldf files is the DATA folder, one level up from where our backup is on the same level as the Backup folder itself. I’m using the Directory class here to achieve this.
  4. The RESTORE statement here has a couple things to it.

After that we complete the code with the necessary logic and begin the restoration. That’s it!

Wrapping Up

So hopefully this has been informative, if you’ve enjoyed reading please leave a comment below or see if I’ve made any mistakes or a better way of doing this I would love to hear it, thanks!