• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

Reading Excel Spreadsheet using ADO.NET and DbDataReader

Hi,

Based on the code below, is there a way to get a total count of records that exist in the Excel worksheet (Cities)?

Thanks very much in advance!
string connectionString = @"Provider=Microsoft.Jet.    OLEDB.4.0;Data Source=Book1.xls;Extended    Properties=""Excel 8.0;HDR=YES;""";
 
DbProviderFactory factory =  DbProviderFactories.GetFactory("System.Data.OleDb");
 
using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;
 
    using (DbCommand command = connection.CreateCommand())
    {
        // Cities$ comes from the name of the worksheet
        command.CommandText = "SELECT ID,City,State FROM [Cities$]";
 
        connection.Open();
 
        using (DbDataReader dr = command.ExecuteReader())
        {
            while (dr.Read())
            {
                Debug.WriteLine(dr["ID"].ToString());
            }
        }
    }
}

Open in new window

0
WebAppDeveloper
Asked:
WebAppDeveloper
  • 4
1 Solution
 
tcs224694Commented:
Hi,

This query will get the count of the records...
SELECT count(*) FROM [Cities$];

Open in new window

0
 
WebAppDeveloperAuthor Commented:
tcs224694,

Also, how do I dynamically get a row number for each record of the Excel?
0
 
tcs224694Commented:
Hi ,

Try this...

select (select SUM(1) from Cities) as 'Row Number', * from Cities;

Open in new window

0
 
tcs224694Commented:
0
 
tcs224694Commented:
Thanks for the grade and points
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now