Member Avatar for Zinderin

Still learning here...

this is my method to get the record count of a table ... it works, but it seems like a lot of coding for something so simple.

Is there an easier way?

public int GetTableRecCount(string table)
        {
       
            //Set up the connection and adapters...
            OleDbConnection myConn = new OleDbConnection();
            myConn.ConnectionString = strConnStr;
            OleDbCommand sqlCmd = new OleDbCommand("SELECT COUNT(*) FROM " + table);
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
            dbAdapter.SelectCommand = sqlCmd;
            DataSet ds = new DataSet();
            ds.Clear();

            // Open the connection ....
            myConn.Open();
            sqlCmd.Connection = myConn;

            // Get the data and see how many records we got ....
            dbAdapter.Fill(ds, "count");

            string tmpStr = ds.Tables[0].Rows[0][0].ToString();
            // Close the connection and toss it...
            myConn.Close();
            myConn.Dispose();

            return int.Parse(tmpStr);
        }

I'm still a little shakey on the dataset stuff, and retreiving the value to a string, and then parsing it was the only way I could get it to work. When I tied;

return int.Parse(ds.Tables[0].Rows[0][0].ToString());

or

  return ds.Tables[0].Rows[0][0];

... the compiler whined at those two attempts, and the build failed. I'm not sure why, but I'm not stressing over it either.

Just looking for mentoring ... thanks in advance guys.

  • 3 Contributors
  • 4 Replies
  • 494 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Zinderin

Dani AI

Generated

Short answer: yes — you can get the count with far less plumbing. As hinted, avoid filling a DataSet when the query returns one scalar value; as also noted, the command returns a boxed object you must convert. Use a short, well-scoped command and let the provider return the single value. (learn.microsoft.com)

A compact, safe pattern (for ASP.NET WebForms / classic ASP.NET) — validate the table name, build an Access connection string to the file in App_Data, use using so connections are disposed, execute the scalar and handle DBNull:

using System.Data.OleDb;
using System.Text.RegularExpressions;

public int GetCountForTable(string tableName)
{
    if (!Regex.IsMatch(tableName, @"^[A-Za-z0-9_]+$"))
        throw new ArgumentException("Invalid table name", nameof(tableName));

    string dbFile = Server.MapPath("~/App_Data/mydb.accdb"); // or build path differently for non-web apps
    string connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbFile};Persist Security Info=False;";

    using (var conn = new OleDbConnection(connStr))
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = $"SELECT COUNT(*) FROM [{tableName}]";
        conn.Open();
        var raw = cmd.ExecuteScalar();
        if (raw == null || raw is DBNull) return 0;
        return Convert.ToInt32(raw);
    }
}

Use Server.MapPath (or IWebHostEnvironment.ContentRootPath in ASP.NET Core) to resolve the Access file path, and prefer the OLE DB provider for Access (System.Data.OleDb). (learn.microsoft.com)

Notes on connection strings and runtime issues: for .accdb use the ACE provider; for older .mdb use Jet. Examples and the |DataDirectory| substitution are documented (and handy for App_Data). If you see "Provider not registered" install the Access Database Engine and make sure your process bitness (32/64-bit) matches the provider. (connectionstrings.com)

Quick tips: always validate/whitelist table names (you cannot parameterize object names), catch and log exceptions around the open/execute, and prefer Convert.ToInt32 (or Convert.ToInt64 if very large counts) after checking for DBNull. In ASP.NET Core, inject IWebHostEnvironment and use its paths instead of Server.MapPath. (learn.microsoft.com)

Recommended Answers

All 4 Replies

Member Avatar for pritesh2010

can you tell me why are you use dataset. when you fire a count(*) query it ultimatly gives only one row and one column . If youwant this result of the query you can directly execute your command With ExecuteScalar. ExecuteScalar this will give you first row of first column.. foe E.g.

con.Open();
OleDbCommand cmd=new OleDbCommand("SELECT count(*) from table",con);
string ans=cmd.ExecuteScalar();
con.close();

i think you want this.other wise tell us in brief wht you want. :)

Member Avatar for kvprajapati

Correction to 's post.

Return data type of method ExecuteScalar() is object - type cast is needed.

OleDbCommand cmd=new OleDbCommand("SELECT count(*) from table",con);

con.Open();
int count=(int) cmd.ExecuteScalar(); 
con.close();

OR use DataAdapter way:

OleDbDataAdapter adp = new OleDbDataAdapter("select count(*) from " + table, @"put_connection_string_here");

DataTable dt = new DataTable();
adp.Fill(dt);
int count = (int)dt.Rows[0][0];
Console.WriteLine(count);
Member Avatar for Zinderin

ANSWER TO pritesh2010:

Because that's the only way I know how to do it. :) Like I said, I'm still learning and thus why I come to the mountain and ask.

ADATAPOST:

The DataAdapter actually fills the table tho right? So there's a un-needed transfer of data to then count it locally?

It looks to me like the ExecuteScalar() is what you guys are saying I need, so now its off to the books and web to find out what that is.

As always, you guys rock!!!!

Edited by Zinderin because: n/a
Member Avatar for Zinderin

Ok, I need some help on the ExecuteScalar() ... I can't get the SqlConnection to work.

And all the examples I can find on the web keep trying to connect to some remote database. I just want to connect to the access db in that's in the same directory.

It doesn't take the same parameters as the OleDBConnection. :(

I'm probably just getting tired, I've been coding for 17 hours now.

Edited by Zinderin because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.