Confused between SqlCommand & SqlDataAdapter

Question

everyone I am a student and new to .NET and specially MVC3 development but for one of my project I’ve to work over it and so going through the learning phase Issue and confusion I am facing is regarding DB-Connectivity, whast I leanree d regarding retrieving records from a database is something like this:

//Method One:
var conn = new SqlConnection(conString.ConnectionString);
const string cmdString = "Select * FROM table";
var cmd = new SqlCommand(cmdString, conn); 
var mySqlDataAdapter = new SqlDataAdapter(cmd);
mySqlDataAdapter = new SqlDataAdapter(cmd);
mySqlDataAdapter.Fill(myDataSet, "design");
// making a new SqlCommand object with stringQuery and SqlConnection object THEN a new SqlDataAdapter object with SqlCommand object and THEN filling up the table with the resulting dataset.

But while I was checking out MSDN Library i found out that SqlDataAdapter offers a constructors SqlDataAdapter(String, String) that directly takes a SelectCommand and a connection string to initiate thus skipping the role of SqlCommand in between, like this:

//Method Two:
var conn = new SqlConnection(conString.ConnectionString);
const string cmdString = "Select * FROM table";
var mySqlDataAdapter = new SqlDataAdapter(cmdString, conn);
mySqlDataAdapter.Fill(myDataSet, "design");

Looks short and pretty to me, But I am confused here that if this is possible in this way then why most of the books/Teachers goes by earlier (SqlCommand’s way).

• What’s actually the difference between SqlCommand and SqlDataAdapter?
• Which method is better One or Two?
• Am afraid of I am using a shortcut in method two that could affect security or performance wise?

Apologising in advance if I sound very newbie or blurred! Will appreciate any help that could clear my concepts up! Thankyou! :)

Treasurer

Answer 1

Errorstacks summed it right:

• SqlAdapter is used to fill a dataset.
• SqlCommand can be used for any purpose you have in mind related to Create/Read/Update/Delete operations, stored procedure execution and much more.

In addition:

• SqlCommand CAN have one big advantage against usage of raw strings in regards of security - they CAN protect you from Sql Injections. Just use parameters for values provided by the user instead of string.Format(...).

My personal preference is to wrap ANY sql strings in SqlCommand and add SqlParameters to it in order to avoid Sql Injection by malicious users.
Regarding performance of the two approaches - I don't expect that there is any difference. (If someone can prove me wrong - do it!).
So I would suggest to stick with the longer variant 1 and use commands plus parameters if necessary.

A bit of a side note - Datasets and DataTables are a bit out of game recently due to Linq2Sql and Entity Framework.
But of course the knowledge of plain old SqlCommands/Adapters/Readers is welcome :)

Millenarian

Answer 2

Hurry-up! Turn your attention to LINQ!!!

No more gran'ma stuff like SQLDataset or TableAdapters, no open connection. Everything gets smoother with LINQ.

LINQ sample:

dim result = from emp in myDataContext.Employees where emp.Salary > 10000 Select emp.ID, emp.SurName, ....

myDatagrid.datasource = result.toList

With LINQ, you don't have to worry about single quotes or crlf within your queries...

And you'll even have intellisense on the SQL tables, columns and objects!

Akee

That didn't answer the question. Further, sometimes LINQ isn't available. For instance, it isn't available in version of .NET CE before version 3.5. – Smelly