What is the difference of inserting values with just using the Sqlcommand vs. using a data adapter i-CodePudding

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?

CodePudding user response：

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).

CodePudding user response：

Great question - While the dataset doesn't cache the data, it creates a dataset that is separate from the database allowing further manipulation of the data.

In the article below, there is a reference to cursors. As cursors allow both forward and reverse reads (as opposed to forward only although that condition can be specified), it is useful for conditions such as ticket sales. In a ticket sale, a user can request 10 tickets but at that point, the credit information needs verification. If the result is unverified, the pool remains in a state where the same tickets can be sold to another buyer almost immediately upon failed verification. This assumes the connection is locked or some locking mechanism is in place for the data in the dataset.

Keeping the dataset separate from the database allows for an older ADO term to be used called "Marshalling". Basically, in this case, you marshal using a three point approach to the data. One, you have the data that was extracted at the time of the read, two, you have the current state, and three, you have a marshalling condition that says overwrite data when putting it back, only put back what data has changed but where the original is still the same as the original state (meaning it hasn't been changed) or fail completely and ask the user to decide what to do with the unmatched changes.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

The data reader is a different approach but it explains how the data is kept separately from the database.