Weighted random selections in SQL Server
UPDATE After testing my code through based on JP’s comments, I’ve realized my implementation was way too naïve and cannot be used for most datasets. For a correct weighted random implementation, see Dems’ answer on StackOverflow. Original (flawed) implementation There are no built-in functions for selecting weighted averages in SQL Server. Fortunately it's a simple task to do so oneself. We'll use this table as an example: CREATE TABLE #tmp
(
Name varchar(64),
Points int
)
INSERT INTO #tmp VALUES ('Mark', 25);
INSERT INTO #tmp VALUES ('Jakob', 12);
INSERT INTO #tmp VALUES ('Peter', 17);
INSERT INTO #tmp VALUES ('Anders', 0);
INSERT INTO #tmp VALUES...
Automatically mapping datatable to objects
I often need to transfer data from my business layer to my presentation layers in a strongly typed way. In this example I'll use the following very struct and corresponding DataTable to represent the data I need to transfer: struct Test
{
public string Name;
public int Value;
}
using (DataTable dt = new DataTable())
{
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Value", typeof(int));
}
Our objective is basically to transfer the DataRows in the DataTable into a List that can be transferred on to the next layer.
The fastest way possible would be doing it manually like so:
List<Test> list = new List<Test>();
foreach (DataRow dr in dt.Rows)
{
Test t = new Test();
t.Name = dr["Name"].ToString();
t.Value =...