Does instantiating a DataSet object automatically create a connection to a SQL service-based database for CRUD operations?

Question

Here is everything that I did:

1. In a visual studio 2013 C# project, I created a service database (.mdf file). Note: I changed the name from Database1.mdf to fghLocalDB.mdf.

2. I opened this database in the server explorer.

3. I created 2 tables called Country and CarbonDioxide using the table designer.

4. I added an entry to the Country table as shown by the Data Table of the Country table.

5. I did the following to create a DataSet my application can use. I created a Data Source by clicking on the "Project" option on the top menu bar and clicking on the "Add New Data Source ..." option from the drop down.

6. This is what my project files looked like at this point.

7. I wrote the following code in the main method thinking that this would be all I need to write to the database.

   // Create a connection to the DataSet and TableAdapters that will communicate with our 
   // local database to handle CRUD operations.
   
   
   fghLocalDBDataSet dataSet = new fghLocalDBDataSet();
   fghLocalDBDataSetTableAdapters.CountryTableAdapter countryTableAdapter = 
   new fghLocalDBDataSetTableAdapters.CountryTableAdapter();
   
   try
   {
      // Insert a row into Country table. EDIT 1 Will comment after first program run.
      Console.WriteLine(countryTableAdapter.Insert("United States"));
   
      // Actually writeback information to the database?      
      // dataSet.AcceptChanges(); EDIT 2 commented this as LeY suggested it was not needed.
   
   
      // EDIT 3 Validation code as suggested by Ley.
      var dt = new fghLocalDBDataSet.CountryDataTable();
      var adapter = new fghLocalDBDataSetTableAdapters.CountryTableAdapter();
      adapter.Fill(dt);
   
      foreach (var row in dt)
      {
               // This does not get executed after a second run of the program.        
               // Nothing is printed to the screen.
               Console.WriteLine("Id:" + row.Id + "----Name: " + row.Name);
      }
      Console.Read();
   }
   catch(SqlException exception){
      Console.WriteLine("ERROR: " + exception.ToString());
   }
   Console.ReadLine();
   

8. I ran the program and everything seemed fine.

9. I opened the tables by right clicking on these tables in the server explorer and pressing "Show Data Table".
10. The "United States" row was not added as wanted.

11. I think it has to do with the connectionstring. I right clicked on my project and opened properties.

12. Here I made sure the connection string matched that of the local database by looking at the string in the properties of the database. They are the same.

I copied and pasted the actual text for each connection string:

Connection string of project:

Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\fghLocalDB.mdf;Integrated Security=True

Connection string of actual database (.mdf file):

Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf;Integrated Security=True

I am assuming |DataDirectory| is equal to C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf; since in the picture above when I clicked on the button to expand the Value of the connection string the connection properties window opened up and had this path for the database file name.

My question in a nutshell is does instantiating a DataSet object in the code automatically create a connection to a SQL service-based database for CRUD operations?

If not how do I connect my DataSet object to my sql database so that way I can actually write to the database when using the TableAdapters?

I read the following links:

Insert method of TableAdapter not working?

TableAdapter Insert not persisting data

Use connectionstring from web.config in source code file

Do I need an actual SqlConnection object? and how to I connect this to the DataSet & TableAdapters?

Answer 1

I never used tableadpter.insert() method. But I tried it on my local machine, and it works. I can't figure out your problem based on the information you provided, sorry, but I can point you a direction.

If you created everything from wizard, you don't need to worry about the connection, the table Adapters will handle the connection for you. The connection string (you circled) will be added to your app.config file as well as your setting class automaticly. That is how your application (or you) uses it.

var countryTableAdapter = new CountryTableAdapter(); countryTableAdapter.Insert("United States");

This 2 lines of code are enough to insert the row into database if there is no exception thrown, I don't know why it doesn't work for you. Maybe the way you verify it somehow goes wrong, but you can verify it in another way.

The countryTableAdapter.Insert method will return the number of row get affected, in your case , should be one. So put the following code in , and set a breakpoint after it. if the rowAffected == 1, then the insertion works.

var rowAffected = countryTableAdapter.Insert("Test2")

If you need more confirmation , try this.

var dt = new fghLocalDBDataSet.CountryDataTable();
var adapter = new CountryTableAdapter();
adapter.fill(dt);
foreach (var row in dt){
    Console.WriteLine("Id:" + row.Id + "----Name: " + row.Name);
}
Console.Read();

you will see all the records in your table. I hope this will help.

By the way, from your code

dataSet.AcceptChanges();

The line of code above doesn't update the database at all. It only modify your local data storage. it overwrites your dataRow original version using current version and change the current version row state to unchanged.

Only the tableadapters can talk to database (not true I know, but I just want to make a point that Dataset can not talk to database directly). And I usually only need tableadapte.Update method and pass the dataSet or dataTable in with correct RowState.

The tableAdapter.update method will call AcceptChanges on each row eventually if it successfully updated the database.

You should never need to call AcceptChanges explicitly unless you only want update your dataset in memory.

I recommend you to read ADO.NET Architecture to get the big picture how DataSet and TableAdapter worked.

Answer 2

It was my connection string after all. In my original post, I said I had two connection strings:

1. Connection string in project settings:

Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\fghLocalDB.mdf;Integrated Security=True

2. Actual connection string in fghLocalDB.mdf file:

Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf;Integrated Security=True

Something went wrong with

|DataDirectory| = C:\Users\gabriel\Source\Workspaces\Capstone\Sandbox\aduclos\QueryDataMarketConsole\QueryDataMarketConsole\fghLocalDB.mdf;

in my App.config.

My Solution: What I did was copy the actual connection string of the .mdf file from the .mdf properties panel and paste it into the project properties => Settings => Value field of the connection string set up.

Afterwards I ran my code again and sure enough the data persisted in the tables.

I did not need dataSet.AcceptChanges(); as #LeY pointed out. I also did not need a TableAdapter.Update(dataset) call as posted in other solutions. I just needed the TableAdapter.Insert("...") call.

EDIT: ALSO Most importantly to answer my original question, instantiation a DataSet does not create a connection with the local database. Instead instantiating a TableAdapter does establish a connection with the database!