obtain random record from datatable

I have created a strongly typed dataset in a class library project.  One of the tables in the dataset contains a series of quotes and the author of the quote.  I use a tableadapter to populate the QuotesDataTable.

What I want to do is obtain a single record from the table randomly and bind the obtained record to a literal control on a user control.

So, my question is two fold.  First, how can I randomly select a record from the datatable?  Second, how can I bind the value to the literal control?

I'm familiar with binding to gridviews, formviews and datalists, but not single controls that do not have built-in databinding.

What would be the best approach to handle this?

See below for the class file code.

Thanks for any help.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Library.DAL;
using Library.DAL.DwayneEppsTableAdapters;
 
namespace Library.BLL
{
    [System.ComponentModel.DataObject]
    public class QuotesBL
    {
        #region Fields...
 
        private Library.DAL.DwayneEppsTableAdapters.QuotesTableAdapter _adapter = null;
 
        #endregion
 
        #region Properties...
 
        public QuotesTableAdapter adapter
        {
            get
            {
                if (_adapter == null)
                {
                    _adapter = new QuotesTableAdapter();
                }
                return _adapter;
            }
        }
 
        #endregion
 
        #region Methods...
 
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public DwayneEpps.QuotesDataTable GetQuotes()
        {
            return adapter.GetQuotes();
        }
 
        #endregion
    }
}
 
 
User control:
 
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="Quotes.ascx.cs" Inherits="controls_Quotes" %>
<asp:Literal ID="Literal1" runat="server"></asp:Literal>
<asp:Literal ID="Literal2" runat="server"></asp:Literal>

Open in new window

-Dman100-Software ConsultantAsked:
Who is Participating?
 
jsbhatiaCommented:
Hello -Dman100-,

I am glad I could be of some help.
This scenario seems Ok. Just check again your random number generation
int randNumber = rnd.Next(1, maxRecords);

in rnd.Next, the first argument is inclusive of the series of numbers it can generate and second option is exclusive. That means, it your maxRecords=5, then
rnd.Next(1, maxRecords) will return either 1 or 2 or 3 or 4. That means you will always be missing 1 record.
Just check what happens when you pass 0 as RANDOMNUMBER and also try passing maxRecords as RandomNumber.

Accordingly change rnd.next to
 rnd.Next(0, maxRecords);   or    rnd.Next(1, maxRecords+1);
0
 
jsbhatiaCommented:
Every data adapter that I know of has an overloaded Fill function  that takes startRecord and maxRecords as arguments, You can use this function to return only one record (maxrecords=1) and use startRecord to give a random number using Random class. To generate the random number you need to have the total number of quotes (records) in the database. you can fetch this at runtime using "count *" query or optimize it as per your design.

In short you will have to modify adapter.GetQuotes() in your library to use Overloaded fill function of your dataadapter.

0
 
-Dman100-Software ConsultantAuthor Commented:
Hi jsbhatia,

Would you have a code example, by chance?  Or, an example somehwere online I could refer to to help me along?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
-Dman100-Software ConsultantAuthor Commented:
As a follow-up...I created another stored procedure that grabs a random record from the database table:

CREATE PROCEDURE [dbo].[spGetRandomQuote]

AS

declare @nRecordCount int

declare @nRandNum int

-- Create a temporary table with the same structure of

-- the table we want to select a random record from

CREATE TABLE #TempTable

(

quote_author varchar(50),

quote_text varchar(255),

idNum int identity(1,1)

)

-- Dump the contents of the table to seach into the

-- temp. table

INSERT INTO #TempTable

Select quote_author, quote_text From quotes

-- Get the number of records in our temp table

Select @nRecordCount = count(*) From #TempTable

-- Select a random number between 1 and the number

-- of records in our table

Select @nRandNum = Round(((@nRecordCount - 2) * Rand() + 1), 0)

-- Select the record from the temp table with the

-- ID equal to the random number selected...

Select quote_author, quote_text From #TempTable

Where idNum = @nRandNum

In my dataset tableadapter, I created a new query and selected the spGetRandomQuote stored procedure returning tabular data, filling a new datatable and returning a new datatable.

I dropped a formview onto the page desinger surface and bound to the formview using my object datasource.

This all works.  The quotes are randomly generated on my page, but is this the best way to do this?  If I understand correctly, anytime the page is refreshed, it has to make another trip to the database to get a new random quote, which does not seem efficient and could be a performance drag.

I already have a datatable with all the records from the quote table.  It seems that I should be able to programmatically select a random record from the existing datatable.  Plus, is using a formview control the appropriate choice to bind this data?  I know literal controls do not have databinding functionality, but is there a way to bind the record data to a label or literal control instead of using a formview control?
0
 
jsbhatiaCommented:
Hi -Dman100-,

This approach has some problems.
1. You are depending upon the  fact that the random number that you generate will be existing in the database as the id of that table. "dNum = @nRandNum". In normal world scenario, this dNum may have some missing numbers(if you delete some of the already added quotes).

2. You will have to get  total number of quotes in the database, everytime the quote is fetched from the database.(Everytime page is refreshed).

