Last active
August 29, 2015 14:01
-
-
Save yasinkuyu/8c2a71d0eb4aae2641e1 to your computer and use it in GitHub Desktop.
Foursquare categories recursive MySQL Database insert with C#
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// @yasinkuyu | |
// 15/05/2014 | |
using System; | |
using System.IO; | |
using MySql.Data.MySqlClient; | |
using Newtonsoft.Json.Linq; | |
namespace FoursquareCategories | |
{ | |
class Program | |
{ | |
private const string constr = "Server=localhost;Uid=root;Pwd=root;Database=dbname;"; | |
// First dump foursquare-categories.json ----> https://gist.github.com/janosgyerik/2906942 | |
// CREATE TABLE `category` ( | |
// `CatId` int(11) NOT NULL AUTO_INCREMENT, | |
// `ParentId` int(11) DEFAULT NULL, | |
// `Name` char(255) DEFAULT NULL, | |
// `fid` varchar(255) DEFAULT NULL, | |
// PRIMARY KEY (`CatId`) | |
//) ENGINE=MyISAM AUTO_INCREMENT=8995 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; | |
public static void Recursive(JToken item, long lastid) | |
{ | |
var db = new MySqlConnection(constr); | |
db.Open(); | |
var cmd = db.CreateCommand(); | |
foreach (var subitem in item["categories"]) | |
{ | |
cmd.CommandText = string.Format("INSERT INTO category2(`Name`, `ParentId`, `fid`) VALUES('{0}', '{1}', '{2}')", subitem["name"].ToString().Replace("'", "`"), lastid, subitem["id"]); | |
cmd.ExecuteNonQuery(); | |
Console.WriteLine("\t --name: {0}", subitem["name"]); | |
if (subitem["categories"] != null) | |
Recursive(subitem, cmd.LastInsertedId); | |
} | |
db.Close(); | |
} | |
static void Main(string[] args) | |
{ | |
var db = new MySqlConnection(constr); | |
db.Open(); | |
var cmd = db.CreateCommand(); | |
var file = Path.GetFullPath(@"foursquare-categories.json"); | |
var json = File.ReadAllText(file); | |
var data = JObject.Parse(json); | |
foreach (var item in data["categories"]) | |
{ | |
cmd.CommandText = string.Format("INSERT INTO category2(`Name`, `ParentId`, `fid`) VALUES('{0}', '{1}', '{2}')", item["name"], 0, item["id"]); | |
cmd.ExecuteNonQuery(); | |
Console.WriteLine("name: {0}", item["name"]); | |
Recursive(item, cmd.LastInsertedId); | |
} | |
db.Close(); | |
Console.ReadKey(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment