What is the exact difference between calling SqlDataAdapter with a SQL query or a SqlCommand?

Question

Code:1

 string Query="Select * from Employee";
 SqlCommand cmd=new SqlCommand(Query,con);
 SqlDataAdapter da=new SqlDataAdapter(cmd);
 da.Fill(ds)//DataSet

Code:2

 string Query="Select * from Employee";
 SqlDataAdapter da=new SqlDataAdapter(Query,con);
 da.Fill(ds)

Anyone please explain the difference between the above two set of codes ?

score 1 · Accepted Answer · answered Sep 17 '18 at 08:38

1

IMO, SqlCommand is inclined towards How to fetch the data while SqlDataAdapter is more inclined towards how to return the Data to C# code (mostly in DataSet & DataTables).

Also, you dont need Data Adapter when using SqlCommand. SqlCommand has ExecuteQuery & ExecuteNonQuery to execute the Sql as is. You can also use the SqlCommand to call StoredProcs on the Database.

answered Sep 17 '18 at 08:38

Prateek Shrivastava

1,877
1
10
17

The SqlCommand also allows you to specify parameters, transaction settings, timeouts etc... – Matthias Auswöger Sep 17 '18 at 08:55
Data adapters aren't about returning data, they are about *modifying* data. They were created only as ways to simplify drag&drop CRUD development using DataTables and DataSets. You can load a DataTable from a DbDataReader directly. The adapter though provides the INSERT/UPDATE/DELETE commands required to persist changes made to a Datatable. – Panagiotis Kanavos Sep 17 '18 at 09:17
@PanagiotisKanavos - Agree with you - DataAdapter helps to Fill up DataSet/DataTables - Which then is (*mostly*) used in Code to allow user to CRUD on. Later the Changes are/can be saved back to Database as the DataAdapter knows how to do so (via the Insert/Update/Delete commands). My response/opinion is to highlight that SqlCommand & SqlDataAdapter are not the same thing (like the accepted answer). – Prateek Shrivastava Sep 17 '18 at 10:04

score 1 · Answer 2 · answered Sep 17 '18 at 08:38

There is none. In the first code block you create a command yourself. See the effect in the constructor call to SqlDataAdapter. It just assigns that command to the SelectCommand:

public SqlDataAdapter(SqlCommand selectCommand) : this() {
    SelectCommand = selectCommand;
}

while in the second code block, you rely on the SqlDataAdapter to create a SqlCommand based on the query and assign it in another constructor:

public SqlDataAdapter(string selectCommandText, SqlConnection selectConnection) : this() {
       SelectCommand = new SqlCommand(selectCommandText, selectConnection);
}

What is the exact difference between calling SqlDataAdapter with a SQL query or a SqlCommand?

2 Answers2