public void Escape_Characters() { IEngine engine = new MySQLEngine(); Assert.Equal('`', engine.Options.EscapeStart); Assert.Equal('`', engine.Options.EscapeEnd); }
public static WPExportDTO RunAllQueries( WPConfigurationSourceDTO configurationSource, WPConfigurationPluginExportDTO configurationPlugin) { MySQLEngine engine = new MySQLEngine( configurationSource.DB_HOST, configurationSource.DB_NAME, configurationSource.DB_USER, configurationSource.DB_PASSWORD ); WPQuery queries = new WPQuery(configurationSource.TABLE_PREFIX); WPExportDTO result = new WPExportDTO(); result.WPPosts = engine.Select <WPPostDTO>(queries.GetWPPosts); result.WPPostChildren = engine.Select <WPPostDTO>(queries.GetWPPostChildren); result.WPTags = engine.Select <WPTagDTO>(queries.GetWPTags); result.WPCategories = engine.Select <WPCategoryDTO>(queries.GetWPCategories); result.WPUsers = engine.Select <WPUserDTO>(queries.GetWPUsers); result.WPPostsMeta = engine.Select <WPPostMetaDTO>(queries.GetWPPostMeta); if (configurationPlugin.WooCommerce) { result.WPProducts = engine.Select <WPProductDTO>(queries.GetWPProducts); result.WPProductChildren = engine.Select <WPProductDTO>(queries.GetWPProductChildren); } return(result); }
public void DoWork() { WPExportDTO exportDTO = WPExportEngine.RunAllQueries( _wpSource, _wpConfigurationPluginExportDTO); string json = string.Empty; if (string.IsNullOrEmpty(_configurationOUTFile.DirtyExportFile) == false) { DirtyWPToJson dirtyWPToJson = new DirtyWPToJson(exportDTO); json = dirtyWPToJson.CreateJSON(Newtonsoft.Json.Formatting.None); FileHelper.WriteToFile(_configurationOUTFile.DirtyExportFile, json); } WPToJson wPToJson = new WPToJson(exportDTO) { ExportSeoWithYoast = _wpConfigurationPluginExportDTO.Yoast }; json = wPToJson.CreateJSON(Newtonsoft.Json.Formatting.Indented); if (string.IsNullOrEmpty(_configurationOUTFile.ExportFile) == false) { FileHelper.WriteToFile(_configurationOUTFile.ExportFile, json); } WPExportResult wp = Newtonsoft.Json.JsonConvert.DeserializeObject <WPExportResult>(json); ISQLEngine engine = null; if (string.IsNullOrEmpty(_configurationOUTFile.SQLServerConnection) == false) { engine = new SQLServerEngine(_configurationOUTFile.SQLServerConnection); ExportToDatabase export = new ExportToDatabase(engine); var result = export.Run(wp).Result; } if (string.IsNullOrEmpty(_configurationOUTFile.MySQLServerConnection) == false) { engine = new MySQLEngine(_configurationOUTFile.MySQLServerConnection); ExportToDatabase export = new ExportToDatabase(engine); var result = export.Run(wp).Result; } }
public async Task <IActionResult> ConvertJSONToSQL(ImportToMySQLDTO values) { if (values.contents.Length == 0) { return(null); } WPExportResult wp = new WPExportResult(); using (var ms = new MemoryStream()) { values.contents.CopyTo(ms); var fileBytes = ms.ToArray(); string json = Encoding.ASCII.GetString(fileBytes); wp = Newtonsoft.Json.JsonConvert.DeserializeObject <WPExportResult>(json); } if (wp == null) { return(null); } ISQLEngine engine = new MySQLEngine(values.ConnectionString); ExportToDatabase export = new ExportToDatabase(engine); var result = await export.Run(wp); StringBuilder sb = new StringBuilder(); foreach (var item in result) { sb.AppendFormat("{0}: {1} {2} | ", item.Key, item.Value, Environment.NewLine); } string log = sb.ToString(); return(View("Index")); }
public void Engine_Name() { IEngine engine = new MySQLEngine(); Assert.Equal(EngineName.MySQL, engine.Options.Name); }
public MainWindow() { InitializeComponent(); bool IsMySQLDB = true; DataSet busDataSet = AccessEngine.Get("SELECT DISTINCT(bus_id) FROM [IntervalData2014-2015]"); DataSet intervalDataSet = AccessEngine.Get("SELECT * FROM [IntervalData2014-2015]"); DataSet routeDataSet = AccessEngine.Get("SELECT * FROM [Route ID]"); DataSet stopDataSet = AccessEngine.Get("SELECT * FROM [Stop ID]"); DataSet weatherDataSet = AccessEngine.Get("SELECT * FROM [Weather Data]"); List <Bus> buses = new List <Bus>(); foreach (DataRow row in busDataSet.Tables[0].Rows) { Bus bus = new Bus(); bus.ID = SQLUtil.ParseID(row[0]); bus.Description = string.Empty; buses.Add(bus); } List <Route> routes = new List <Route>(); foreach (DataRow row in routeDataSet.Tables[0].Rows) { Route route = new Route(); route.ID = SQLUtil.ParseID(row["ID"]); string info = SQLUtil.ParseString(row["Route ID"]); string[] infoArray = info.Split(' '); if (infoArray.Length > 1 && infoArray[0].Length == 1) { route.Name = infoArray[0]; } else { route.Name = info; } route.RouteDetail = info; if (route.RouteDetail.Contains("M-R")) { route.IsMtoR = true; } else { route.IsMtoR = false; } routes.Add(route); } List <RouteDetail> routeDetails = new List <RouteDetail>(); List <string> lines = new List <string>(); //Build each route. lines = CSVUtil.ReadCSV("A.csv"); int ARouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value; for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header { string[] line = lines[i].Split(','); RouteDetail rd = new RouteDetail(); rd.RouteID = ARouteID; rd.Sequence = i - 1; rd.FromBusStopID = int.Parse(line[0]); rd.ToBusStopID = int.Parse(line[1]); if (rd.FromBusStopID == rd.ToBusStopID) { rd.IsStationary = true; } else { rd.IsStationary = false; } rd.Distance = double.Parse(line[2]); rd.IdealTravelTime = int.Parse(line[3]); rd.ScheduledTravelTime = int.Parse(line[3]); if (rd.IsStationary) { rd.ScheduledStopTime = int.Parse(line[3]); } routeDetails.Add(rd); } lines.Clear(); lines = CSVUtil.ReadCSV("B.csv"); int BRouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value; for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header { string[] line = lines[i].Split(','); RouteDetail rd = new RouteDetail(); rd.RouteID = BRouteID; rd.Sequence = i - 1; rd.FromBusStopID = int.Parse(line[0]); rd.ToBusStopID = int.Parse(line[1]); if (rd.FromBusStopID == rd.ToBusStopID) { rd.IsStationary = true; } else { rd.IsStationary = false; } rd.Distance = double.Parse(line[2]); rd.IdealTravelTime = int.Parse(line[3]); rd.ScheduledTravelTime = int.Parse(line[3]); if (rd.IsStationary) { rd.ScheduledStopTime = int.Parse(line[3]); } routeDetails.Add(rd); } lines.Clear(); lines = CSVUtil.ReadCSV("E.csv"); int ERouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value; for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header { string[] line = lines[i].Split(','); RouteDetail rd = new RouteDetail(); rd.RouteID = ERouteID; rd.Sequence = i - 1; rd.FromBusStopID = int.Parse(line[0]); rd.ToBusStopID = int.Parse(line[1]); if (rd.FromBusStopID == rd.ToBusStopID) { rd.IsStationary = true; } else { rd.IsStationary = false; } rd.Distance = double.Parse(line[2]); rd.IdealTravelTime = int.Parse(line[3]); rd.ScheduledTravelTime = int.Parse(line[3]); if (rd.IsStationary) { rd.ScheduledStopTime = int.Parse(line[3]); } routeDetails.Add(rd); } lines.Clear(); lines = CSVUtil.ReadCSV("X.csv"); int XRouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value; for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header { string[] line = lines[i].Split(','); RouteDetail rd = new RouteDetail(); rd.RouteID = ERouteID; rd.Sequence = i - 1; rd.FromBusStopID = int.Parse(line[0]); rd.ToBusStopID = int.Parse(line[1]); if (rd.FromBusStopID == rd.ToBusStopID) { rd.IsStationary = true; } else { rd.IsStationary = false; } rd.Distance = double.Parse(line[2]); rd.IdealTravelTime = int.Parse(line[3]); rd.ScheduledTravelTime = int.Parse(line[3]); if (rd.IsStationary) { rd.ScheduledStopTime = int.Parse(line[3]); } routeDetails.Add(rd); } List <BusStop> busStops = new List <BusStop>(); //foreach (DataRow row in stopDataSet.Tables[0].Rows) //{ // BusStop busStop = new BusStop(); // busStop.ID = SQLUtil.ParseID(row["ID"]); // busStop.Name = SQLUtil.ParseString(row["Stop"]); // busStop.IsMajorStop = false; // busStops.Add(busStop); //} lines.Clear(); lines = CSVUtil.ReadCSV("Combined GPS Data.csv"); for (int i = 1; i < lines.Count; i++) // Line 0 is header { string[] line = lines[i].Split(','); BusStop busStop = new BusStop(); busStop.ID = int.Parse(line[0]); busStop.Name = line[1]; busStop.Latitude = double.Parse(line[2]); busStop.Longitude = double.Parse(line[3]); busStop.Buddy = line[4]; if (line[5].ToLower() == "yes") { busStop.Visible = true; } else { busStop.Visible = false; } if (line[6].ToLower() == "yes") { busStop.Announce = true; } else { busStop.Announce = false; } busStops.Add(busStop); } List <Weather> weathers = new List <Weather>(); foreach (DataRow row in weatherDataSet.Tables[0].Rows) { Weather weather = new Weather(); weather.ID = SQLUtil.ParseID(row["ID"]); weather.Date = SQLUtil.ParseDateTime(row["EDT"]); weather.MinTemp = SQLUtil.ParseInt(row["MinTemp"]); weather.Precipitation = SQLUtil.ParseString(row["Precipitation"]); weather.Events = SQLUtil.ParseString(row["Events"]); weathers.Add(weather); } List <Interval> intervals = new List <Interval>(); foreach (DataRow row in intervalDataSet.Tables[0].Rows) { Interval interval = new Interval(); //interval.ID = SQLUtil.ParseID(row["ID1"]); interval.BusID = SQLUtil.ParseInt(row["bus_id"]); string routeName = SQLUtil.ParseString(row["route_id"]); if (routeName == "354") { routeName = "A"; } interval.RouteObj = routes.First(o => o.Name == routeName && o.IsMtoR == true); interval.FromBusStopID = SQLUtil.ParseInt(row["from"]); string toBusStop = SQLUtil.ParseString(row["to"]); interval.ToBusStopID = busStops.First(o => o.Name.ToLower().Contains(toBusStop.ToLower())).ID.Value; if (interval.FromBusStopID == interval.ToBusStopID) { interval.IsStationary = true; } else { interval.IsStationary = false; } interval.TimeInterval = SQLUtil.ParseInt(row["time"]); interval.Timestamp = SQLUtil.ParseDateTime(row["when"]); Weather temp = weathers.FirstOrDefault(o => o.DateString == interval.Timestamp.ToShortDateString()); if (temp != null) { interval.MinTemp = temp.MinTemp; interval.Precipitation = temp.Precipitation; interval.Events = temp.Events; } intervals.Add(interval); } DataTable rdDataTable; if (IsMySQLDB) { MySQLEngine.OpenDBConnection(); MySqlCommand myCmd = new MySqlCommand(); foreach (Bus bus in buses) { myCmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')"; MySQLEngine.ExecuteCommandText(myCmd); } foreach (Bus bus in buses) { myCmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')"; MySQLEngine.ExecuteCommandText(myCmd); } foreach (BusStop busStop in busStops) { myCmd.CommandText = "INSERT INTO TBL_BusStop(ID, Name, IsMajorStop, Latitude, Longitude, Buddy, Visible, Announce) VALUES (" + busStop.ID + ", '" + busStop.Name.Replace("'", "''") + "', '" + busStop.IsMajorStop + "', " + busStop.Latitude + ", " + busStop.Longitude + ", '" + busStop.Buddy + "', '" + busStop.Visible + "', '" + busStop.Announce + "')"; MySQLEngine.ExecuteCommandText(myCmd); } foreach (Route route in routes) { myCmd.CommandText = "INSERT INTO TBL_Route(ID, Name, RouteDetail, IsMtoR) VALUES (" + route.ID + ", '" + route.Name + "', '" + route.RouteDetail + "', '" + route.IsMtoR.ToString() + "')"; MySQLEngine.ExecuteCommandText(myCmd); } foreach (RouteDetail rd in routeDetails) { myCmd.CommandText = "INSERT INTO TBL_RouteDetail(RouteID, Sequence, FromBusStopID, ToBusStopID, IsStationary, " + "Distance, IdealTravelTime, ScheduleStopTime, ScheduleTravelTime) VALUES (" + rd.RouteID + ", " + rd.Sequence + ", " + rd.FromBusStopID + ", " + rd.ToBusStopID + ", '" + rd.IsStationary + "', " + rd.Distance + ", " + rd.IdealTravelTime + ", " + rd.ScheduledStopTime + ", " + rd.ScheduledTravelTime + ")"; MySQLEngine.ExecuteCommandText(myCmd); myCmd.CommandText = "SELECT MAX(ID) FROM TBL_RouteDetail"; rdDataTable = MySQLEngine.GetDataTableCommandText(myCmd); rd.ID = SQLUtil.ParseID(rdDataTable.Rows[0][0]); } foreach (Interval interval in intervals) { interval.RouteDetailObj = routeDetails.FirstOrDefault(o => o.FromBusStopID == interval.FromBusStopID && o.ToBusStopID == interval.ToBusStopID && o.RouteID == interval.RouteID); myCmd.CommandText = "INSERT INTO TBL_Interval(BusID, RouteID, RouteDetailID, FromBusStopID, ToBusStopID, " + "IsStationary, TimeInterval, Timestamp, Day, MinTemp, Precipitation, Events) VALUES (" + interval.BusID + ", " + interval.RouteID + ", " + interval.RouteDetailID + ", " + interval.FromBusStopID + ", " + interval.ToBusStopID + ", '" + interval.IsStationary + "', " + interval.TimeInterval + ", '" + interval.Timestamp.ToString() + "', '" + interval.Day + "', " + interval.MinTemp + ", '" + interval.Precipitation + "', '" + interval.Events + "')"; MySQLEngine.ExecuteCommandText(myCmd); } MySQLEngine.CloseDBConnection(); } else { SQLEngine.OpenDBConnection(); SqlCommand cmd = new SqlCommand(); foreach (Bus bus in buses) { cmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')"; SQLEngine.ExecuteCommandText(cmd); } foreach (Bus bus in buses) { cmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')"; SQLEngine.ExecuteCommandText(cmd); } foreach (BusStop busStop in busStops) { cmd.CommandText = "INSERT INTO TBL_BusStop(ID, Name, IsMajorStop, Latitude, Longitude, Buddy, Visible, Announce) VALUES (" + busStop.ID + ", '" + busStop.Name.Replace("'", "''") + "', '" + busStop.IsMajorStop + "', " + busStop.Latitude + ", " + busStop.Longitude + ", '" + busStop.Buddy + "', '" + busStop.Visible + "', '" + busStop.Announce + "')"; SQLEngine.ExecuteCommandText(cmd); } foreach (Route route in routes) { cmd.CommandText = "INSERT INTO TBL_Route(ID, Name, RouteDetail, IsMtoR) VALUES (" + route.ID + ", '" + route.Name + "', '" + route.RouteDetail + "', '" + route.IsMtoR.ToString() + "')"; SQLEngine.ExecuteCommandText(cmd); } foreach (RouteDetail rd in routeDetails) { cmd.CommandText = "INSERT INTO TBL_RouteDetail(RouteID, Sequence, FromBusStopID, ToBusStopID, IsStationary, " + "Distance, IdealTravelTime, ScheduleStopTime, ScheduleTravelTime) VALUES (" + rd.RouteID + ", " + rd.Sequence + ", " + rd.FromBusStopID + ", " + rd.ToBusStopID + ", '" + rd.IsStationary + "', " + rd.Distance + ", " + rd.IdealTravelTime + ", " + rd.ScheduledStopTime + ", " + rd.ScheduledTravelTime + ")"; SQLEngine.ExecuteCommandText(cmd); cmd.CommandText = "SELECT MAX(ID) FROM TBL_RouteDetail"; rdDataTable = SQLEngine.GetDataTableCommandText(cmd); rd.ID = SQLUtil.ParseID(rdDataTable.Rows[0][0]); } foreach (Interval interval in intervals) { interval.RouteDetailObj = routeDetails.FirstOrDefault(o => o.FromBusStopID == interval.FromBusStopID && o.ToBusStopID == interval.ToBusStopID && o.RouteID == interval.RouteID); cmd.CommandText = "INSERT INTO TBL_Interval(BusID, RouteID, RouteDetailID, FromBusStopID, ToBusStopID, " + "IsStationary, TimeInterval, Timestamp, Day, MinTemp, Precipitation, Events) VALUES (" + interval.BusID + ", " + interval.RouteID + ", " + interval.RouteDetailID + ", " + interval.FromBusStopID + ", " + interval.ToBusStopID + ", '" + interval.IsStationary + "', " + interval.TimeInterval + ", '" + interval.Timestamp.ToString() + "', '" + interval.Day + "', " + interval.MinTemp + ", '" + interval.Precipitation + "', '" + interval.Events + "')"; SQLEngine.ExecuteCommandText(cmd); } SQLEngine.CloseDBConnection(); } }