ADO.NET 3.5 Cookbook, 2nd Edition

4.11. Retrieving a Random Sample of Records

Problem

You want to retrieve a result set containing a random sample of records from a result set using SQL Server 2005 or later.

Solution

Use either the TABLESAMPLE T-SQL clause or a custom method. The solution shows how to use both techniques to return a random sample of 10 percent of records from the Person.Contact table in AdventureWorks. In either case, the top 10 sampled rows are returned.

The C# code in Program.cs in the project RetrieveRandomSampleRecords is shown in Example 4-12.

Example 4-12. File: Program.cs for RetrieveRandomSampleRecords solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace RetrieveRandomSampleRecords
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect;
            DataTable dt;
            SqlDataAdapter da;

            // Get the count of records in Person.Contact.
            sqlSelect = "SELECT COUNT(*) FROM Person.Contact";
            int recCount;
            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                SqlCommand command = new SqlCommand(sqlSelect, connection);
                connection.Open();
                recCount = (int)command.ExecuteScalar();
            }
            Console.WriteLine("Person.Contact record count = {0}\n", recCount);

            // Fill the DataTable with Person.Contact data
            sqlSelect = "SELECT * FROM Person.Contact TABLESAMPLE (10 PERCENT)"; dt = new DataTable(); da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.Fill(dt); // Output ...