Can you please clarify what is Library.DAL.DwayneEppsTableAdapters.QuotesTableAdapter. Are you using an external library or is this your own library and do you have access to the code to modify it. That dataadapter in this library might be using SQLDataAdapter or OLEdbDataAdapter and when you call adapter.GetQuotes(), it must be using that dataadapter.Fill(DataTable) function internally. You can modify that code in that library to use dataAdapter.Fill(DataTable, RANDOM_NUMBER, 1, "DataTable") overloaded function to fetch just one record.

Note every time the page is refreshed, this one query will still have to be made but it will be much faster than getting all the rows. Anyways, I wouldn't recommend saving of  quote table and reusing it for every page refresh, as you will have to store it in the session variable and that would put quite a load on the server when the users increase.

You can optimize my scenario by storing the total number of quotes that are in the database in web.config as global variable, provided the quotes are not going to be changed too often
0
 
-Dman100-Software ConsultantAuthor Commented:
Hi jsbhatia,

Library.DAL.DwayneEppsTableAdapters.QuotesTableAdapter comes from the class library project I created, which I can modify and recompile.

Can you explain how I can modify the default fill method that is created thru the tableadapter wizard so I have the overloaded function you are referring to?  Is this another query that I add or modifying the existing one?

Thanks for the help.
Regards.
0
 
jsbhatiaCommented:
Hi -Dman100-,

I have never used TableAdapter generated through wizard. I have always created all datasets and dataadapters in code manually.

But, I did create a sample now to find out how table adapters work. The wizard doesn't give you any options to use the internal dataadapter's overloaded functions, so I did dig deeper. What you can do now is

1. Open the Table Adapter in design view in studio. Right click on the adapter and select "View Code". This will create a new class where you can add your own methods.


2. public DwayneEpps.QuotesDataTable GetQuotes()
        {
            return adapter.GetQuotes();
        }

In this above function, right click on GetQoutes and select "Go To Definition". Now you will be able to see the code generated by the studio in order to fetch the quotes.

3. create a copy of this function in the class created at Step 1. Remove the virtual keyword and change the method name to GetRandomQuote and add parameter for RandomNumber (int)

4. Now simply modify
 this.Adapter.Fill(dataTable);
to
this.Adapter.Fill(RandomNumber, 1, dataTable);

Now when you compile your binary you will be able to use this function as
 return adapter.GetRandomQuote (RANDOMNUMBER);

0
 
-Dman100-Software ConsultantAuthor Commented:
Thanks jsbhatia, that has helped me a lot.  I have successfully created the new method and bound to a formview on my page.

My next step is to pass in a random number based on the count in my table.  You mentioned that to generate the random number you need to have the total number of quotes (records) in the database. you can fetch this at runtime using "count *" query or optimize it as per your design.  Would you suggest to add another query to the tableadapter that just returns the count of the table?

Right now, I'm just passing a default value of 2, so it is returning the second row each time, but it works.

I'm reviewing the random class to understand how to generated a random number based on the count in my database table.
0
 
-Dman100-Software ConsultantAuthor Commented:
Hi jsbhatia,

I got the random number generated correctly and passed it successfully to my GetRandomQuote method.  Everything works perfectly.

I did use another query in the tableadapter to return the count.  I couldn't figure out how to obtain the count from the existing datatable that was already populated.  So, I made two trips to the database.  Once to populate the datatable and once to get the count.  Is this not recommended?

Either way, In my user control, I bound to a formview and in the code-behind I created a method to return a random number and used the object datasource selecting event handler to set the input parameter to the GetRandomNumber method.  (code below).

Thanks again.  This taught me a lot.
user control:
 
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="Quotes.ascx.cs" Inherits="controls_Quotes" %>
 
 
<asp:FormView ID="FormView1" runat="server" DataKeyNames="quote_id" 
    DataSourceID="ObjectDataSource1">
    <ItemTemplate>
        <asp:Label ID="quote_textLabel" runat="server" Text='<%# Bind("quote_text") %>' />
        <asp:Label ID="quote_authorLabel" runat="server" Text='<%# Bind("quote_author") %>' />       
    </ItemTemplate>
</asp:FormView>
 
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
    OldValuesParameterFormatString="original_{0}" SelectMethod="GetRandomQuote" 
    TypeName="Library.BLL.QuotesBL" onselecting="ObjectDataSource1_Selecting">
    <SelectParameters>
        <asp:Parameter Name="RandomNumber" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>
 
user control code-behind:
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Library.BLL;
using System.Data;
 
public partial class controls_Quotes : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
 
    protected int GetRandomNumber()
    {
        QuotesBL quote = new QuotesBL();
        int maxRecords = quote.GetTotalRecords();
 
        Random rnd = new Random();
        int randNumber = rnd.Next(1, maxRecords);
 
        return randNumber;
    }
 
    protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        e.InputParameters["RandomNumber"] = GetRandomNumber();
    }
}

Open in new window

0
 
-Dman100-Software ConsultantAuthor Commented:
Thanks again...this was a tremendous help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.