Efficient Syntax for Filling DataTable from MS Access

Posted on 2011-05-09
Last Modified: 2012-08-13
Hi All,

Just looking for advice on whether the following code could be made more efficient/better?..

Basically it connects to Access DB fills a DataTable and then returns it to the calling code. I haven't included error trapping within this method but rather wrppaing the error tradding around the calling code.


public static DataTable GetUserDetails(string strFirstName, string strLastName)
        string ConnString = GetConnString();
        string SqlString = "Select * From Contacts Where FirstName = ? And LastName = ?";
        OleDbDataAdapter adptUserData = new OleDbDataAdapter();
        DataTable dtUserData = new DataTable();

        using (OleDbConnection conn = new OleDbConnection(ConnString))
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("FirstName", strFirstName);
                cmd.Parameters.AddWithValue("LastName", strLastName);

                adptUserData.SelectCommand = cmd;



	return dtUserData;

Open in new window

Question by:rito1
    LVL 9

    Expert Comment

    If you are going to only read the data, then use DataReader instead of dataset since it will be faster and use memory.

    IF you are going to do some validations on data in the dataset the yours is good to go.
    LVL 1

    Author Comment

    thanks radcaesar,

    The DataReader would be good in this scenario... My only question then is how would I pass the DataReader as a return value?

    Many thanks,

    LVL 9

    Accepted Solution

    Just return your datareader object

    and use while <datareaderObject>.read() methos to navigate it (To get values)

    while (reader.Read())
                            reader[0], reader[1]);
    LVL 1

    Author Closing Comment

    Excellent thank you.

    I have been using SubSonic Project for the past 2 years to build my DAL so my ADO has become very rusty!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now