private bool FixPastelCustomerCategories(string dataPath, List <CustomerCategory> categories) { try { var allowedIds = categories.Select(a => a.CategoryId).ToArray(); string ids = "(" + string.Join(",", allowedIds) + ")"; var catListOnDB = GetCustomerCategories(dataPath); var listToUpdate = new List <CustomerCategory>(); var listToInsert = new List <CustomerCategory>(); foreach (var cat in categories) { var curr = catListOnDB.FirstOrDefault(a => a.CategoryId == cat.CategoryId); if (curr == null) { listToInsert.Add(cat); } else if (curr.CategoryName != cat.CategoryName) { listToUpdate.Add(cat); } } foreach (var i in listToInsert) { string q = "Insert into [DataSet].CustomerCategories (CCCode,CCDesc) values (" + i.CategoryId.ToString() + ",'" + i.CategoryName + "')"; q = PervasiveSqlUtilities.SetDataSource(q, dataPath); PervasiveSqlUtilities.ExecuteSQLCommand(q); } foreach (var i in listToUpdate) { Console.WriteLine("Update " + i.CategoryName + " for " + dataPath); string q = "Update [DataSet].CustomerCategories set CCDesc = '" + i.CategoryName + "' where CCCode = " + i.CategoryId.ToString(); q = PervasiveSqlUtilities.SetDataSource(q, dataPath); PervasiveSqlUtilities.ExecuteSQLCommand(q); } string odbcQuery = "update [DataSet].CustomerMaster set Category = 0 where Category not in " + ids; odbcQuery = PervasiveSqlUtilities.SetDataSource(odbcQuery, dataPath); PervasiveSqlUtilities.ExecuteSQLCommand(odbcQuery); odbcQuery = "delete from [DataSet].CustomerCategories where CCCode not in " + ids; odbcQuery = PervasiveSqlUtilities.SetDataSource(odbcQuery, dataPath); PervasiveSqlUtilities.ExecuteSQLCommand(odbcQuery); return(true); } catch (Exception e) { _Context.SystemLogSet.Add(new Data.Log.SystemLog() { EventTime = DateTime.Now, Message = e.Message, StackTrace = e.StackTrace }); } return(false); }
public void UpdateNoneCategory(string sql) { PervasiveSqlUtilities.ExecuteSQLCommand(sql); }