Understanding SqlHelper Class in .NET — ExecuteNonQuery, ExecuteDataset, ExecuteScalar

When working with Microsoft SQL Server in a .NET application, you often repeat the same database tasks:

• Opening SQL connections

• Creating commands

• Executing queries

• Filling DataSets

• Returning values

To avoid writing this boilerplate code again and again, developers create a reusable SQL Helper class.
Your SqlHelper class provides commonly used database execution methods such as:

• ExecuteDataset

• ExecuteNonQuery

• ExecuteScalar

This article explains each method, why it is used, and provides practical, real-time examples.

ExecuteDataset — Fetching Records (SELECT Queries)

Purpose

To execute SELECT queries and return the result as a DataSet.

Why used?

• When you need multiple tables or multiple rows

• When UI needs table-like data (GridView, DataTable, Excel Export etc.)

A. ExecuteDataset (Text Query)

  public DataSet ExecuteDataset(string conStr, string query)
  {
      DataSet ds = new DataSet();

      using (SqlConnection con = new SqlConnection(conStr))
      {
          con.Open();
          using (SqlDataAdapter da = new SqlDataAdapter(query, con))
          {
              da.Fill(ds);
          }
      }

      return ds;
  }

Real-Time Use Case

Example: Fetch IPO Master list from database and show in grid.

Example Code

SqlHelper helper = new SqlHelper();
DataSet ds = SqlHelper.ExecuteDataset(conStr, "SELECT * FROM  Userdetails");

if (ds.Tables[0].Rows.Count > 0)
{
    // Bind to Grid or dropdown
}

B. ExecuteDataset (Stored Procedure)

 public static DataSet ExecuteDataset(string conStr, string proc, SqlParameter[] param, bool isSp)
 {
     DataSet ds = new DataSet();

     using (SqlConnection con = new SqlConnection(conStr))
     {
         con.Open();
         using (SqlCommand cmd = new SqlCommand(proc, con))
         {
             cmd.CommandType = isSp ? CommandType.StoredProcedure : CommandType.Text;
             cmd.Parameters.AddRange(param);

             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
             {
                 da.Fill(ds);
             }
         }
     }

     return ds;
 }

Real-Time Use Case

When your project requires fetching data using a stored procedure with parameters.

Example: Get User details based on ID.

Example Code

SqlParameter[] param =
{
    new SqlParameter("@Id", 25)
};

DataSet ds = SqlHelper.ExecuteDataset(
    conStr,
    "sp_GetDetails",
    param,
    true
);

ExecuteNonQuery — Insert, Update, Delete (No Data Returned)

Purpose

For queries that do not return rows:

• INSERT

• UPDATE

• DELETE

• Stored procedures performing actions

Why used?

To know how many rows were affected.

A. ExecuteNonQuery (Stored Procedure with Parameters)

 public int ExecuteNonQuery(string conStr, string proc, SqlParameter[] param, bool isSp)
 {
     using (SqlConnection con = new SqlConnection(conStr))
     {
         con.Open();
         using (SqlCommand cmd = new SqlCommand(proc, con))
         {
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.AddRange(param);

             return cmd.ExecuteNonQuery();
         }
     }
 }

Real-Time Use Case

Example: Save IPO Application values (Insert).

Example Code

SqlParameter[] param =
{
    new SqlParameter("@AppNo", "12345"),
    new SqlParameter("@InvestorName", "Sandhiya")
};

SqlHelper helper = new SqlHelper();
int rows = helper.ExecuteNonQuery(
    conStr,
    "sp_InsertApplication",
    param,
    true
);

if (rows > 0)
{
    Console.WriteLine("Record Inserted Successfully!");
}

B. ExecuteNonQuery (Static)

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText)
{
    using SqlConnection conn = new SqlConnection(connectionString);
    using SqlCommand cmd = new SqlCommand(cmdText, conn);
    cmd.CommandType = cmdType;
    conn.Open();
    return cmd.ExecuteNonQuery();
}

Real-Time Use Case

When you want a simple command without parameters.

Example

int rows = SqlHelper.ExecuteNonQuery(
    conStr,
    CommandType.Text,
    "DELETE FROM TempTable"
);

ExecuteScalar — Return Single Value

Purpose

To get one single value from the database.

Used When?

• COUNT()

• SUM()

• MAX()

• MIN()

• Get latest inserted ID

Example: Count total User records

 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText)
 {
     using SqlConnection conn = new SqlConnection(connectionString);
     using SqlCommand cmd = new SqlCommand(cmdText, conn);
     cmd.CommandType = cmdType;
     conn.Open();
     return cmd.ExecuteScalar();
 }
object count = SqlHelper.ExecuteScalar(
    conStr,
    CommandType.Text,
    "SELECT COUNT(*) FROM IPOMaster"
);

Console.WriteLine("Total IPO: " + count);

ExecuteDataset (Another Static Version)

  public static DataSet ExecuteDataset(string connectionString, CommandType cmdType, string cmdText)
  {
      using SqlConnection conn = new SqlConnection(connectionString);
      using SqlCommand cmd = new SqlCommand(cmdText, conn);
      cmd.CommandType = cmdType;

      using SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds);
      return ds;
  }

Purpose

Same as other ExecuteDataset but used when the developer doesn’t need parameters.

Example

DataSet ds = SqlHelper.ExecuteDataset(
    conStr,
    CommandType.Text,
    "SELECT * FROM Users"
);

Difference Between All Methods (Quick Table)

Method	Purpose	Returns	Use Case
ExecuteDataset	SELECT queries	DataSet	Fetch rows, tables
ExecuteNonQuery	INSERT, UPDATE, DELETE	int (affected rows)	Save, update, delete data
ExecuteScalar	Single value queries	object	COUNT(), MAX(), ID
ExecuteDataset (SP)	Stored procedure results	DataSet	Query with parameters
ExecuteNonQuery (SP)	Stored procedure actions	Affected rows	Insert/update using SP

Conclusion

Your SqlHelper class simplifies database operations by removing repeated ADO.NET code. Each method serves a specific purpose:

• ExecuteDataset → Fetch records

• ExecuteNonQuery → Insert / Update / Delete

• ExecuteScalar → Return single value

• With / Without parameters

• With Text queries or Stored Procedures

You can reuse this helper throughout your project, making your code clean, readable, and maintainable.