SqlDataAdapter

C# ADO.NET Call Stored Procedure to Populate DataSet with SqlDataAdapter

In the previous section we used SqlDataReader for forward‑only, read‑only streaming access. This section covers SqlDataAdapter, which loads stored procedure results all at once into a DataSet (in‑memory offline data table). This is commonly used for direct binding to controls such as DataGridView.
SqlCommand (define stored procedure) → SqlDataAdapter.SelectCommand → adapter.Fill(DataSet) fetches data automatically

Example

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    SqlCommand sqlCom = new SqlCommand();
    sqlCom.Connection = conn;
    sqlCom.CommandText = "myProc";
    // Mark command as stored‑procedure execution
    sqlCom.CommandType = CommandType.StoredProcedure;

    // Supply input parameter for stored procedure
    SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 8);
    param.Value = 4;
    param.Direction = ParameterDirection.Input;
    sqlCom.Parameters.Add(param);

    // Data adapter instance
    SqlDataAdapter sqlDA = new SqlDataAdapter();
    sqlDA.SelectCommand = sqlCom;

    DataSet ds = new DataSet();
    // Fill dataset; second argument sets internal DataTable name
    sqlDA.Fill(ds, "sdfdsf");

    // Bind WinForms grid control
    this.dataGridView1.DataSource = ds.Tables[0];
}Code language: C# (cs)

Better Practice Example

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string procName = "myProc";
    using (SqlCommand sqlCom = new SqlCommand(procName, conn))
    {
        sqlCom.CommandType = CommandType.StoredProcedure;
        // Compact parameter assignment syntax
        sqlCom.Parameters.Add("@id", SqlDbType.Int).Value = 4;

        SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCom);
        DataSet ds = new DataSet();
        // Fill result set, alias table as ArticleResult
        sqlDA.Fill(ds, "ArticleResult");

        // Bind control
        dataGridView1.DataSource = ds.Tables["ArticleResult"];
        // Index‑based access is also valid: ds.Tables[0]
    }
}Code language: JavaScript (javascript)

Breakdown

Behavior of SqlDataAdapter.Fill()

1. Automatic connection handling: If you call Fill and the connection is not Open(), the adapter opens the connection internally and closes it once finished;

The sample explicitly calls conn.Open(). It works, yet it is not mandatory.

2. Fill(ds, "TableName"): Assigns a name to the in‑memory DataTable for named lookup. Omit it and the default name becomes Table.
3. When your stored procedure returns multiple result sets, Fill creates multiple DataTable objects: ds.Tables[0], ds.Tables[1]...

DataSet vs SqlDataReader Key Comparison

Object	Mode	Connection Usage	Typical Use‑Cases
SqlDataReader	Connected streaming read	Holds database connection open	Large datasets, no full caching needed, row‑by‑row processing
DataSet(DataAdapter)	Disconnected in‑memory cache	Releases connection right after Fill completes	UI control binding, small datasets, repeated data reading

Mandatory Setting for Stored‑Procedure Execution

sqlCom.CommandType = CommandType.StoredProcedure;

Without this line, ADO.NET treats myProc as plain SQL text and throws an error.

Control Binding

dataGridView1.DataSource = ds.Tables[0];

The WinForms DataGridView accepts direct DataTable binding and generates columns automatically.
Should you need to persist modified data back to database, pair it with SqlDataAdapter.Update() for batch updates.

Without Explicit Connection.Open()

using(SqlConnection conn=new SqlConnection(connectionString))
using(SqlCommand cmd=new SqlCommand("myProc",conn))
{
    cmd.CommandType=CommandType.StoredProcedure;
    cmd.Parameters.Add("@id",SqlDbType.Int).Value=4;
    SqlDataAdapter da=new SqlDataAdapter(cmd);
    DataSet ds=new DataSet();
    da.Fill(ds,"Article"); // Opens & closes connection automatically
}Code language: JavaScript (javascript)

This snippet leverages using statements for connection resource management

SqlDataAdapter