public bool UpdateSchool(SchoolModel school)
{
bool result = true;
StringBuilder statement = new StringBuilder();
statement.Append("UPDATE `schools` SET");
if (school.State != null)
{
statement.Append(" `state` = ").Append(Util.ConvertToDBString(school.State, null)).Append(",");
}
if (school.City != null)
{
statement.Append(" `city` = ").Append(Util.ConvertToDBString(school.City, null)).Append(",");
}
if (school.Name != null)
{
statement.Append(" `name` = ").Append(Util.ConvertToDBString(school.Name, null)).Append(",");
}
if (school.Url != null)
{
statement.Append(" `url` = ").Append(Util.ConvertToDBString(school.Url, null)).Append(",");
}
String currentTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
statement.Append(" `created_at` = '").Append(currentTime).Append("'");
statement.Append(" WHERE");
if (school.ID != null)
{
statement.Append(" `id` = ").Append(Util.ConvertToDBString(school.ID, null));
}
statement.Append(";");
MySqlConnection conn = new MySqlConnection(Util.MainConnectionString);
try
{
conn.Open();
MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = statement.ToString();
cmd.CommandType = CommandType.Text;
DateTime dtStart = DateTime.Now;
cmd.ExecuteNonQuery();
TimeSpan ts = DateTime.Now - dtStart;
}
catch (MySqlException ex)
{
if (school.Name != null)
{
log.Info(school.Name);
}
log.Error(ex.Message);
result = false;
}
catch (Exception ex)
{
log.Error(ex.Message);
result = false;
}
finally
{
conn.Close();
}
return result;
}
static void parseSchools(string url, string state)
{
HtmlWeb hw = new HtmlWeb();
HtmlDocument doc = hw.Load(url);
HtmlNodeCollection nodes = doc.DocumentNode.SelectNodes("//p/a");
char[] trimChars = { ',', ' ', '-' };
if (nodes == null || nodes.Count == 0)
{
nodes = doc.DocumentNode.SelectNodes("//a");
}
if (nodes != null && nodes.Count > 0)
{
foreach (HtmlNode node in nodes)
{
string parentHTML = node.ParentNode.InnerHtml;
if (parentHTML.Contains("a>"))
{
SchoolModel school = new SchoolModel();
school.State = Util.GetStateAbrv(state);
school.City = parentHTML.Substring(parentHTML.LastIndexOf("a>") + 2);
school.City = Regex.Replace(school.City, @"<[^>]*>", String.Empty);
school.City = school.City.Trim(trimChars);
school.Url = node.GetAttributeValue("href", "");
school.Name = node.InnerHtml.Trim(trimChars);
if (school.Name == null || school.Name.Length == 0)
{
continue;
}
if (school.City == null || school.City.Length == 0)
{
continue;
}
if (school.Url == null || school.Url.Length == 0 || school.Url.Contains("..") || school.Url.Contains("#"))
{
continue;
}
reader.GetSchool(school);
if (school.ID != null)
{
wrtier.UpdateSchool(school);
}
else
{
wrtier.InsertSchool(school);
}
}
}
}
else
{
log.Error("Something went terribly wrong and no schools were found for " + state);
}
}
public bool InsertSchool(SchoolModel school)
{
bool result = true;
StringBuilder statement = new StringBuilder();
statement.Append("INSERT INTO `schools`");
statement.Append(" (");
statement.Append(" `state`,");
statement.Append(" `city`,");
statement.Append(" `name`,");
statement.Append(" `url`,");
statement.Append(" `created_at`,");
statement.Append(" `updated_at`");
statement.Append(" )");
statement.Append(" VALUES ");
statement.Append(" (");
statement.Append(Util.ConvertToDBString(school.State, null)).Append(",");
statement.Append(Util.ConvertToDBString(school.City, null)).Append(",");
statement.Append(Util.ConvertToDBString(school.Name, null)).Append(",");
statement.Append(Util.ConvertToDBString(school.Url, null)).Append(",");
String currentTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
statement.Append(Util.ConvertToDBString(currentTime, null)).Append(",");
statement.Append(Util.ConvertToDBString(currentTime, null));
statement.Append(" );");
MySqlConnection conn = new MySqlConnection();
try
{
conn.ConnectionString = Util.MainConnectionString;
conn.Open();
MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = statement.ToString();
cmd.CommandType = CommandType.Text;
DateTime dtStart = DateTime.Now;
cmd.ExecuteNonQuery();
TimeSpan ts = DateTime.Now - dtStart;
school.ID = Convert.ToInt32(cmd.LastInsertedId);
conn.Close();
}
catch (MySqlException ex)
{
if (school.Name != null)
{
log.Info(school.Name);
}
log.Error(ex.Message);
result = false;
}
catch (Exception ex)
{
log.Error(ex.Message);
result = false;
}
finally
{
conn.Close();
}
return result;
}
public bool GetSchool(SchoolModel school)
{
bool result = false;
StringBuilder statement = new StringBuilder();
statement.Append("SELECT");
statement.Append(" s.`id`,");
statement.Append(" s.`state`,");
statement.Append(" s.`city`,");
statement.Append(" s.`name`,");
statement.Append(" s.`url`,");
statement.Append(" s.`created_at`,");
statement.Append(" s.`updated_at`");
statement.Append(" FROM");
statement.Append(" `schools` s");
statement.Append(" WHERE");
if (school.ID != null)
{
statement.Append(" s.`id` = ").Append(Util.ConvertToDBString(school.ID, null));
}
else if (school.State != null && school.State != null && school.State != null)
{
statement.Append(" s.`state` = ").Append(Util.ConvertToDBString(school.State, null));
statement.Append(" AND s.`city` = ").Append(Util.ConvertToDBString(school.City, null));
statement.Append(" AND s.`name` = ").Append(Util.ConvertToDBString(school.Name, null));
}
statement.Append(";");
MySqlConnection conn = new MySqlConnection();
try
{
conn.ConnectionString = Util.MainConnectionString;
conn.Open();
MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = statement.ToString();
cmd.CommandType = CommandType.Text;
DateTime dtStart = DateTime.Now;
MySqlDataReader rdr = cmd.ExecuteReader();
TimeSpan ts = DateTime.Now - dtStart;
while (rdr.Read())
{
school.ID = Util.SafeConvert.ToInt32(rdr["id"], null);
school.State = Util.SafeConvert.ToString(rdr["state"], null);
school.City = Util.SafeConvert.ToString(rdr["city"], null);
school.Name = Util.SafeConvert.ToString(rdr["name"], null);
school.Url = Util.SafeConvert.ToString(rdr["url"], null);
school.CreatedAt = GetMySQLDateTime(rdr, "created_at");
school.UpdatedAt = GetMySQLDateTime(rdr, "updated_at");
result = true;
}
conn.Close();
}
catch (MySqlException ex)
{
if (school.Name != null)
{
log.Info(school.Name);
}
log.Error(ex.Message);
result = false;
}
catch (Exception ex)
{
log.Error(ex.Message);
result = false;
}
finally
{
conn.Close();
}
return result;
}