Working with database confusion in C#

Question

I am trying to learn about how to work with databases in C# and I have gotten to a part in a tutorial when I have to work with DataSet , SqlDataAdapter and SqlCommandBuilder.This is the code I wrote in the example in the tutorial:

 public void InitData() {
        //instantiate the connection
        conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=\"D:\\Projects IDE\\Visual Studio\\Exercitii\\Console.app\\WindowsFormsApplication1\\WindowsFormsApplication1\\PlanetWrox.mdf\";Integrated Security=True;User Instance=True");

        //1.instantiate a new DataSet
        dsCustomers = new DataSet();

        //2.init SqlDataAdapter with select command and connection
        daCustomers = new SqlDataAdapter("SELECT Id ,Name, SortOrder FROM Genre", conn);

        // 3. fill in insert, update, and delete commands
        SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);

        // 4. fill the dataset
        daCustomers.Fill(dsCustomers, tableName);
    }

    public void btnUpdateClicked(object sender, EventArgs e) {
        // write changes back to DataBase
        daCustomers.Update(dsCustomers, tableName);

    }

There are a couple of things I do not understand here:

The first thing I noticed is that I do not have to open and close the database.From what limited knoledge I have about databases I know that in order to acces the data you have to open a connection to it and after you are done you have to close it.This must happen somewhere behind the scene.Is that righT? If that is so witch method does that?

The second question is regarding the SqlDataAdapter and SqlCommandBuilder.I do not understand what does SqlCommandBuilder does here.Isen't the SqlDataAdapter the one that is executing the sql query?

When you call daCustomers.Fill(..) ADO.NET open and close connection to database for you — Danilo Vulović
– Danilo Vulović, Commented Oct 9, 2012 at 11:28

Tim Schmelter · Accepted Answer · 2012-10-09 11:31:29Z

2

The first thing I noticed is that I do not have to open and close the database.

SqldataAdapter.Fill will open/close the connection for you.

If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

The SqlCommandBuilder generates INSERT, UPDATE, or DELETE statements from the metadata of your provided select-statement. On this way you can call daCustomers.Update and all changes made to the DataSet will automatically be updated in the database.

answered Oct 9, 2012 at 11:31

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MichaelT · Accepted Answer · 2012-10-09 11:29:18Z

1

dsCustomers.Fill opens and closes connection for you. SqlCommandBuilder creates insert, update, and delete based on your select statement.

answered Oct 9, 2012 at 11:29

MichaelT

7,9548 gold badges37 silver badges47 bronze badges

Comments

m4ngl3r · Accepted Answer · 2012-10-09 11:30:45Z

1

ADO.NET handles that task for you, when you Fill your table with data

answered Oct 9, 2012 at 11:30

m4ngl3r

5603 silver badges21 bronze badges

Comments

Rauld · Accepted Answer · 2012-10-09 11:38:54Z

//instantiate the connection
  using (SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=\"D:\\Projects IDE\\Visual Studio\\Exercitii\\Console.app\\WindowsFormsApplication1\\WindowsFormsApplication1\\PlanetWrox.mdf\";Integrated Security=True;User Instance=True"))
  {
    //1.instantiate a new DataSet
    dsCustomers = new DataSet();

    //2.init SqlDataAdapter with select command and connection
    daCustomers = new SqlDataAdapter("SELECT Id ,Name, SortOrder FROM Genre", conn);

    // 3. fill in insert, update, and delete commands
    SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);

    // 4. fill the dataset
    daCustomers.Fill(dsCustomers, tableName);
  }

This would automatically close the connection and Dispose, without you having to bother about it.

James · Accepted Answer · 2012-10-09 11:42:53Z

0

Fill will make the all the resources locked that are involved in the process. Also as per the sql settings it may block other resources too.

If you are using this code for some real time work..Please choose it only if you need this.

answered Oct 9, 2012 at 11:42

James

2,2763 gold badges24 silver badges43 bronze badges

Comments

Erwin · Accepted Answer · 2012-10-09 11:57:38Z

0

For your first thing:

We use conn.open(); to open the connection and conn.close(); to close the connection.

Here in your program you are not connecting to database like to sqlserver. You have provided shown physical path to the file.

See the below for second thing:

SqlCommandBuilder Class

edited Oct 9, 2012 at 11:57

Erwin

4,8113 gold badges34 silver badges42 bronze badges

answered Oct 9, 2012 at 11:53

Ajay Kumar

11 bronze badge

Collectives™ on Stack Overflow

Working with database confusion in C#

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related