Esempio n. 1
0
        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;
        }
Esempio n. 2
0
        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;
 }
Esempio n. 4
0
        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");
        }
Esempio n. 5
0
        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.");
        }
Esempio n. 8
0
 public DatabaseSeeder(CarsDbContext carsDbContext)
 {
     this.carsDbContext = carsDbContext;
     this.InitializeDictionaries();
 }
Esempio n. 9
0
        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();
        }
Esempio n. 10
0
        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;
                }
            }
        }
Esempio n. 11
0
        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;
 }
Esempio n. 13
0
        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;
        }
Esempio n. 14
0
 public XmlQueryBuilder(CarsDbContext carsDbContext, XmlQueryWriter xmlQueryWriter)
 {
     this.carsDbContext = carsDbContext;
     this.xmlQueryWriter = xmlQueryWriter;
 }