Creating a data table from IEnumerable of T and defining column order explicitly in C#

This article shows code how you can create a DataTable from a collection of T (IEnumerable<T>) and defining explicitly the column order. An extension method for this looks like the following:

public static class DataTableExtensions
{


	public static DataTable CreateOrderedDataTable<T>(this IEnumerable<T> data)
	{
		var dataTable = new DataTable();
		var orderedProps = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
		 .OrderBy(prop => GetColumnOrder(prop)).ToList();
		
		foreach (var prop in orderedProps){
			dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
		}
		
		if (data != null)
		{
			dataTable.BeginLoadData();
			var enumerator = data.GetEnumerator();
			while (enumerator.MoveNext()){
			   var item = enumerator.Current;
			   var rowValues = new List<object>();
			   foreach (var prop in orderedProps){
			    rowValues.Add(prop.GetValue(item, null));		   	
			   }
			   dataTable.Rows.Add(rowValues.ToArray());			 			
			}
			dataTable.AcceptChanges();
		}
		return dataTable;
	}

	static int GetColumnOrder(PropertyInfo prop)
	{
		var displayAttribute = prop.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault() as DisplayAttribute;
		int orderKey = displayAttribute?.Order ?? prop.MetadataToken;		
		return orderKey;
	}
	
}

We order first by DisplayAttribute and the Order value, and fallback to property's MetadataToken. This is an integer value that also returns the order the property was declared, in case you want to order just by the way properties are defined. We get the enumerator here and fetch the row one by one. We could use a simple foreach loop here too. Note the use of BeginLoadData and AcceptChanges. Consider the two classes next. One class does not set any explicit order, the other class uses the Display attribute's Order value to define a custom order of columns for the DataTable.

public class Car
{

	public int Id { get; set; }

	public string Make { get; set; }

	public string Model { get; set; }

	public string Color { get; set; }
}


public class CarV2
{
	[Display(Order = 4)]
	public int Id { get; set; }
	
	[Display(Order = 3)]
	public string Make { get; set; }
	
	[Display(Order = 2)]
	public string Model { get; set; }

	[Display(Order = 14)]
	public bool IsElectric { get; set; }

	[Display(Order = -188865)]
	public string Color { get; set; }
	
}

Next, the following little program in Linqpad tests this extension method and displays the datatables resulting with column ordering set.

void Main()
{
	var cars = new List<Car>{
		new Car { Id = 1, Make = "Audi", Model = "A5", Color = "Blue" },
		new Car { Id = 2, Make = "Volvo", Model = "XC60", Color = "Silver" },
		new Car { Id = 3, Make = "Golf", Model = "GTI", Color = "White" },
		new Car { Id = 4, Make = "Audi", Model = "A5", Color = "Blue" },
	};
	var dataTable = cars.CreateOrderedDataTable();
	dataTable.Dump("Cars datatable, data type is: Car");
	
	var carV2s = new List<CarV2>{
		new CarV2 { Id = 1, Make = "Audi", Model = "A5", Color = "Blue" },
		new CarV2 { Id = 2, Make = "Volvo", Model = "XC60", Color = "Silver" },
		new CarV2 { Id = 3, Make = "Golf", Model = "GTI", Color = "White" },
		new CarV2 { Id = 4, Make = "Audi", Model = "A5", Color = "Blue" },
	};	
	var dataTableV2 = carV2s.CreateOrderedDataTable();
	dataTableV2.Dump("Carsv2 datatable, datatype is CarV2");

}

Generating repeated data into variable in SQL Server in T-SQL

Let's see how we can create repeated data into variable of SQL Server in T-SQL. Use the REPLICATE function to create repeated data like this:

DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE('.', 10)
PRINT @myVariable
PRINT len(@myVariable)

In case you want to set the variable to data which is longer than 8000 characters, you must convert the argument to NVARCHAR(MAX).

DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE(CONVERT(NVARCHAR(MAX),'.'), 1024*1024*2)
PRINT len(@myVariable)

Creating random content is also easy in T-SQL:

DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE(CONVERT(NVARCHAR(MAX),REPLACE(NEWID(),'-', '')), 4)
PRINT len(@myVariable)
PRINT @myVariable

NEWID() creates a new guid, and we strip away the '-' letter, giving 32 chars which we replicate above four times. Since we were below 8000 chars, we chould have skipped using convert to nvarchar(max).

Password hashing in .NET

This article will look on different ways to hash a password in .NET. MD5 was developed by Ron Rivest in 1991 and was used a lot in the 90s, but in 2005 it was revealed it contains collisions. MD5 and SHA-1 is not advised to used in sensitive hashing related to security anymore. Instead, a PBKDF or Password Derived Key-derivation function algorithm will be used. A PBKDF2-based method in Rfc2898DeriveBytes will be used. It has been available since .NET 6. Users of Asp.net Core Identity are recommended to use PasswordHasher instead : https://andrewlock.net/exploring-the-asp-net-core-identity-passwordhasher/ An overview of the arithmetic flow of PBKDF2 is shown below. In the diagram, SHA-512 is indicated, but the code shown in this article
uses SHA-256.

