I was working on a project using my C# Class Generator and realized that I had no way to get a count of the number of records in the table. In the past I have used the DataAdapter class - easy, but not very efficient.

I ran accross a post on one of the discussion forums and decided to build a new function in my class generator appropriately named RecordCount. The function returns an 'int' with the number of records in the table.

• m_dbConnection is the Connection string to your database
• aRepresentative is the name of our table
• Id is unique column used in our table

public int RecordCount() // Get Count of Records
{
   int recCount=0;
   string countCmd = "SELECT Count(Id) AS Total FROM aRepresentative ";
   SqlConnection m_SqlConnection = new SqlConnection(m_dbConnection);
   SqlCommand    m_SqlCommand    = new SqlCommand(countCmd, m_SqlConnection);
   try 
   {
      // Open the Connection ----------------------------
      m_SqlCommand.Connection.Open();
      recCount = (int)m_SqlCommand.ExecuteScalar();
   } // end try
   catch (Exception e)
   {
      throw new Exception("Error in RecordCount() -> " + e.ToString());
   }
   finally
   { 
      m_SqlConnection.Close();
      m_SqlCommand.Dispose();
   }
   return recCount; 
} // end RecordCount

From the VS.Net Help File... Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations necessary to generate the single value using the data returned by a SqlDataReader.

The RecordCount function is now part of the C# Code Generator. In addition, the Add() function now sets the Id column equal to @@Identity and we have also added XML/Binary Serialization.

Top

About DeveloperLand Administrator

Other articles that may interest you