//Create some laptop
public async Task <LaptopModels> createLaptop(LaptopModels laptop)
{
_db.LaptopModels.Add(laptop);
await _db.SaveChangesAsync();
//Last input in database
var lastInput = await(from k in _db.LaptopModels
select k)
.OrderByDescending(k => k.ID)
.FirstOrDefaultAsync();
//Get new full price
lastInput.FullPrice = await(from k in _db.LaptopModels where k.ID == lastInput.ID select k.Price * k.Quantity).FirstAsync();
//Get new saving
lastInput.Savings = await(from k in _db.LaptopModels where k.ID == lastInput.ID select k.OldPrice - k.Price).FirstAsync();
lastInput.Date = DateTime.Now;
await _db.SaveChangesAsync();
//Create new log
LogModels log = new LogModels
{
Type = "0",
Description = "New laptop was inserted with name " + lastInput.Name + " on date " + lastInput.Date + " with quantity of " + lastInput.Quantity + ".",
Date = lastInput.Date
};
_db.LogModels.Add(log);
await _db.SaveChangesAsync();
return(lastInput);
}
public ActionResult Delete()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
using (var dbNoSQL = new LiteDatabase(_connestionString))
{
var companyCollection = dbNoSQL.GetCollection <CompanyModels>("company");
companyCollection.DeleteMany(_ => true);
}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-LiteDB";
logs.OperationTime = timeLog;
logs.OperationName = "DELETE";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = true;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Select()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
var result = companyCollection.Find(f => true);
dbSQL.SaveChanges();
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-MongoDB";
logs.OperationTime = timeLog;
logs.OperationName = "SELECT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Update()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
var update = Builders <CompanyModels> .Update.Set(s => s.Country, "Niemcy");
companyCollection.UpdateMany(_ => true, update);
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-MongoDB";
logs.OperationTime = timeLog;
logs.OperationName = "UPDATE";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Update()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
int numberOfDocumentsPerSession = 10000;
using (var documentStore = new DocumentStore
{
Urls = new[] { "http://localhost:8080/" },
Database = "appBDO"
})
{
documentStore.Initialize();
{
var sessionCount = documentStore.OpenSession();
var count = sessionCount.Query <CompanyModels>().Count();
for (int i = 0; i < count; i += numberOfDocumentsPerSession)
{
using (var session = documentStore.OpenSession())
{
session.Advanced.MaxNumberOfRequestsPerSession = 1000;
var skipCollection = session.Query <CompanyModels>().Skip(i).Take(numberOfDocumentsPerSession);
foreach (var item in skipCollection)
{
item.Country = "Niemcy";
}
session.SaveChanges();
}
}
}
}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-RavenDB";
logs.OperationTime = timeLog;
logs.OperationName = "UPDATE";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
private StringBuilder PrepareDataJsonForListLog(LogModels model)
{
StringBuilder sbResult = new StringBuilder();
try
{
sbResult.Append("{");
sbResult.Append("\"col_class\":\"rows-box\",");
sbResult.Append("\"col_id\":\"" + "323" + "\",");
sbResult.Append("\"col_value\":[");
#region Data cell
//stt
sbResult.Append("{");
sbResult.Append("\"colspan\":\"1\",");
sbResult.Append("\"col_class\":\"ovh col1 stt\",");
sbResult.Append("\"col_id\":\"6\",");
sbResult.Append("\"col_value\":\"" + model.stt + "\"");
sbResult.Append("},");
//noi dung
sbResult.Append("{");
sbResult.Append("\"colspan\":\"1\",");
sbResult.Append("\"col_class\":\"ovh col2\",");
sbResult.Append("\"col_id\":\"6\",");
sbResult.Append("\"title\":\"" + "23" + "\",");
sbResult.Append("\"col_value\":\"" + model.NoiDung + "\"");
sbResult.Append("},");
//cap do
sbResult.Append("{");
sbResult.Append("\"colspan\":\"1\",");
sbResult.Append("\"col_class\":\"ovh col3\",");
sbResult.Append("\"col_id\":\"6\",");
sbResult.Append("\"title\":\"" + "323" + "\",");
sbResult.Append("\"col_value\":\"" + model.CapDo + "\"");
sbResult.Append("},");
//ngay nhap
sbResult.Append("{");
sbResult.Append("\"colspan\":\"1\",");
sbResult.Append("\"col_class\":\"ovh col4\",");
sbResult.Append("\"col_id\":\"6\",");
sbResult.Append("\"col_value\":\"" + model.NgayTao + "\"");
sbResult.Append("}");
#endregion
sbResult.Append("]");
sbResult.Append("},");
}
catch (Exception ex)
{
_logger.Error(ex);
}
return(sbResult);
}
//Create new log
public async Task <string> log7(FormCollection form, int userID, string adminUserIDUsername)
{
//Get info from from
string access = form["item.Access"];
string laptopAccess = form["item.LaptopAccess"];
string logAccess = form["item.LogAccess"];
string searchAccess = form["item.SearchAccess"];
string storeAccess = form["item.StoreAccess"];
string transferAccess = form["item.TransferAccess"];
string taskAccess = form["item.TaskAccess"];
string supplierAccess = form["item.SupplierAccess"];
string procurementAccess = form["item.ProcurementAccess"];
//Check if true or false for every of them and save changes
var acc = await adminUser(userID);
bool a = access == "true,false" ? acc.Access = Convert.ToBoolean("true") : acc.Access = Convert.ToBoolean("false");
bool b = laptopAccess == "true,false" ? acc.LaptopAccess = Convert.ToBoolean("true") : acc.LaptopAccess = Convert.ToBoolean("false");
bool c = logAccess == "true,false" ? acc.LogAccess = Convert.ToBoolean("true") : acc.LogAccess = Convert.ToBoolean("false");
bool d = searchAccess == "true,false" ? acc.SearchAccess = Convert.ToBoolean("true") : acc.SearchAccess = Convert.ToBoolean("false");
bool e = storeAccess == "true,false" ? acc.StoreAccess = Convert.ToBoolean("true") : acc.StoreAccess = Convert.ToBoolean("false");
bool f = transferAccess == "true,false" ? acc.TransferAccess = Convert.ToBoolean("true") : acc.TransferAccess = Convert.ToBoolean("false");
bool g = taskAccess == "true,false" ? acc.TaskAccess = Convert.ToBoolean("true") : acc.TaskAccess = Convert.ToBoolean("false");
bool h = supplierAccess == "true,false" ? acc.SupplierAccess = Convert.ToBoolean("true") : acc.SupplierAccess = Convert.ToBoolean("false");
bool j = procurementAccess == "true,false" ? acc.ProcurementAccess = Convert.ToBoolean("true") : acc.ProcurementAccess = Convert.ToBoolean("false");
await _db.SaveChangesAsync();
string[] userAccess = { "Admin access", "Laptop access", " Log access", "Search access", "Store access", "Transfer access", "Task access", "Supplier access", "Procurement access" };
string[] rights = { Convert.ToString(a), Convert.ToString(b), Convert.ToString(c), Convert.ToString(d), Convert.ToString(e),
Convert.ToString(f), Convert.ToString(g), Convert.ToString(h), Convert.ToString(j) };
// Create log for changes
for (int i = 0; i < userAccess.Length; i++)
{
LogModels log = new LogModels
{
Type = "7",
Description = "New change was made for user " + adminUserIDUsername + " on date " + DateTime.Now + " granting access:" + rights[i] + " for " + userAccess[i],
Date = DateTime.Now
};
_db.LogModels.Add(log);
await _db.SaveChangesAsync();
}
return("Done");
}
public ActionResult Update()
{
var update = Builders <CompanyModels> .Update.Set(s => s.Country, "Niemcy");
int numberOfDocumentsPerSession = 10000;
var timerSQL = new Stopwatch();
timerSQL.Start();
using (var dbNoSQL = new LiteDatabase(_connestionString))
{
var companyCollection = dbNoSQL.GetCollection <CompanyModels>("company");
var selectCompany = companyCollection.Find(f => true);
var selectCompanyCount = companyCollection.Find(f => true).Count();
for (int i = 0; i < selectCompanyCount; i += numberOfDocumentsPerSession)
{
var selectSkip = selectCompany.Skip(i).Take(numberOfDocumentsPerSession);
foreach (var item in selectSkip)
{
item.Country = "Niemcy";
companyCollection.Update(item);
}
}
}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-LiteDB";
logs.OperationTime = timeLog;
logs.OperationName = "UPDATE";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Index()
{
try
{
MyClinic.Infrastructure.Log log = new MyClinic.Infrastructure.Log();
var objSession = Session["user"] as MyClinic.Infrastructure.SessUser;
var idLogUser = objSession.UserId;
string searchBy = "";
string keyword = "";
var orderBy = Common.defaultOrderBy;
var order = Common.defaultListOrder;
var _pageNo = 1;
var _pageSize = 10;
var _pageStatus = 1;
var totalRecords = 0;
//var logs = logRepository.Search(searchBy, keyword, orderBy, order, _pageNo, _pageSize, out totalRecords);
var logs = logRepository.SearchInLogUser(searchBy, keyword, orderBy, order, _pageNo, _pageSize, out totalRecords, idLogUser);
var listResult = Paging.GetResultInfo(totalRecords, _pageNo, _pageSize);
var paging = Paging.GetPaging(totalRecords, _pageNo, _pageSize, _pageStatus, Common.defaultNoOfPageLinkList, "$managelog.pagingManageRecords", orderBy, order);
var itemPerPage = Paging.getItemPerPage(totalRecords, _pageSize, orderBy, order, "$managelog.itemPerPageChangeLogByDate");
var lstProcessTypes = auditLogRepository.GetProcessType();
var lstUsers = userRepository.Get();
string cboPro = "";
string startDate = "";
string endDate = "";
PageUtilities pageUtilities = new PageUtilities()
{
listHeader = listResult,
listFooter = paging + itemPerPage,
order = order,
orderBy = orderBy,
};
logModels = new LogModels
{
logRecords = logs,
pageUtilities = pageUtilities,
lstProcessTypes = lstProcessTypes,
lstUsers = lstUsers,
startDate = startDate,
endDate = endDate,
cboPro = cboPro,
};
}
catch (Exception ex)
{
log.Error(ex);
ModelState.AddModelError("error", Translator.UnexpectedError);
}
return(View(logModels));
}
public async Task <LogModels> log(string ResultName, DateTime?ResultDate, string ResultLocation)
{
LogModels log = new LogModels
{
Type = "1",
Description = "New store was inserted with name " + ResultName + " on date " + ResultDate + " with location on " + ResultLocation + ".",
Date = ResultDate
};
_db.LogModels.Add(log);
await _db.SaveChangesAsync();
return(log);
}
//Logs
public async Task <LogModels> log6(string userUsername, object tempdataCurrentRole)
{
LogModels log = new LogModels
{
Type = "6",
Description = "New change was made for user " + userUsername + " on date " + DateTime.Now + " for role " + tempdataCurrentRole + ".",
Date = DateTime.Now
};
_db.LogModels.Add(log);
await _db.SaveChangesAsync();
return(log);
}
public ActionResult Select()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
using (var documentStore = new DocumentStore
{
Urls = new[] { "http://localhost:8080/" },
Database = "appBDO"
})
{
documentStore.Initialize();
{
using (var session = documentStore.OpenSession())
{
var result = session.Query <CompanyModels>();
foreach (var item in result)
{
Console.WriteLine(item);
}
}
}
}
dbSQL.SaveChanges();
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-RavenDB";
logs.OperationTime = timeLog;
logs.OperationName = "SELECT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Insert()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
var collectionCompanyFromFile = DeSerializeObject <List <CompanyModels> >("SerializationOverview");
using (var dbNoSQL = new LiteDatabase(_connestionString))
{
var companyCollection = dbNoSQL.GetCollection <CompanyModels>("company");
// BULKING
//companyCollection.InsertBulk(collectionCompanyFromFile);
// WITHOUT BULKING
foreach (var item in collectionCompanyFromFile)
{
companyCollection.Insert(item);
}
}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-LiteDB";
logs.OperationTime = timeLog;
logs.OperationName = "INSERT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
//Create transfer
public async Task <TransferModels> createTransfer(FormCollection form, TransferModels transfer, TransferRepository transferRepository)
{
int storeID = Convert.ToInt32(form["StoreName"].ToString());
int LaptopID = Convert.ToInt32(form["LaptopName"].ToString());
int LaptopQuantity = Convert.ToInt32(form["LaptopQuantity"].ToString());
if (await transferRepository.possibleCount(LaptopID) > 0)
{
transfer.StoreID = storeID;
transfer.LaptopID = LaptopID;
transfer.LaptopName = await transferRepository.laptop(LaptopID);
transfer.LaptopQuantity = LaptopQuantity;
transfer.Date = DateTime.Now;
_db.TransferModels.Add(transfer);
await _db.SaveChangesAsync();
//get Laptop
var laptop = await transferRepository.storeFind(storeID);
laptop.QoP -= LaptopQuantity; // reduce QoP for quantity number
// transferRepository.SaveData();
await _db.SaveChangesAsync();
var laptopFind = await transferRepository.laptopFind(transfer.LaptopID);
laptopFind.Quantity -= LaptopQuantity; // reduce LaptopQuantity from Quantity
// transferRepository.laptopFind(transfer.LaptopID).Quantity -= LaptopQuantity;
await _db.SaveChangesAsync();
LogModels log = new LogModels
{
Type = "2",
Description = "New transfer was inserted with transfer of laptop called " + transfer.LaptopName + " with quantity of " +
transfer.LaptopQuantity + " on date " + DateTime.Now + " with location to " + laptop.Name + ", " + laptop.Location + ".",
Date = DateTime.Now
};
_db.LogModels.Add(log);
await _db.SaveChangesAsync();
}
return(transfer);
}
protected void AddLog()
{
LogModels log = new LogModels();
log.ClientId = "";
log.ClientIp = ClientHelper.ClientIp;
log.CreateDate = DateTime.Now;
log.ReferUrl = "";
log.TimeEnd = DateTime.Now;
log.TimeSpan = 0;
log.TimeStart = DateTime.Now;
log.Url = m_context.Request.Path.Value;
log.UserAgent = ClientHelper.UseAgent;
log.UserId = 0;
log.WinHeight = 0;
log.WinWidth = 0;
_logService.AddLogModels(log);
}
public ActionResult Select()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
using (var dbNoSQL = new LiteDatabase(_connestionString))
{
var companyCollection = dbNoSQL.GetCollection <CompanyModels>("company");
var result = companyCollection.Find(f => true);
foreach (var item in result)
{
Console.WriteLine(item);
}
}
dbSQL.SaveChanges();
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-LiteDB";
logs.OperationTime = timeLog;
logs.OperationName = "SELECT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Select()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
using (MySqlConnection sqlConnection = new MySqlConnection(_connectionString))
{
sqlConnection.Open();
string sqlSelect = "SELECT * FROM Company";
MySqlCommand mySqlCmd = new MySqlCommand(sqlSelect, sqlConnection);
var result = mySqlCmd.ExecuteReader();
foreach (var item in result)
{
Console.WriteLine(item);
}
}
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "SQL-MySQL";
logs.OperationTime = timeLog;
logs.OperationName = "SELECT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = true;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
//private List<CompanyModels> GetMySqlColumnMapping(DataTable dataTable)
//{
// List<CompanyModels> colMappings = new List<CompanyModels>();
// int i = 0;
// foreach (DataColumn col in dataTable.Columns)
// {
// colMappings.Add(new CompanyModels(i, col.ColumnName));
// i++;
// }
// return colMappings;
//}
public ActionResult Update()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
using (MySqlConnection sqlConnection = new MySqlConnection(_connectionString))
{
sqlConnection.Open();
string sqlUpdate = "UPDATE Company SET Country = 'Niemcy'";
MySqlCommand mySqlCmd = new MySqlCommand(sqlUpdate, sqlConnection);
mySqlCmd.ExecuteReader();
}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "SQL-MySQL";
logs.OperationTime = timeLog;
logs.OperationName = "UPDATE";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = true;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Insert()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
var collectionCompanyFromFile = DeSerializeObject <List <CompanyModels> >("SerializationOverview");
// BULKING
DataTable tbl = new DataTable();
tbl.Columns.Add(new DataColumn("Id", typeof(string)));
tbl.Columns.Add(new DataColumn("CompanyId", typeof(string)));
tbl.Columns.Add(new DataColumn("RegistrationNumber", typeof(string)));
tbl.Columns.Add(new DataColumn("Name", typeof(string)));
tbl.Columns.Add(new DataColumn("NIP", typeof(string)));
tbl.Columns.Add(new DataColumn("Pesel", typeof(string)));
tbl.Columns.Add(new DataColumn("Country", typeof(string)));
tbl.Columns.Add(new DataColumn("Address", typeof(string)));
tbl.Columns.Add(new DataColumn("PostalCode", typeof(string)));
tbl.Columns.Add(new DataColumn("Teryt", typeof(string)));
foreach (var item in collectionCompanyFromFile)
{
DataRow row = tbl.NewRow();
row["Id"] = item.Id;
row["CompanyId"] = item.CompanyId;
row["RegistrationNumber"] = item.RegistrationNumber;
row["Name"] = item.Name;
row["NIP"] = item.NIP;
row["Pesel"] = item.Pesel;
row["Country"] = item.Country;
row["Address"] = item.Address;
row["PostalCode"] = item.PostalCode;
row["Teryt"] = item.Teryt;
tbl.Rows.Add(row);
}
using (var sqlConnection = new MySqlConnection(_connectionString))
{
sqlConnection.Open();
using (MySqlTransaction tran = sqlConnection.BeginTransaction(IsolationLevel.Serializable))
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = sqlConnection;
cmd.Transaction = tran;
cmd.CommandText = "SELECT * FROM Company";
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
da.UpdateBatchSize = 1000;
using (MySqlCommandBuilder cb = new MySqlCommandBuilder(da))
{
da.Update(tbl);
tran.Commit();
}
}
}
}
}
// WITHOUT BULKING
//using (MySqlConnection sqlConnection = new MySqlConnection(_connectionString))
//{
// sqlConnection.Open();
// foreach (var item in collectionCompanyFromFile)
// {
// string sqlInsert = "INSERT INTO Company (Id,CompanyId,RegistrationNumber,Name,NIP,Pesel,Country,Address,PostalCode,Teryt) " +
// "VALUES ('" + item.Id + "','" + item.CompanyId + "','" + item.RegistrationNumber + "','" + item.Name + "','" + item.NIP + "','" + item.Pesel + "','" + item.Country + "','" + item.Address + "','" + item.PostalCode + "','" + item.Teryt + "')";
// MySqlCommand mySqlCmd = new MySqlCommand(sqlInsert, sqlConnection);
// mySqlCmd.ExecuteNonQuery();
// }
//}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "SQL-MySQL";
logs.OperationTime = timeLog;
logs.OperationName = "INSERT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = true;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Insert()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
var collectionCompanyFromFile = DeSerializeObject <List <CompanyModels> >("SerializationOverview");
int numberOfDocumentsPerSession = 10000;
List <CompanyModels> objectListInChunks = new List <CompanyModels>();
using (var documentStore = new DocumentStore
{
Urls = new[] { "http://localhost:8080/" },
Database = "appBDO"
})
{
documentStore.Initialize();
// BULKING
//using (BulkInsertOperation bulkInsert = documentStore.BulkInsert())
//{
// foreach (var item in collectionCompanyFromFile)
// {
// bulkInsert.Store(new CompanyModels
// {
// Id = item.Id,
// Country = item.Country,
// Address = item.Address,
// NIP = item.NIP,
// CompanyId = item.CompanyId,
// Name = item.Name,
// Pesel = item.Pesel,
// PostalCode = item.PostalCode,
// RegistrationNumber = item.RegistrationNumber,
// Teryt = item.Teryt
// });
// }
//}
// WITHOUT BULKING
for (int i = 0; i < collectionCompanyFromFile.Count; i += numberOfDocumentsPerSession)
{
using (var session = documentStore.OpenSession())
{
session.Advanced.MaxNumberOfRequestsPerSession = 1000;
var skipCollection = collectionCompanyFromFile.Skip(i).Take(numberOfDocumentsPerSession);
skipCollection.ForEach(x => session.Store(x));
session.SaveChanges();
session.Advanced.Clear();
}
}
}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "NoSQL-RavenDB";
logs.OperationTime = timeLog;
logs.OperationName = "INSERT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
public void AddLogModels(LogModels log)
{
_logModelsInterface.Insert(log);
}
public ActionResult Insert()
{
var timerSQL = new Stopwatch();
timerSQL.Start();
var collectionCompanyFromFile = DeSerializeObject <List <CompanyModels> >("SerializationOverview");
// BULKING
using (SQLiteConnection sqlConnection = new SQLiteConnection(_connectionString))
{
sqlConnection.Open();
using (var sqlCmd = new SQLiteCommand(sqlConnection))
{
using (var transaction = sqlConnection.BeginTransaction())
{
foreach (var item in collectionCompanyFromFile)
{
sqlCmd.CommandText =
"INSERT INTO Company (Id,CompanyId,RegistrationNumber,Name,NIP,Pesel,Country,Address,PostalCode,Teryt) " +
"VALUES ('" + item.Id + "','" + item.CompanyId + "','" + item.RegistrationNumber +
"','" +
item.Name + "','" + item.NIP + "','" + item.Pesel + "','" + item.Country + "','" +
item.Address + "','" + item.PostalCode + "','" + item.Teryt + "')";
sqlCmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
// WITHOUT BULKING
using (SQLiteConnection sqlConnection = new SQLiteConnection(_connectionString))
{
sqlConnection.Open();
foreach (var item in collectionCompanyFromFile)
{
string query = "INSERT INTO Company (Id,CompanyId,RegistrationNumber,Name,NIP,Pesel,Country,Address,PostalCode,Teryt) " +
"VALUES ('" + item.Id + "','" + item.CompanyId + "','" + item.RegistrationNumber + "','" + item.Name + "','" + item.NIP + "','" + item.Pesel + "','" + item.Country + "','" + item.Address + "','" + item.PostalCode + "','" + item.Teryt + "')";
SQLiteCommand sqlCmd = new SQLiteCommand(query, sqlConnection);
sqlCmd.ExecuteNonQuery();
}
}
timerSQL.Stop();
TimeSpan timeTaken = timerSQL.Elapsed;
var timeLog = timeTaken.ToString();
var logs = new LogModels();
logs.OperationDate = DateTime.Now;
logs.Database = "SQL-SQLite";
logs.OperationTime = timeLog;
logs.OperationName = "INSERT";
logs.NameAPI = "SearchCompany";
logs.NumberOfRecords = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfRecords;
logs.NumberOfFieldsModel = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").NumberOfFieldsModel;
logs.SizeFile = dbSQL.LogModels.FirstOrDefault(m => m.OperationName == "LOAD").SizeFile;
logs.EntityFramework = false;
logs.BulkLoading = false;
logs.NoTracing = false;
dbSQL.LogModels.Add(logs);
dbSQL.SaveChanges();
return(RedirectToAction("Index"));
}
