1

I have tried to retrieve data from my table on MySql using my C# application. So I applied the usual connection methods in order to connect my c# application to my MySql database and also called to appropriate methods to retrieve the data from the table and then display it on my application. However, I noticed that by just using the following code :

conString = "server=localhost;user id=" + user + ";database=db;password="+pass;
connection = new MySqlConnection(conString);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM users", connection);
adapter.Fill(table);
dataGridView1.DataSource = table;

I can retrieve the data from the table and display, without using the following code:

connection.Open();

what is the purpose to use connection.Open() if I only need the following code to retrieve data? When will I need to use connection.Open() ?

Do I need to use connection.Open() only when I sending information from my application to mysql but when I want to get/retrieve information from MySql then I don't need to use connection.Open(), is this correct?

sayah imad
  • 1,507
  • 3
  • 16
  • 24
JoeyB
  • 123
  • 6
  • 2
    I haven't used this MySql library but I'm sure the `MySqlDataAdapter` is opening the connection for you when you call `adapter.Fill(table)` – Xerillio May 17 '20 at 11:54
  • @Xerillio thank you. So when do you know when to use connection.Open()? Or is it okay to allows call it when you first connect to your database using the first two lines of code? – JoeyB May 17 '20 at 11:59
  • 1
    A DataAdapter derived class always opens the connection itself if finds it closed. And if it opens then it closes as well when finished. So you really don't need to open if you plan to use an adapter. This is not true for the other ADO.NET object like a DbCommand derived class – Steve May 17 '20 at 12:04

1 Answers1

2

Since the intention of calling adapter.Fill(table); is to retrieve data from the database I would highly expect that the Fill method opens the connection if it isn't already.

You would only need to explicitly call Open if you intend to operate on the connection directly instead of through helper classes like MySqlDataAdapter for example. You can of course open it whenever you feel like.

I would, however, suggest you put the connection in a using statement to ensure that it's closed and disposed of when you are done with it:

using (var connection = new MySqlConnection(conString))
{
    DataTable table = new DataTable();
    MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM users", connection);
    adapter.Fill(table);
    dataGridView1.DataSource = table; 
}
// Now you are sure the connection is closed and being properly garbage collected
Xerillio
  • 4,855
  • 1
  • 17
  • 28
  • Is it wrong to keep your connection open for hours/days. Or should you rather only open your connection when needed and then close it? And what's the consequences of leaving your connection open for days/hours – JoeyB May 17 '20 at 12:10
  • In the extreme case you can also totally remove the MySqlConnection object. The DbDataAdapter base class has a constructor that takes directly a connection string. – Steve May 17 '20 at 12:10
  • 1
    Close your connections (with `using(){}` if possible) when you don't need them. They don't cost much to close and reopen because MySql's Connector.NET offers connection pooling by default. – O. Jones May 17 '20 at 12:15
  • 1
    As @O.Jones suggests you should close them when not needed. I can't speak for this particular library but I've seen examples where long-running connections slowly perform worse and worse - this could be due to improperly configured settings but I would still say it's good practice not to keep it open for longer than needed. – Xerillio May 17 '20 at 12:29
  • @O.Jones so far in order to close my connections I have been using connection.Close() will this suffice? – JoeyB May 17 '20 at 12:35
  • @Joey is there a reason you don't want to use the `using` statement? When working with disposable objects such as `MySqlConnection` you should at the very least call `connection.Dispose()` when you're done with it. But with `using` this is taken care of automatically and therefore it's also highly suggested to do it that way – Xerillio May 17 '20 at 12:40
  • @Xerillio the reason is quite stupid :(. I have been using connection.Open() and connection.Close() for a while and have accustomed to it only. I have not use the using statement in this way and I am not so sure how to exactly use it. However I see you mentioned connection.Dispose(). Is this another way of closing the connection like connection.Close()? – JoeyB May 17 '20 at 12:45
  • @Xerillio I also want to add other features to my application like allowing me to update or insert data into the table using my application. I know you have to use the MySqlCommand(query,connection) to do this. But how would I then use the using statement? And what if there's an error. I have been using the try,catch and final statements to first open the connection (connection.Open()), catch to catch any errors and final to close the connection (connection.Close()). – JoeyB May 17 '20 at 12:48
  • @Joey The `using` statement is used exactly as my example. Everything you need the connection for should be inside the curly braces. I would suggest you read up on it, since it's a much safer way of working with disposable objects. Basically the `Dispose` method of any class implementing `IDisposable` is meant to clear up any resources that the object uses - for DB connections this usually also means closing the connection. [See here](https://dev.mysql.com/doc/dev/connector-net/8.0/html/M_MySql_Data_MySqlClient_MySqlConnection_Dispose.htm) – Xerillio May 17 '20 at 12:52
  • Yes, `Close()` works just fine. Still, `using` is cool because it closes the connection even if the program throws an exception and exits a function abnormally. Connections left open use up resources on the database server, and `using` is the lazy programmer's / smart programmer's way to make sure they get closed. `using` calls `Dispose()` for you. Read about unmanaged resources. – O. Jones May 17 '20 at 13:23
  • @Xerillio just for understanding, so every time I want to connect to the DB I should use the using statement. So for example if I have three buttons on the applications. One connects, the other extracts the table data onto my application and the third inserts new data into the DB. So for each button method I should use the using statement shown above and then put my respective code in each using statements for each button? – JoeyB May 17 '20 at 13:24
  • 1
    Exactly so. Get yourself a connection with `using` each time you need one. They're cheap. – O. Jones May 17 '20 at 13:26
  • @O.Jones what do you mean by cheap? – JoeyB May 17 '20 at 13:36
  • By *cheap* I mean fast to run, and using shared scarce resources (on the MySql server) in a way that's not wasteful. – O. Jones May 17 '20 at 15:17
  • @Joey as a rule of thumb: always use `using` when you can. If you have a very specific - good - reason not to, then you can think of a different solution. As Jones says, creating a new connection is a cheap operation so you shouldn't worry about that. I know `using` is a weird construct at first, put it pays off if you learn to use it. – Xerillio May 18 '20 at 16:18
  • @Xerillio thanks. I have been implementing it and as now become second nature. But I still don't understand why it's needed. I keep hearing it cheap and so on. But I have now idea what cheap is been referenced too. – JoeyB May 18 '20 at 16:25
  • @Joey I think [this answer](https://stackoverflow.com/a/3433209/3034273) may shed some light. Every object implementing the `IDisposable` interface usually works with unmanaged resources, so if you don't use `using` you should manually call `Dispose()`. – Xerillio May 18 '20 at 16:34