public void SetModelYears(ModelTypeObj obj) { try { string query = "CREATE_MODEL_YEARS_BY_MODEL_ID"; //open connection if (_mySqlExporter.connection.State == ConnectionState.Open || _mySqlExporter.OpenConnection() == true) { //create command and assign the query and connection from the constructor MySqlCommand cmd = new MySqlCommand(query, _mySqlExporter.connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new MySqlParameter("m_id", obj.ModelTypeID)); //Execute command cmd.ExecuteNonQuery(); //close connection _mySqlExporter.CloseConnection(); } } catch (Exception ex) { if (Core != null && Core.Log != null) { Core.Log.Error(string.Format("SQLCarModelType::SetModelYears : {0}", ex.Message)); } else { throw new Exception("SQLCarModelType::SetModelYears", ex); } } }
public void SelectModelTypesFromDB() { string query = string.Format("SELECT * FROM {0}", MySQLExporter.MODELTYPE_TABLE); /* * //Create a list to store the result * List<string>[] list = new List<string>[3]; * list[0] = new List<string>(); * list[1] = new List<string>(); * list[2] = new List<string>(); */ modelTypesList.Clear(); modelTypesList.Capacity = 1; //Open connection if (this.OpenConnection() == true) { //Create Command MySqlCommand cmd = new MySqlCommand(query, connection); //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { ModelTypeObj obj = new ModelTypeObj(); obj.MakerID = Convert.ToInt32(dataReader["maker_id"] + ""); obj.ModelID = Convert.ToInt32(dataReader["model_id"] + ""); obj.ModelTypeID = Convert.ToInt32(dataReader["modeltype_id"] + ""); obj.ModelTypeName = dataReader["name"] + ""; obj.ModelTypeDetailsUrl = dataReader["type_url"] + ""; modelTypesList.Capacity++; modelTypesList.Add(obj); /* * list[0].Add(dataReader["id"] + ""); * list[1].Add(dataReader["name"] + ""); * list[2].Add(dataReader["age"] + ""); */ } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); } }
public void Update_CarModelType(ModelTypeObj obj, long makerId, long modelId, long id) { try { string query = string.Format("UPDATE {0} SET " + "maker_id=@maker_id, " + "model_id=@model_id, " + "modeltype_id=@modeltype_id," + "name=@name," + "cubic=@cubic," + "fuel=@fuel," + "power=@power," + "tank=@tank," + "from_year=@from_year," + "to_year=@to_year," + "chassis=@chassis," + "doors=@doors," + "type_url=@type_url" + " WHERE id={1}", MySQLExporter.MODELTYPE_TABLE, id); //Open connection if (_mySqlExporter.connection.State == ConnectionState.Open || _mySqlExporter.OpenConnection() == true) { //create mysql command MySqlCommand cmd = new MySqlCommand { CommandText = query, Connection = _mySqlExporter.connection }; //Assign the query using CommandText //Assign the connection using Connection SetSQLParameters(obj, cmd, makerId, modelId); //Execute query cmd.ExecuteNonQuery(); //close connection _mySqlExporter.CloseConnection(); } } catch (Exception ex) { if (Core != null && Core.Log != null) { Core.Log.Error(string.Format("SQLCarModelType::Update_CarModel : {0}", ex.Message)); } else { throw new Exception("SQLCarModelType::Update_CarModel", ex); } } }
private static void SetSQLParameters(ModelTypeObj obj, MySqlCommand cmd, long makerId, long modelId) { cmd.Parameters.AddWithValue("@maker_id", makerId); cmd.Parameters.AddWithValue("@model_id", modelId); cmd.Parameters.AddWithValue("@modeltype_id", obj.ModelTypeID); cmd.Parameters.AddWithValue("@name", obj.ModelTypeName); cmd.Parameters.AddWithValue("@cubic", obj.ModelTypeCubic); cmd.Parameters.AddWithValue("@fuel", obj.ModelTypeFuel); cmd.Parameters.AddWithValue("@power", obj.ModelTypePower); cmd.Parameters.AddWithValue("@tank", obj.ModelTypeTank); cmd.Parameters.AddWithValue("@from_year", obj.ModelTypeFromYear); cmd.Parameters.AddWithValue("@to_year", obj.ModelTypeToYear); cmd.Parameters.AddWithValue("@chassis", obj.ModelTypeChassis); cmd.Parameters.AddWithValue("@doors", obj.ModelTypeDoors); cmd.Parameters.AddWithValue("@type_url", obj.ModelTypeDetailsUrl); }
private void GetModelTypes(ModelObj modelObj) { string modelTypesUrl = string.Format("{0}{1}", this.baseUrl, modelObj.ModelUrlPath); string modelsContent = GetContent(modelTypesUrl); var htmlDoc = new HtmlDocument(); htmlDoc.LoadHtml(modelsContent); HtmlNodeCollection types_div = null; if (htmlDoc.DocumentNode != null && htmlDoc.ParseErrors != null && !htmlDoc.ParseErrors.Any()) { types_div = htmlDoc.DocumentNode.SelectNodes("//*[@id=\"modelis\"]/table"); } if (types_div != null) { foreach (var node in types_div.First().ChildNodes) { ModelTypeObj typeObj = new ModelTypeObj(); typeObj.ModelTypeID = modelTypesList.Count + 1; typeObj.ModelID = modelObj.ModelID; typeObj.ModelTypeName = node.ChildNodes[0].InnerText; typeObj.ModelTypeCubic = node.ChildNodes[1].InnerText .Substring(0, node.ChildNodes[1].InnerText.IndexOf("cm3")); typeObj.ModelTypeFuel = node.ChildNodes[2].InnerText; typeObj.ModelTypePower = node.ChildNodes[3].InnerText; typeObj.ModelTypeTank = node.ChildNodes[4].InnerText; typeObj.ModelTypeFromYear = node.ChildNodes[5].InnerText; typeObj.ModelTypeToYear = node.ChildNodes[6].InnerText; string value = node.ChildNodes[0].Attributes.AttributesWithName("onclick").First().Value; value = value.Substring(value.IndexOf("'") + 1, value.LastIndexOf("'") - value.IndexOf("'") - 1); typeObj.ModelTypeDetailsUrl = value; modelTypesList.Add(typeObj); } } }
public void Insert_CarModelType(ModelTypeObj obj, long makerId, long modelId) { try { string query = string.Format("INSERT INTO {0} " + "(maker_id, model_id, modeltype_id, name, cubic, fuel, power, tank, from_year, to_year, chassis, doors, type_url) " + "VALUES" + "(@maker_id, @model_id, @modeltype_id, @name, @cubic, @fuel, @power, @tank, @from_year, @to_year, @chassis, @doors, @type_url)", MySQLExporter.MODELTYPE_TABLE); //open connection if (_mySqlExporter.connection.State == ConnectionState.Open || _mySqlExporter.OpenConnection() == true) { //create command and assign the query and connection from the constructor MySqlCommand cmd = new MySqlCommand(query, _mySqlExporter.connection); //cmd.CommandText = query; SetSQLParameters(obj, cmd, makerId, modelId); //Execute command cmd.ExecuteNonQuery(); //close connection _mySqlExporter.CloseConnection(); } } catch (Exception ex) { if (Core != null && Core.Log != null) { Core.Log.Error(string.Format("SQLCarModelType::Insert_CarModel : {0}", ex.Message)); } else { throw new Exception("SQLCarModelType::Insert_CarModel", ex); } } }
private void GetModelTypes(ModelObj modelObj) { string modelTypesUrl = string.Format("{0}{1}", baseUrl, modelObj.ModelUrlPath); string modelsContent = GetContent(modelTypesUrl); var htmlDoc = new HtmlDocument(); htmlDoc.LoadHtml(modelsContent); HtmlNodeCollection types_div = null; if (htmlDoc.DocumentNode != null) { types_div = htmlDoc.DocumentNode.SelectNodes("//*[@id=\"car_db_select_hits\"]/tbody"); } if (types_div != null) { int limit_cnt = 0; foreach (var node in types_div.First().ChildNodes) { try { if (node.Name.ToLower().Equals("tr")) { limit_cnt++; ModelTypeObj typeObj = new ModelTypeObj(); typeObj.ModelTypeID = modelTypesList.Count + 1; typeObj.ModelID = modelObj.ModelID; typeObj.MakerName = modelObj.MakerName; typeObj.ModelName = modelObj.ModelName; typeObj.ModelTypeDetailsUrl = node.ChildNodes[3].ChildNodes[1].Attributes .AttributesWithName("href") .First().Value; typeObj.ModelTypeName = node.ChildNodes[5].InnerText.Trim(); typeObj.ModelTypeChassis = node.ChildNodes[7].InnerText.Trim(); typeObj.ModelTypeDoors = node.ChildNodes[9].InnerText.Trim().ToInt32OrDefault(0); typeObj.ModelTypeFuel = node.ChildNodes[11].InnerText.Trim(); typeObj.ModelTypePower = node.ChildNodes[13].InnerText.Trim(); typeObj.ModelTypeCubic = node.ChildNodes[17].InnerText.Trim(); //link to details page //Type Name //Chassis //Doors //Fuel //KW modelTypesList.Add(typeObj); //DEBUG: Break after x number of model types if (IsLimited(limit_cnt)) { break; } } } catch (Exception ex) { if (Core != null && Core.Log != null) { Core.Log.Error(string.Format("ADACImporter::GetModelTypes : {0}", ex.Message)); } else { throw new Exception("ADACImporter::GetModelTypes", ex); } } } } }