public static void BulkInsertLeonid(int number, ref List <Statistics> statsList, string connectionString)
{
DataTable insertDataTable = new DataTable();
insertDataTable.Columns.Add("UUID", typeof(SqlGuid));
for (int i = 0; i < number; i++)
{
SqlGuid myGuid = SqlGuidUtil.NewSequentialId();
DataRow row = insertDataTable.NewRow();
row["UUID"] = myGuid;
insertDataTable.Rows.Add(row);
}
Stopwatch stopwatch = new Stopwatch();
Statistics stats = new Statistics();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
stopwatch.Start();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
bulkCopy.BulkCopyTimeout = 3600;
bulkCopy.DestinationTableName =
"[dbo].[SQLGuidUtilTestTable]";
// Write from the source to the destination.
bulkCopy.WriteToServer(insertDataTable);
}
stopwatch.Stop();
SqlCommand getRowCount = new SqlCommand("GetRowcountFromTable", con);
getRowCount.CommandType = CommandType.StoredProcedure;
getRowCount.Parameters.Add(new SqlParameter("@schema_name", "dbo"));
getRowCount.Parameters.Add(new SqlParameter("@table_name", "SQLGuidUtilTestTable"));
SqlParameter pvRowCount = new SqlParameter();
pvRowCount.ParameterName = "@row_count";
pvRowCount.DbType = DbType.Int32;
pvRowCount.Direction = ParameterDirection.Output;
getRowCount.Parameters.Add(pvRowCount);
getRowCount.ExecuteNonQuery();
//int rowCount = int.Parse(getRowCount.Parameters["@row_count"].Value.ToString());
int rowCount = Convert.ToInt32(getRowCount.Parameters["@row_count"].Value);
stats.NumberOfRowsInTable = rowCount;
stats.Operation = System.Reflection.MethodBase.GetCurrentMethod().Name;
stats.DurationInMilliseconds = stopwatch.ElapsedMilliseconds;
statsList.Add(stats);
}
}
public static void InsertLeonid(int number, ref List <Statistics> statsList, string connectionString)
{
Stopwatch stopwatch = new Stopwatch();
Statistics stats = new Statistics();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
stopwatch.Start();
for (int i = 0; i < number; i++)
{
Guid myGuid = SqlGuidUtil.NewSequentialId();
string cmd = string.Format("INSERT INTO SQLGuidUtilTestTable ([UUID]) VALUES ('{0}')", myGuid.ToString());
SqlCommand command = new SqlCommand(cmd, con);
command.ExecuteNonQuery();
cmd = string.Format("INSERT INTO SQLGuidUtilTestTableFKTable (FK) VALUES ('{0}')", myGuid.ToString());
command = new SqlCommand(cmd, con);
command.ExecuteNonQuery();
}
stopwatch.Stop();
SqlCommand getRowCount = new SqlCommand("GetRowcountFromTable", con);
getRowCount.CommandType = CommandType.StoredProcedure;
getRowCount.Parameters.Add(new SqlParameter("@schema_name", "dbo"));
getRowCount.Parameters.Add(new SqlParameter("@table_name", "SQLGuidUtilTestTable"));
SqlParameter pvRowCount = new SqlParameter();
pvRowCount.ParameterName = "@row_count";
pvRowCount.DbType = DbType.Int32;
pvRowCount.Direction = ParameterDirection.Output;
getRowCount.Parameters.Add(pvRowCount);
getRowCount.ExecuteNonQuery();
//int rowCount = int.Parse(getRowCount.Parameters["@row_count"].Value.ToString());
int rowCount = Convert.ToInt32(getRowCount.Parameters["@row_count"].Value);
stats.NumberOfRowsInTable = rowCount;
stats.Operation = System.Reflection.MethodBase.GetCurrentMethod().Name;
stats.DurationInMilliseconds = stopwatch.ElapsedMilliseconds;
statsList.Add(stats);
}
}
public async Task <ActionResult> Create(CategoryViewModel categoryViewModel)
{
if (ModelState.IsValid)
{
var category = new Category
{
Id = SqlGuidUtil.NewSequentialId(),
CategoryName = categoryViewModel.CategoryName,
IsActive = categoryViewModel.IsActive
};
// Then create:
_db.Categories.Add(category);
await _db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
return(View());
}
public async Task <ActionResult> Create(SkillViewModel skillViewModel)
{
if (ModelState.IsValid)
{
var skill = new Skill
{
SkillId = SqlGuidUtil.NewSequentialId(),
SkillName = skillViewModel.SkillName,
IsActive = skillViewModel.IsActive
};
// Then create:
_db.Skills.Add(skill);
await _db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
return(View());
}
public async Task <ActionResult> Create(PostJobViewModel obj, HttpPostedFileBase file)
{
string categorypass = null;
string skillpass = null;
if (!ModelState.IsValid)
{
return(View(obj));
}
var pn = new Job
{
JobDetails = obj.JobDetails,
ExpireDate = obj.ExpireDate,
Id = SqlGuidUtil.NewSequentialId(),
IsNotification = obj.IsNotification,
Lan = obj.Lan,
Lat = obj.Lat,
PostedDate = obj.PostedDate,
Radius = obj.Radius,
JobTitle = obj.JobTitle,
CompanyAddress = obj.CompanyAddress,
CompanyJobId = obj.CompanyJobId,
CompanyName = obj.CompanyName,
MonthExperience = obj.MonthExperience,
Website = obj.Website,
YearExpereince = obj.YearExpereince,
IsApplicableforWholeCity = obj.IsApplicableforWholeCity
};
if (obj.SelectedCategory != null)
{
// Category
foreach (string equipme in obj.SelectedCategory)
{
categorypass = categorypass + "'" + equipme + "',";
var postcategory = new JobCategory();
postcategory.Id = SqlGuidUtil.NewSequentialId();
postcategory.CategoryId = Guid.Parse(equipme);
pn.JobCategories.Add(postcategory);
}
}
if (obj.SelectedSkill != null)
{
// Category
foreach (string skill in obj.SelectedSkill)
{
skillpass = skillpass + "'" + skill + "',";
var postskill = new JobSkill();
postskill.JobSkillId = SqlGuidUtil.NewSequentialId();
postskill.SkillId = Guid.Parse(skill);
pn.JobSkills.Add(postskill);
}
}
if (file != null && file.ContentLength > 0)
{
var ni = new JobImage {
Id = SqlGuidUtil.NewSequentialId()
};
// Initialize variables
string sSavePath;
// Set constant values
sSavePath = "Images/";
if (!Directory.Exists(Server.MapPath("~/" + sSavePath)))
{
// if not created then it will create it.
Directory.CreateDirectory(Server.MapPath("~/" + sSavePath));
}
// Make sure a duplicate file doesn’t exist. If it does, keep on appending an
// incremental numeric until it is unique
var sFilename = Path.GetFileName(file.FileName);
var fileExtension = Path.GetExtension(file.FileName);
var fileAppend = 0;
if (!string.IsNullOrWhiteSpace(sFilename))
{
while (System.IO.File.Exists(Server.MapPath(sSavePath + sFilename)))
{
fileAppend++;
sFilename = Path.GetFileNameWithoutExtension(file.FileName)
+ fileAppend.ToString() + fileExtension;
}
// Save document
file.SaveAs(Server.MapPath("~/" + sSavePath + sFilename));
ni.ImagePath = "/" + sSavePath + sFilename;
pn.JobImages.Add(ni);
}
}
// Then create:
_db.Jobs.Add(pn);
await _db.SaveChangesAsync();
// If is Notification, then only send push notification
if (!pn.IsNotification)
{
return(RedirectToAction("Index"));
}
IScheduler scheduler = StdSchedulerFactory.GetDefaultScheduler();
scheduler.Start();
decimal radiust = Convert.ToDecimal(pn.Radius) * 1000;
TimeSpan span = pn.ExpireDate - pn.PostedDate;
//JobDataMap jdm = new JobDataMap();
if (categorypass != null)
{
categorypass = categorypass.Remove(categorypass.Length - 1);
//jdm.Add("category", categorypass);
}
//jdm.Add("idd", pn.Id);
//jdm.Add("lat", pn.Lat);
//jdm.Add("lng", pn.Lan);
//jdm.Add("title", pn.Title);
//jdm.Add("description", pn.Description);
//jdm.Add("expirationdate", pn.ExpireDate.ToString(CultureInfo.InvariantCulture));
//jdm.Add("radius", radiust.ToString("#.##"));
//jdm.Add("millisecond", span.TotalMilliseconds.ToString(CultureInfo.InvariantCulture));
IJobDetail job = JobBuilder.Create <PushNewsScheduleJob>()
.UsingJobData("category", categorypass)
.UsingJobData("skills", skillpass)
.UsingJobData("idd", pn.Id.ToString())
.UsingJobData("lat", pn.Lat)
.UsingJobData("lng", pn.Lan)
.UsingJobData("title", pn.JobTitle)
.UsingJobData("description", pn.JobDetails)
.UsingJobData("years", pn.YearExpereince)
.UsingJobData("months", pn.MonthExperience)
.UsingJobData("expirationdate", pn.ExpireDate.ToString())
.UsingJobData("radius", radiust.ToString("#.##"))
.UsingJobData("millisecond", span.TotalMilliseconds.ToString())
.Build();
ITrigger trigger = TriggerBuilder.Create()
.StartNow()
.Build();
scheduler.ScheduleJob(job, trigger);
//List<UserRegId> urlist = await _db.UserRegIds.Where(ur => ur.UserId = ).ToListAsync();
//foreach(UserRegId u in urlist)
//{
// PushNewsModel pnm = new PushNewsModel();
// pnm.id = pn.Id.ToString();
// pnm.lat = pn.Lat;
// pnm.lng = pn.Lan;
// pnm.title = pn.Title;
// pnm.description = pn.Description;
// pnm.expirationdate = pn.ExpireDate.ToString();
// decimal radiust = pn.Radius * 1000;
// pnm.radius = radiust.ToString("#.##");
// TimeSpan span = pn.ExpireDate - pn.PostedDate;
// pnm.millisecond = span.TotalMilliseconds.ToString();
// SendNotification(u.RegId,pnm);
//}
return(RedirectToAction("Index"));
}
private static void evalNewSequentialIds()
{
Console.WriteLine($"{nameof(evalNewSequentialIds)}: generating ids");
var range = Enumerable.Range(0, 512);
var winUuidSeqRange = range
//.Select(delay(42))
.Select(x => { if (x % 32 == 0)
{
Thread.Sleep(10);
}
return(x); })
.Select(i => new { i, guid = WinGuidUtil.NewSequentialId() })
.Select(x => (
x.i,
x.guid,
sqlGuid: SqlGuidUtil.ToSqlSequential(x.guid),
sqlByteGuid: new SqlGuid(x.guid.ToByteArray())
)).ToList();
var combSeqGuidRange = range
//.Select(delay(42))
.Select(x => { if (x % 32 == 0)
{
Thread.Sleep(10);
}
return(x); })
.Select(i => new { i, guid = CombGuidUtil.NewSequentialId() })
.Select(x => (
x.i,
x.guid,
sqlGuid: SqlGuidUtil.ToSqlSequential(x.guid),
sqlByteGuid: new SqlGuid(x.guid.ToByteArray())
)).ToList();
var newIdSeqGuidRange = range
//.Select(delay(42))
.Select(x => { if (x % 32 == 0)
{
Thread.Sleep(10);
}
return(x); })
.Select(i => new { i, guid = NewIdGuidUtil.NewSequentialId() })
.Select(x => (
x.i,
x.guid,
sqlGuid: SqlGuidUtil.ToSqlSequential(x.guid),
sqlByteGuid: new SqlGuid(x.guid.ToByteArray())
)).ToList();
Console.WriteLine($"{nameof(evalNewSequentialIds)}: sorting");
Console.WriteLine();
Console.WriteLine("SQL GUID sorting 0f = most significant byte, 00 = least significant byte: 00010203-0405-0607-0908-0f0e0d0c0b0a");
Console.WriteLine("Windows GUID sorting 0f = most significant byte, 00 = least significant byte: 0f0e0d0c-0b0a-0908-0706-050403020100");
Console.WriteLine("!!!But be careful, this has nothing to do with the order in which the bytes are saved in an array (for example when you do guid.ToByteArray())!!!");
Console.WriteLine();
evalSortNewSequentialIds("winUuidSeqRange", winUuidSeqRange);
Console.WriteLine("winUuidSeqRange: Windows GUID and SQL GUID is sorted the same way, but only if it is converted correctly and the GUIDs are only generated on one machine and without reboot of the PC.");
Console.WriteLine("- The format is: t3t2t1t0-t5t4-t7t6-rrrr-m0m1m2m3m4m5.");
Console.WriteLine(" - m0 - m5: MAC-address (m0-m1-m2-m3-m4-m5 = [supplier id]-[individual id])");
Console.WriteLine(" - t7 - t0: most to least significant byte of timestamp (60-bits of 100ns intervals since 15.10.1582) (bits 4-7 of t7 are a version indicator and is 0b0001 for this version)");
Console.WriteLine(" - rrrr: clock sequence (14-bits random initial number if the system cannot guarantee that the time of the system was not changed) (the bits 14-15 are always 0b10 for this version)");
Console.WriteLine(" - If you'd like to dive deeper into this topic you may find the answer to the above '?' in https://tools.ietf.org/html/rfc4122.");
Console.WriteLine();
evalSortNewSequentialIds("combSeqGuidRange", combSeqGuidRange);
Console.WriteLine("combSeqGuidRange:");
Console.WriteLine("- Windows GUID is sorting it completely wrong (because it starts sorting by the random numbers first, and the timestamp is on the least significant position).");
Console.WriteLine("- SQL GUID is sorting it correct if enough time has passed between each generation of a GUID (if the GUIDs are generated very fast, the 6 bytes for the timestamp are equal and a random number decides about the ordering).");
Console.WriteLine("- There may be a different ordering between sqlGuid (with correct conversion from Windows GUID to SQL GUID) and sqlByteGuid (without correct conversion from Windows GUID to SQL GUID).");
Console.WriteLine(" - This happens if the GUIDs are generated very fast and therefore the timestamp is the same.");
Console.WriteLine(" - Then the next two bytes of random bits are sorted equally but afterwards some bytes are swapped.");
Console.WriteLine(" - If the first two random bytes are equal, then the sorting may differ (between sqlGuid and sqlByteGuid) based on the following random bytes.");
Console.WriteLine("- This approach has no effect if the GUIDs are generated on the same machine or on separate machines.");
Console.WriteLine("- The format is: rrrrrrrr-rrrr-rrrr-rrrr-t5t4t3t2t1t0.");
Console.WriteLine(" - rr: random byte");
Console.WriteLine(" - t5 - t0: most to least significant byte of timestamp");
Console.WriteLine();
evalSortNewSequentialIds("newIdSeqGuidRange", newIdSeqGuidRange);
Console.WriteLine("newIdSeqGuidRange:");
Console.WriteLine("- Windows GUID is sorting it completely wrong (because it starts sorting by the MAC-address and the thread-number first, and the timestamp is on the least significant position).");
Console.WriteLine(" - If the GUIDs are generated very fast, there may be a partially correct order, because 6 of the 8 timestamp bytes (which are at the end and are the most significant part of the timestamp) are the some for some time until the change again.");
Console.WriteLine("- SQL GUID is sorting it completely correct (because it starts sorting by the timestamp and afterwards by the mixed number of MAC-address and thread-number).");
Console.WriteLine("- The default format is (without process id): m4m5s1s0-m2m3-m0m1-t1t0-t7t6t5t4t3t2.");
Console.WriteLine(" - m0 - m5: MAC-address (m0-m1-m2-m3-m4-m5 = [supplier id]-[individual id])");
Console.WriteLine(" - s1 - s0: most to least significant byte of sequence counter (16-bits)");
Console.WriteLine(" - t7 - t0: most to least significant byte of timestamp (64-bits)");
Console.WriteLine("- The format with process id is: p0p1s1s0-m2m3-m0m1-t1t0-t7t6t5t4t3t2.");
Console.WriteLine(" - nnnn: thread number");
Console.WriteLine(" - m0 - m5: MAC-address (m0-m1-m2-m3-m4-m5 = [supplier id]-[individual id]) (m4 and m5 are not used)");
Console.WriteLine(" - p1 - p0: most to least significant byte of process id (16-bits)");
Console.WriteLine(" - s1 - s0: most to least significant byte of sequence counter (16-bits)");
Console.WriteLine(" - t7 - t0: most to least significant byte of timestamp (64-bits)");
Console.WriteLine();
}
private static void evalSortingAlgorithms()
{
var range = Enumerable.Range(0, 16);
var byteRange = range.Select(i => {
var bytes = Enumerable.Range(0, 16).Select(_ => (byte)0).ToArray();
bytes[i] = 1;
return(new { i, bytes });
}).ToList();
Console.WriteLine("byteRange:");
foreach (var o in byteRange)
{
Console.WriteLine($"{o.i}: {toString(o.bytes)} ({new Guid(o.bytes)})");
}
var winRange = byteRange.Select(o => new { o.i, guid = new Guid(o.bytes) }).OrderBy(o => o.guid).ToList();
Console.WriteLine("winRange:");
foreach (var o in winRange)
{
Console.WriteLine($"{o.i}: {toString(o.guid.ToByteArray())} ({o.guid})");
}
var sqlRange = byteRange.Select(o => new { o.i, guid = new SqlGuid(o.bytes) }).OrderBy(o => o.guid).ToList();
Console.WriteLine("sqlRange:");
foreach (var o in sqlRange)
{
Console.WriteLine($"{o.i}: {toString(o.guid.ToByteArray())} ({o.guid}) swap = {toString(SqlGuidUtil.ToSqlSequential(o.guid.ToByteArray()))}");
}
var stringRange = byteRange.Select(o => new { o.i, str = toStringGuid(o.bytes) }).OrderBy(o => o.str).ToList();
Console.WriteLine("stringRange:");
foreach (var o in stringRange)
{
Console.WriteLine($"{o.i}: {o.str}");
}
var winStringRange = stringRange.Select(o => new { o.i, guid = new Guid(o.str) }).OrderBy(o => o.guid).ToList();
Console.WriteLine("winStringRange:");
foreach (var o in winStringRange)
{
Console.WriteLine($"{o.i}: {toString(o.guid.ToByteArray())} ({o.guid})");
}
var sqlStringRange = stringRange.Select(o => new { o.i, guid = new SqlGuid(o.str) }).OrderBy(o => o.guid).ToList();
Console.WriteLine("sqlStringRange:");
foreach (var o in sqlStringRange)
{
Console.WriteLine($"{o.i}: {toString(o.guid.ToByteArray())} ({o.guid}) swap = {toString(SqlGuidUtil.ToSqlSequential(o.guid.ToByteArray()))}");
}
}
