The same database tasks are frequently repeated while using Microsoft SQL Server in a.NET application:

• Creating SQL connections
• Making commands
• Running queries
• Completing DataSets
• Giving back values

  

Developers make a reusable SQL Helper class to avoid repeatedly writing this boilerplate code.
Commonly used database execution techniques are provided by your SqlHelper class, including:

• Execute Dataset
• Run NonQuery
• Run Scalar

Each technique is described in this article along with its rationale and useful, real-world 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)