Sunday, October 27, 2013

Randomizing rows – C#

In earlier post we have seen how we can randomize rows using sql server function “NewId()”.
 Now we will explore way of randomizing rows in C# after retrieving them from database.
I am writing a console application to illustrate the concept
My console application contain a class for generating random number, in main program class I have a function to get dataset “GetDataSet” that emulate fetching data from SQL server using ADO.NET .
The Idea is to add one more row into retrieved record set and put random number value there ,then based on this column sorting whole datatable.
   I am using LINQ to achieve it.       
Below is complete code for the same.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet ds = GetDataSet();
             //Copying A Schema of Existing Result Reset and adding one more column to 
            //save randomly generated number
            DataTable Mydt = new DataTable();
            Mydt = ds.Tables[0].Clone();
            Mydt.Columns.Add("Randomizer", typeof(Int32));

            //Generate specfied number of brandom numbers
            var RandomList = RandomProvider.GetRandomSequence(100, ds.Tables[0].Rows.Count).ToList();
           
            //used to keep track of row number in linq query
            int i=0;

            //doing linq operations on datarow collection
             IEnumerable<DataRow> results =(
                                            from
                                                row
                                            in
                                                ds.Tables["ExampleDt"].AsEnumerable()
                                            select
                                                 Mydt.Rows.Add(
                                                                new Object[] {
                                                                                row["Id"].ToString(),
                                                                                row["Name"].ToString(),
                                                                                row["City"].ToString(),
                                                                                row["Company"].ToString(),
                                                                                RandomList[i++]
                                                                             }
                                                               )
                                            ).ToList().OrderBy(x => x.Field<Int32>("Randomizer"));

            //Final Output
             Mydt = results.CopyToDataTable();
        }

       
        public static DataSet GetDataSet()
        {
            DataSet ds = new DataSet();

            DataTable dt = new DataTable("ExampleDt");
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("City", typeof(string));
            dt.Columns.Add("Company", typeof(string));

            DataRow drow1 = dt.NewRow();
            drow1["Id"] = 1;
            drow1["Name"] = "suresh";
            drow1["City"] = "Mumbai";
            drow1["Company"] = "ABC Pvt Ltd.";

            DataRow drow2 = dt.NewRow();
            drow2["Id"] = 2;
            drow2["Name"] = "Ramesh";
            drow2["City"] = "Nagpur";
            drow2["Company"] = "PQR Pvt Ltd.";

            DataRow drow3 = dt.NewRow();
            drow3["Id"] = 3;
            drow3["Name"] = "Jayesh";
            drow3["City"] = "Delhi";
            drow3["Company"] = "LMN Pvt Ltd.";

            DataRow drow4 = dt.NewRow();
            drow4["Id"] = 4;
            drow4["Name"] = "Mahesh";
            drow4["City"] = "Banglore";
            drow4["Company"] = "JKL Pvt Ltd.";


            dt.Rows.Add(drow1);
            dt.Rows.Add(drow2);
            dt.Rows.Add(drow3);
            dt.Rows.Add(drow4);

            ds.Tables.Add(dt);
            return ds;
        }
    }
     public static class RandomProvider
    {
        public static IEnumerable<int>  GetRandomSequence(int maxNumber)
        {
            var random = new Random();
            while (true)
                yield return random.Next(maxNumber);
        }

        public static IEnumerable<int> GetRandomSequence(int maxNumber, int maxCount)
        {
            return GetRandomSequence(maxNumber).Take(maxCount);
        }

    }
}


Randomizing rows - MSSQL server



Today we will explore ,how we can get ramdomized the rowsfrom SQL server
 Common approaches are
         1)        Using Sql Server Query
         2)        Using Programming Language ( C#|VB.net) to randomize row after retrieval
        Either in Sql server or in programming language we can generate a new column which will hold a randomly generated number for each row then we can sort the record set based on this column.

We First Explore the Pure SQL Server Way:
Using Rand Function?
In MSSQL server there is a function called RAND()
SELECT RAND()
This will generate a random number between 0 & 1.Or
SELECT RAND(10)
Here Rand function takes 10 as seed and generate random number less than 10 ,Can It Be Useful to randomize rows.Try Running Command
select RAND(10) as MyRandNum,* from sys.sysobjects

when we execute this command we get one more column in recordset called MyRandNum yet all the values in it are same irrespective of multiple rows
Now lets try without passing seed to Rand function as
select RAND(),* from sys.sysobjects
Here too same behavior.
Using NewId() Function ?
There is new kid on the block called NEWID(),this function generates some sort 
of long random char string,Lets try to see output of below query  
select NEWID(),*from sys.sysobjects  
   Here we observe that the a random string is generated for each row means its value for each row is different.
 Now let us see how much this function can stand, lets add one more random column
select NEWID(),NEWID(),* from sys.sysobjects
here when we run it found out that value of two random columns in each row ,do have 
different values from one another .Hence this function suits our requirement. 
Now can we convert this random character string to a random number With little googling 
and testing provided stuff on my MSSQL I found a trick that 
convert this random char string to a random number,it cast the random string to binary & 
then to number
 select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],* from sys.sysobjects
 Now we got what we need Here is final query 
select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],* from sys.sysobjects
Order by   ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) 
 Or if we don’t inclined to use this random number further
 select * from sys.sysobjects Order by ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))  
 Now we got what we need Here is final query 
 select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],* from sys.sysobjects
 Order by    ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) 
 Output Of above query:   
Reference :
http://www.sql-server-helper.com/tips/generate-random-numbers.aspx