First off, to do a MD5 hash we can use the following :

 
 
 static string Md5(string input){
	using (var md5 = MD5.Create()){
		var byteHash = md5.ComputeHash(Encoding.UTF8.GetBytes(input)); 
		var hash = BitConverter.ToString(byteHash).Replace("-", "");
		return hash;
	}
}
 
 

And to test it out we can run the following:

 
 
 void Md5Demo()
{
	string inputPassword = "abc123";
	string md5Hash = Md5(inputPassword);
	Console.WriteLine("MD5 Demonstration in .NET");
	Console.WriteLine("-------------------------");
	Console.WriteLine($"Password to hash: {inputPassword}");
	Console.WriteLine($"MD5 hashed password: {md5Hash}");
	Console.WriteLine();
} 
 
 

MD5 Demonstration in .NET ------------------------- Password to hash: abc123 MD5 hashed password: E99A18C428CB38D5F260853678922E03 The MD5 hash above agrees with the online MD5 hash here: https://www.md5hashgenerator.com/ MD5 method here does not mention any salt, but this could be concatenated with the password to prevent against rainbow table attacks, that is dictionary attacks. Next, to perform PDKDF2 hashing, the code below can be used. Note that this algorithm will be run iteratively to generate a hash value that is increasingly more computationally expensive to calculate the hash of compared to the number of iterations and includes a salt, making it scalable
to be more and more difficult for attacks.

static byte[] _salt = RandomNumberGenerator.GetBytes(32);

static void HashPassword(string passwordToHash, int numberOfRounds)
{
	var sw = Stopwatch.StartNew();
	var hashedPassword = Rfc2898DeriveBytes.Pbkdf2(
		passwordToHash,
		_salt,
		numberOfRounds,
		HashAlgorithmName.SHA256,
		32);
	sw.Stop();

	Console.WriteLine();
	Console.WriteLine("Password to hash : " + passwordToHash);
	Console.WriteLine("Hashed Password : " + Convert.ToBase64String(hashedPassword));
	Console.WriteLine("Iterations < " + numberOfRounds + "> Elapsed Time: " + sw.ElapsedMilliseconds + " ms");
}

The value 32 here is the desired output length of the hash, we can decide how long the hash we get out of the call to the method. We can then test out the Pbkdf2 method using an increasing number of iterations.

 
 
 void RunPbkdf2HashDemo()
{
	const string passwordToHash = "abc123";

	Console.WriteLine("Password Based Key Derivation Function Demonstration in .NET");
	Console.WriteLine("------------------------------------------------------------");
	Console.WriteLine();
	Console.WriteLine("PBKDF2 Hashes using Rfc2898DeriveBytes");
	Console.WriteLine();

	HashPassword(passwordToHash, 1);
	HashPassword(passwordToHash, 10);
	HashPassword(passwordToHash, 100);
	HashPassword(passwordToHash, 1000);
	HashPassword(passwordToHash, 10000);
	HashPassword(passwordToHash, 100000);
	HashPassword(passwordToHash, 1000000);
	HashPassword(passwordToHash, 5000000);
} 
 
 

This gives the following output:

 
 
Password Based Key Derivation Function Demonstration in .NET
------------------------------------------------------------

PBKDF2 Hashes using Rfc2898DeriveBytes

Password to hash : abc123
Hashed Password : eqeul5z7l2dPrOo8WjH/oTt0RYHvlZ2lvk8SUoTjZq4=
Iterations (1) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : wfd8qQobzBPZvdemqrtZczqctFe0JeAkKjU3IJ48cms=
Iterations (10) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : VY45SxzhqjYronha0kt1mQx+JRDVlXj82prX3H7kjII=
Iterations (100) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : B0LfHgRSslG/lWe7hbp4jb8dEqQ/bZwNtxsaqbVBZ2I=
Iterations (1000) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : LAHwpS4bnbO7CQ1r7buYgUTrp10FyaRyeK6hCwGwv20=
Iterations (10000) Elapsed Time: 1 ms

Password to hash : abc123
Hashed Password : WDjyPySpULXtVOVmSR9cYlzAY4LWeJqDBhszKAfIaPc=
Iterations (100000) Elapsed Time: 13 ms

Password to hash : abc123
Hashed Password : sDx6sOrTl2b7cNZGUAecg7YO4Md/g3eAtfQSvh/vxpM=
Iterations (1000000) Elapsed Time: 127 ms

Password to hash : abc123
Hashed Password : ruywLaR0QApOU5bkqE/x2AAhYJzBj5y6D3P3IxlIF2I=
Iterations (5000000) Elapsed Time: 643 ms
 
 

Note that it takes many iterations before the computation takes significant time. Sources / links :

• OWASP recommends using 600 000 iterations when using SHA256 as noted here: https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html#pbkdf2
• Hashing passwords using PBKDF2 are explained by Stephen Haunts here: Hashing Passwords Safely using a Password Based Key Derivation Function (PBKDF2)