Menu
I am receiving data through a com port continuously and doing some decoding. When decoding is done i have to store the results in a sql database. I am thinking since the decoding is done (in a while loop always running) dozens of times per second and data need to be stored to the database dozen of times every second if it is wise to open and close the connection to the sql server in each while loop or just leave it open and continue to write data to the database. First of all is this possible? Secondly if the connection remains open can third party applications or computer access the database at the same time and read data as my programm stores data?
217
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose . Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes , the underlying connection is returned back to the connection pool.
Not closing connections could cause timeouts as the connection pool may run out of available connections that can be used. A side point to this. If you use the using keyword, that will automatically close the connection for you.
Don't close your connection unless you are exiting your app, and then make sure you do. Ensure that you are using the same connection when you come back to do more I/O. Database connections are resource-intensive and should be established as few times as possible and shared as much as possible.
At the time of writing the first connection pool created in a process would always have a timer interval of 3min 10s, so you'd normally see sql connections being closed somewhere between 3min 10s and 6min 20s after you call Close() on the ADO object.
I'd open the connection, go through the loop as many times as needed, then close the connection. Opening and closing is very expensive. But, thanks to Mitch Wheat and Dave Markle, I've learned that connection pooling is done for free in the background with .NET, so the expense should be amortized over all your requests.
Even better, I'd batch the requests in the loop and execute the batch after the loop was done. You only require one network round trip that way.
If you agree with that last bit, I'd make sure that the batch INSERT was done in a transaction context so it could be committed or rolled back as a single unit of work. Isolation and thread safety will matter.
20
A database supports more than one concurrent connection, so yes it is very feasible in this scenario to leave the DB connection open - you will only lock out others if you have i.e. a long running query that results in row/ table locking. Just close the connection when you are done.
Also consider though that most DBs (i.e. SQL Server) use connection pooling internally, so even though you close a DB connection it just goes back to the pool and is not physically closed - the pool manages the physical DB connections - this results in much better performance, so the impact of opening/closing connections rapidly is reduced.
From MSDN:
Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
