static void Main()
{
// Working!
var db = new CarsDbContext();
db.Configuration.AutoDetectChangesEnabled = false;
for (int i = 0; i <= 4; i++)
{
var filePath = "../../../../JSONData/data." + i + ".json";
var jsonText = File.ReadAllText(filePath);
JArray allCars = JArray.Parse(jsonText);
Console.WriteLine("Adding cars from file: {0}", filePath);
int counter = 0;
foreach (var car in allCars)
{
Manufacturer newManufacturer = new Manufacturer
{
Name = car["ManufacturerName"].ToString()
};
Dealer newDealer = new Dealer
{
Name = car["Dealer"]["Name"].ToString()
};
newDealer.Cities.Add(new City { Name = car["Dealer"]["City"].ToString() });
Car newCar = new Car
{
Model = car["Model"].ToString(),
TransmisionType = int.Parse(car["TransmissionType"].ToString()),
Price = decimal.Parse(car["Price"].ToString()),
Year = int.Parse(car["Year"].ToString()),
Manufacturer = newManufacturer,
Dealer = newDealer
};
newManufacturer.Cars.Add(newCar);
newDealer.Cars.Add(newCar);
db.Manufacturers.Add(newManufacturer);
db.Dealers.Add(newDealer);
db.Cars.Add(newCar);
Console.Write(".");
if (counter % 100 == 0)
{
db.SaveChanges();
}
counter++;
}
db.SaveChanges();
Console.WriteLine("\nFile Read Complete -> All Cars where added successfuly!");
Console.WriteLine("\n");
}
db.Configuration.AutoDetectChangesEnabled = false;
}
private static void CreateSampleExport()
{
var db = new CarsDbContext();
var cars =
db.Cars.Where(car => car.Id <= 4)
.OrderBy(car => car.Id)
.Select(
car =>
new Car
{
Id = car.Id,
Manufacturer = car.Manufacturer.Name,
Model = car.Model,
Price = car.Price,
Year = car.Year,
TransmissionType =
car.TransmissionType == Models.TransmissionType.Manual ? "manual" : "automatic",
Dealer =
new Dealer
{
Name = car.Dealer.Name,
Cities = car.Dealer.Cities.Select(city => city.Name).ToList(),
}
})
.ToList();
var serializer = new XmlSerializer(cars.GetType(), new XmlRootAttribute("Cars"));
using (var fs = new FileStream("Cars.xml", FileMode.Create))
{
serializer.Serialize(fs, cars);
}
}
private static void Main()
{
var databaseContext = new CarsDbContext();
databaseContext.Cars.Count();
databaseContext.Configuration.AutoDetectChangesEnabled = false;
const string JsonImportFilePath = @"..\..\input\";
var jsonImporter = new JsonImporter(databaseContext, new ConsoleLogger());
jsonImporter.ImportAll(JsonImportFilePath);
databaseContext.Configuration.AutoDetectChangesEnabled = true;
}
static void Main()
{
// This is quite a mess. It is not working properly.
// It was too hard for me to solve this problem.
// :(
var db = new CarsDbContext();
var xmlQueries = XElement.Load("../../../../QueriesData/queries.xml").Elements();
var result = new XElement("search-results");
foreach (var xmlQuery in xmlQueries)
{
var queryInCars = db.Cars.AsQueryable();
var queryNodes = xmlQuery.Nodes();
foreach (XElement node in queryNodes)
{
Console.WriteLine(node.Name);
if (node.Name == "OrderBy")
{
string idValue = node.Value;
queryInCars = queryInCars.OrderBy(c => c.Id);
}
if (node.Name == "WhereClauses")
{
var allWhereClauses = node.Elements();
foreach (var whereClauseNode in allWhereClauses)
{
string PropertyName = whereClauseNode.Attribute("PropertyName").Value;
string CompareBy = whereClauseNode.Attribute("Type").Value;
if (PropertyName == "Year")
{
if (CompareBy == "GreaterThan")
{
queryInCars.Where(c => c.Year > int.Parse(whereClauseNode.Value));
}
else if (CompareBy == "Equals")
{
queryInCars.Where(c => c.Year == int.Parse(whereClauseNode.Value));
}
}
else if (PropertyName == "City")
{
if (CompareBy == "Equals")
{
queryInCars.Where(c => c.Dealer.Cities.FirstOrDefault().Name == whereClauseNode.Value);
}
}
}
}
}
var resultSet = queryInCars
.Select(c => new
{
Model = c.Model,
TransmisionType = c.TransmisionType,
Year = c.Year,
Price = c.Price
}).ToList();
var xmlResultSet = new XElement("result-set");
foreach (var carInResult in resultSet)
{
var xmlReview = new XElement("Car");
xmlReview.Add(new XElement("Model", carInResult.Model));
xmlReview.Add(new XElement("TransmisionType", carInResult.TransmisionType));
xmlReview.Add(new XElement("Year", carInResult.Year));
xmlReview.Add(new XElement("Price", carInResult.Price));
xmlResultSet.Add(xmlReview);
}
result.Add(xmlResultSet);
}
result.Save("../../../../reviews-search-results.xml");
}
private static void ProcessQuery(Query query)
{
var db = new CarsDbContext();
var dataQuery =
db.Cars.Select(
car =>
new Car
{
Id = car.Id,
Manufacturer = car.Manufacturer.Name,
Model = car.Model,
Price = car.Price,
Year = car.Year,
TransmissionType =
car.TransmissionType == Models.TransmissionType.Manual ? "manual" : "automatic",
Dealer =
new Dealer
{
Name = car.Dealer.Name,
Cities = car.Dealer.Cities.Select(city => city.Name).ToList(),
}
});
switch (query.OrderBy)
{
case "Id":
dataQuery = dataQuery.OrderBy(x => x.Id);
break;
case "Year":
dataQuery = dataQuery.OrderBy(x => x.Year);
break;
case "Model":
dataQuery = dataQuery.OrderBy(x => x.Model);
break;
case "Price":
dataQuery = dataQuery.OrderBy(x => x.Price);
break;
case "Manufacturer":
dataQuery = dataQuery.OrderBy(x => x.Manufacturer);
break;
case "Dealer":
dataQuery = dataQuery.OrderBy(x => x.Dealer.Name);
break;
}
foreach (var whereClause in query.WhereClauses)
{
if (whereClause.PropertyName == "Id")
{
var constant = int.Parse(whereClause.Value);
switch (whereClause.TypeAsEnum)
{
case WhereType.Equals:
dataQuery = dataQuery.Where(x => x.Id == constant);
break;
case WhereType.GreaterThan:
dataQuery = dataQuery.Where(x => x.Id > constant);
break;
case WhereType.LessThan:
dataQuery = dataQuery.Where(x => x.Id < constant);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
else if (whereClause.PropertyName == "Year")
{
var constant = int.Parse(whereClause.Value);
switch (whereClause.TypeAsEnum)
{
case WhereType.Equals:
dataQuery = dataQuery.Where(x => x.Year == constant);
break;
case WhereType.GreaterThan:
dataQuery = dataQuery.Where(x => x.Year > constant);
break;
case WhereType.LessThan:
dataQuery = dataQuery.Where(x => x.Year < constant);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
else if (whereClause.PropertyName == "Price")
{
Console.WriteLine("will parse: {0}", whereClause.Value);
var constant = decimal.Parse(whereClause.Value, CultureInfo.InvariantCulture);
switch (whereClause.TypeAsEnum)
{
case WhereType.Equals:
dataQuery = dataQuery.Where(x => x.Price == constant);
break;
case WhereType.GreaterThan:
dataQuery = dataQuery.Where(x => x.Price > constant);
break;
case WhereType.LessThan:
dataQuery = dataQuery.Where(x => x.Price < constant);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
else if (whereClause.PropertyName == "Model")
{
switch (whereClause.TypeAsEnum)
{
case WhereType.Equals:
dataQuery = dataQuery.Where(x => x.Model == whereClause.Value);
break;
case WhereType.Contains:
dataQuery = dataQuery.Where(x => x.Model.Contains(whereClause.Value));
break;
default:
throw new ArgumentOutOfRangeException();
}
}
else if (whereClause.PropertyName == "Manufacturer")
{
switch (whereClause.TypeAsEnum)
{
case WhereType.Equals:
dataQuery = dataQuery.Where(x => x.Manufacturer == whereClause.Value);
break;
case WhereType.Contains:
dataQuery = dataQuery.Where(x => x.Manufacturer.Contains(whereClause.Value));
break;
default:
throw new ArgumentOutOfRangeException();
}
}
else if (whereClause.PropertyName == "Dealer")
{
switch (whereClause.TypeAsEnum)
{
case WhereType.Equals:
dataQuery = dataQuery.Where(x => x.Dealer.Name == whereClause.Value);
break;
case WhereType.Contains:
dataQuery = dataQuery.Where(x => x.Dealer.Name.Contains(whereClause.Value));
break;
default:
throw new ArgumentOutOfRangeException();
}
}
else if (whereClause.PropertyName == "City")
{
switch (whereClause.TypeAsEnum)
{
case WhereType.Equals:
dataQuery = dataQuery.Where(x => x.Dealer.Cities.Contains(whereClause.Value));
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
var data = dataQuery.ToList();
var serializer = new XmlSerializer(data.GetType(), new XmlRootAttribute("Cars"));
using (var fs = new FileStream("./QueryResults/"+query.OutputFileName, FileMode.Create))
{
serializer.Serialize(fs, data);
}
Console.WriteLine("{0} ready.", query.OutputFileName);
}
public JsonImporter(CarsDbContext databaseContext, ILogger logger)
{
this.databaseContext = databaseContext;
this.logger = logger;
}
private static void ImportData()
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<CarsDbContext, Configuration>());
var cars = new List<Car>();
var files = Directory.GetFiles(Environment.CurrentDirectory).Where(fileName => fileName.EndsWith(".json")).ToList();
foreach (var file in files)
{
var fileContent = File.ReadAllText(file);
var fileCars = JsonConvert.DeserializeObject<IEnumerable<Car>>(fileContent);
cars.AddRange(fileCars);
Console.WriteLine("{0} read.", file);
}
var manufacturerNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
var cityNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
var dealerNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
foreach (var car in cars)
{
manufacturerNames.Add(car.ManufacturerName);
cityNames.Add(car.Dealer.City);
dealerNames.Add(car.Dealer.Name);
}
var db = new CarsDbContext();
db.Configuration.AutoDetectChangesEnabled = false;
// Add cities
Console.WriteLine("Adding cities...");
foreach (var cityName in cityNames)
{
db.Cities.AddOrUpdate(c => c.Name, new City { Name = cityName });
}
db.SaveChanges();
Console.WriteLine("Cities added.");
// Add manufacturers
Console.WriteLine("Adding manufacturers...");
foreach (var manufacturerName in manufacturerNames)
{
db.Manufacturers.AddOrUpdate(m => m.Name, new Manufacturer { Name = manufacturerName });
}
db.SaveChanges();
Console.WriteLine("Manufacturers added.");
// Add dealers
Console.WriteLine("Adding dealers...");
foreach (var dealerName in dealerNames)
{
db.Dealers.AddOrUpdate(d => d.Name, new Dealer { Name = dealerName });
}
db.SaveChanges();
Console.WriteLine("Dealers added.");
// Add cars
Console.Write("Adding cars");
for (int carIndex = 0; carIndex < cars.Count; carIndex++)
{
var car = cars[carIndex];
var databaseCarCity = db.Cities.FirstOrDefault(x => x.Name == car.Dealer.City);
if (databaseCarCity == null)
{
throw new ArgumentException("Invalid city name!");
}
var databaseCar = new Cars.Models.Car
{
Dealer = db.Dealers.FirstOrDefault(d => d.Name == car.Dealer.Name),
Manufacturer =
db.Manufacturers.FirstOrDefault(x => x.Name == car.ManufacturerName),
Model = car.Model,
Price = car.Price,
TransmissionType = car.TransmissionType,
Year = (short)car.Year,
};
db.Configuration.AutoDetectChangesEnabled = true;
if (!databaseCar.Dealer.Cities.Any(c => c.Name == databaseCarCity.Name))
{
databaseCarCity.Dealers.Add(databaseCar.Dealer);
}
db.Cars.Add(databaseCar);
if (carIndex % 100 == 0)
{
db.SaveChanges();
Console.Write(".");
}
db.Configuration.AutoDetectChangesEnabled = false;
}
Console.WriteLine();
db.SaveChanges();
Console.WriteLine("Cars added.");
}
public DatabaseSeeder(CarsDbContext carsDbContext)
{
this.carsDbContext = carsDbContext;
this.InitializeDictionaries();
}
public static void ExecuteAllFromFile(string queriesFilePath, string resultFilePath)
{
db = new CarsDbContext();
ConnectToDB();
var xmlQueries = XDocument.Load(queriesFilePath).Element("Queries");
int valuesCounter = 1;
foreach (var xmlQuery in xmlQueries.Elements("Query"))
{
SqlCommand cmdQuery = new SqlCommand(
"SELECT * FROM Cars", databaseConnection);
var query = "SELECT * FROM Cars ";
string currentFileName = xmlQuery.Attribute("OutputFileName").Value;
var orderByCondition = xmlQuery.Element("OrderBy").Value;
var xmlWhereClauses = xmlQuery.Element("WhereClauses").Elements("WhereClause");
int counter = 0;
foreach (var xmlWhereClause in xmlWhereClauses)
{
counter++;
valuesCounter++;
var propertyName = xmlWhereClause.Attribute("PropertyName").Value;
var type = xmlWhereClause.Attribute("Type").Value;
var whereClauseValue = xmlWhereClause.Value;
if (counter == 1)
{
cmdQuery.CommandText += " Where ";
query += " WHERE ";
}
else
{
cmdQuery.CommandText += " And ";
query += " AND ";
}
cmdQuery.CommandText += string.Format("{0} {1} @currValue{2}", propertyName, type, valuesCounter);
cmdQuery.Parameters.AddWithValue(string.Format("@currValue{0}", valuesCounter), whereClauseValue);
query += propertyName + " " + type + " '" + whereClauseValue + "'";
}
cmdQuery.CommandText += " ORDER BY " + orderByCondition;
query += " ORDER BY " + orderByCondition;
////StreamWriter sw = new StreamWriter(resultFilePath + currentFileName);
////using (sw)
////{
//// sw.WriteLine(query);
////}
Console.WriteLine(query);
////SqlDataReader reader = cmdQuery.ExecuteReader();
////using (reader)
////{
//// if (reader.Read())
//// {
//// var model = (string)reader["Model"];
//// var transmissionType = (TransmissionType)reader["TransmissionType"];
//// var year = (int)reader["Year"];
//// var price = (decimal)reader["Price"];
//// var manufacturerId = (int)reader["ManufacturerId"];
//// var dealerId = (int)reader["DealerId"];
//// //if (endDateObj != DBNull.Value)
//// //{
//// // endDate = (DateTime)endDateObj;
//// //}
//// Car currentCar = new Car
//// {
//// Model = model,
//// TransmissionType = transmissionType,
//// Year = year,
//// Price = price,
//// Manufacturer = db.Manufacturers.FirstOrDefault(m => m.Id == manufacturerId),
//// Dealer = db.Dealers.FirstOrDefault(d => d.Id == dealerId)
//// };
//// }
//// }
}
DisconnectFromDB();
}
private static void ProcessQuery(QueriesQuery query)
{
var db = new CarsDbContext();
var carsQuery = db.Cars.AsQueryable();
foreach (var whereClause in query.WhereClauses)
{
switch (whereClause.PropertyName)
{
case "Id":
switch (whereClause.Type)
{
case "Equals":
carsQuery = carsQuery.Where(c => c.Id == int.Parse(whereClause.Value));
break;
case "GreaterThan":
carsQuery = carsQuery.Where(c => c.Id > int.Parse(whereClause.Value));
break;
case "LessThan":
carsQuery = carsQuery.Where(c => c.Id < int.Parse(whereClause.Value));
break;
default:
break;
}
break;
case "Year":
break;
case "Price":
break;
case "Model":
break;
case "Manufacturer":
break;
case "Dealer":
break;
case "City":
break;
default:
break;
}
switch (query.OrderBy)
{
case "Id": carsQuery = carsQuery.OrderBy(c => c.Id);
break;
case "Year": carsQuery = carsQuery.OrderBy(c => c.Year);
break;
case "Model": carsQuery = carsQuery.OrderBy(c => c.Model);
break;
case "Price": carsQuery = carsQuery.OrderBy(c => c.Price);
break;
case "Manufacturer": carsQuery = carsQuery.OrderBy(c => c.Manufacturer.Name);
break;
case "Dealer": carsQuery = carsQuery.OrderBy(c => c.Dealer.Name);
break;
default:
break;
}
}
}
public static void ImportFromDir(string dataFilesDirectory)
{
string[] jsonFilePaths = Directory.GetFiles(dataFilesDirectory + "Json", "*.json");
int currentlyAddedCarsCounter = 0;
Stopwatch sw = new Stopwatch();
sw.Start();
db = new CarsDbContext();
db.Configuration.AutoDetectChangesEnabled = false;
Console.WriteLine("Adding cars into DB from Json - one dot = 100 cars added (saving after each car)");
foreach (var filePath in jsonFilePaths)
{
string currentFileText = File.ReadAllText(filePath);
JArray allCarInfoAsObjects = JArray.Parse(currentFileText);
foreach (JObject item in allCarInfoAsObjects)
{
currentlyAddedCarsCounter++;
if (currentlyAddedCarsCounter >= 100)
{
Console.Write(".");
db = new CarsDbContext();
db.Configuration.AutoDetectChangesEnabled = false;
currentlyAddedCarsCounter = 0;
}
var year = (int)item["Year"];
var transmissionType = (int)item["TransmissionType"];
var manufacturerName = (string)item["ManufacturerName"];
var model = (string)item["Model"];
var price = (decimal)item["Price"];
var dealerName = (string)item["Dealer"]["Name"];
var cityName = (string)item["Dealer"]["City"];
Manufacturer currentManufacturer = GetManufacturer(manufacturerName);
if (!ManufacturerExists(manufacturerName))
{
db.Manufacturers.Add(currentManufacturer);
}
City currentCity = GetCity(cityName);
if (!CityExists(cityName))
{
db.Cities.Add(currentCity);
}
Dealer currentDealer = new Dealer
{
Name = dealerName,
};
currentDealer.Cities.Add(currentCity);
db.Dealers.Add(currentDealer);
Car currentCar = new Car
{
Year = year,
TransmissionType = (TransmissionType)transmissionType,
Manufacturer = currentManufacturer,
Model = model,
Price = price,
Dealer = currentDealer
};
db.Cars.Add(currentCar);
db.SaveChanges();
}
}
Console.WriteLine("\nAll cars added! Finally.....");
Console.WriteLine("Elapsed time: {0}", sw.Elapsed);
db.Configuration.AutoDetectChangesEnabled = true;
}
public XmlSearcher(CarsDbContext databaseContext, ILogger logger)
{
this.databaseContext = databaseContext;
this.logger = logger;
}
public static void Import()
{
var carsToAdd = Directory
.GetFiles(Directory.GetCurrentDirectory() + "/JsonFiles/")
.Where(f => f.EndsWith("json"))
.Select(f => File.ReadAllText(f))
.SelectMany(str => JsonConvert.DeserializeObject<IEnumerable<JsonCar>>(str))
.ToList();
var addedCities = new HashSet<string>();
var addedManufacturers = new HashSet<string>();
var addedCars = 0;
var db = new CarsDbContext();
db.Configuration.AutoDetectChangesEnabled = false;
Console.WriteLine("Adding cars");
foreach (var car in carsToAdd)
{
var cityName = car.Dealer.City;
if (!addedCities.Contains(car.Dealer.City))
{
var city = new City
{
Name = cityName
};
db.Cities.Add(city);
db.SaveChanges();
addedCities.Add(cityName);
}
var manufacturer = car.ManufacturerName;
if (!addedManufacturers.Contains(car.ManufacturerName))
{
var newManufacturer = new Manufacturer
{
Name = manufacturer
};
addedManufacturers.Add(manufacturer);
db.Manufacturers.Add(newManufacturer);
db.SaveChanges();
}
var dealerToAdd = new Cars.Models.Dealer
{
Name = car.Dealer.Name
};
var dbCity = db.Cities
.FirstOrDefault(c => c.Name == cityName);
dealerToAdd.Cities.Add(dbCity);
var dbManufacturer = db.Manufacturers
.FirstOrDefault(m => m.Name == car.ManufacturerName);
var carToAdd = new Car
{
Manufacturer = dbManufacturer,
Dealer = dealerToAdd,
Model = car.Model,
Price = car.Price,
TransmissionType = car.TransmissionType,
Year = (short)car.Year
};
db.Cars.Add(carToAdd);
if (addedCars % 100 == 0)
{
Console.Write(".");
db.SaveChanges();
db.Dispose();
db = new CarsDbContext();
db.Configuration.AutoDetectChangesEnabled = false;
}
addedCars++;
}
db.SaveChanges();
db.Configuration.AutoDetectChangesEnabled = true;
}
public XmlQueryBuilder(CarsDbContext carsDbContext, XmlQueryWriter xmlQueryWriter)
{
this.carsDbContext = carsDbContext;
this.xmlQueryWriter = xmlQueryWriter;
}
