Search code examples
c#visual-studioado.netado

What is the difference of inserting values with just using the Sqlcommand vs. using a data adapter in ADO.NET?


I'm new to ADO.Net and I just came across Data Adapters and DataSets and I just can't wrap my head about their concept yet. What difference does this code:

string firstName = "Jane";
string lastName = "Doe";
string age = 25;
    
string insertString = @"INSERT INTO dbo.Customer (FirstName, LastName, Age)
VALUES (@FirstName, @LastName, @Age)";
    
SqlCommand insertCommand = new SqlCommand(insertString, connection);
    
insertCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar, 100).Value = firstName;
insertCommand.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar, 100).Value = lastName;
insertCommand.Parameters.Add("@Age", System.Data.SqlDbType.Int).Value = age;
    
Console.WriteLine("Record has been added.");
    
connection.Open();
insertCommand.ExecuteNonQuery();

and this code have?

SqlCommand selectCommand = new SqlCommand("SELECT * FROM dbo.Customer", connection);
SqlCommand insertCommand = new SqlCommand("INSERT INTO dbo.Customer (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)", connection);
    
insertCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 30, "FirstName"));
insertCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));
insertCommand.Parameters.Add(new SqlParameter("@Age", SqlDbType.Int, 0, "Age"));
    
SqlDataAdapter dataAdapter = new SqlDataAdapter();
    
dataAdapter.SelectCommand = selectCommand;
dataAdapter.InsertCommand = insertCommand;
    
connection.Open();
    
DataSet dataSet = new DataSet("DataSet");
    
dataAdapter.Fill(dataSet, "Customer");
    
DataRow dataRow = dataSet.Tables[0].NewRow();
dataRow["FirstName"] = "Jane";
dataRow["LastName"] = "Doe";
dataRow["Age"] = 25;
    
dataSet.Tables[0].Rows.Add(dataRow);
    
dataAdapter.Update(dataSet, "Customer");
    
Console.WriteLine("Record has been added.");

Why does the second code use a dataset instead of just inserting the values directly to the database? And what is the more optimal approach in inserting values? The first or the second one?


Solution

  • You are right in that the second approach uses a similar set of SQL commands and also only inserts a single row. This is definitely more effort for the same result.

    However, the whole concept of data sets, data tables and data adapters is much more powerful than displayed in this sample. Data sets (and data tables) can keep a set of data in memory, record the changes to them and persist them later through a data adapter that knows which statements to use for which kind of change.

    It is up to you to decide whether you need the benefits of data sets (or an object-relational-mapper like Entity Framework) in your specific scenario or whether it is sufficient to have a bunch of SQL commands that perform the tasks.

    Please see this link for details on data sets and the key concepts. This link details some factors that are involved on whether to use data sets or simple SQL statements (using a data reader).