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

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:

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?

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:

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?

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)

MethodPurposeReturnsUse Case
ExecuteDatasetSELECT queriesDataSetFetch rows, tables
ExecuteNonQueryINSERT, UPDATE, DELETEint (affected rows)Save, update, delete data
ExecuteScalarSingle value queriesobjectCOUNT(), MAX(), ID
ExecuteDataset (SP)Stored procedure resultsDataSetQuery with parameters
ExecuteNonQuery (SP)Stored procedure actionsAffected rowsInsert/update using SP

Conclusion

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

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