SqlBulkWriter class is tightly coupled to implementation concerns which make it difficult to test the class in isolation. Knowing the connection string is not really a concern of that class and can be delegated out to another service. Something like public interface IDbConnectionFactory { IDbConnection CreateConnection(); } and its implementation for your class would look … Read more
It’s not a common way to share a SqlConnection, and it should be used only under special uses cases. First, You’re true that resource pooling is a common pattern used to improve performance when working with sockets, network streams, web services… But especially for SqlConnection, you don’t have to worry about this because the framework … Read more
By default, SqlConnection will use connection pooling. Therefore your code does most likely not actually open many connections in either case. You can control if SqlConnection will use pooling by enabling or disabling the pool in the connectionstring, depending on what DB your connection string is for, the syntax will vary. See here for some … Read more
Dapper now (and for quite some time) deals with this internally. It just works™ Original (outdated) answer: You aren’t wrong. The reason I hadn’t noticed this inconvenience is that for legacy reasons (specifically: we used to use LINQ-to-SQL exclusively) our primary connection-like-thing is a DataContext – so we re-expose the dapper methods as extension methods … Read more
Since you have a using block, the Dispose method of the SQLCommand will be called and it will close the connection: // System.Data.SqlClient.SqlConnection.Dispose disassemble protected override void Dispose(bool disposing) { if (disposing) { this._userConnectionOptions = null; this._poolGroup = null; this.Close(); } this.DisposeMe(disposing); base.Dispose(disposing); }
I’m not sure if you’re still looking into this, but the DbCommandBuilder class provides a method QuoteIdentifier for this purpose. The main benefits of this are that it’s database-independent and doesn’t involve any RegEx mess. As of .NET 4.5, you have everything you need to sanitize table and column names just using your DbConnection object: … Read more
Because it also implements IDisposable. The purpose of Using statement is that when control will reach end of using it will dispose that object of using block and free up memory. its purpose is not only for auto connection close, basically it will dispose connection object and obviously connection also closed due to it. Its … Read more
No, Disposing of the SqlCommand will not effect the Connection. A better approach would be to also wrap the SqlConnection in a using block as well: using (SqlConnection conn = new SqlConnection(connstring)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(cmdstring, conn)) { cmd.ExecuteNonQuery(); } } Otherwise, the Connection is unchanged by the fact that a … Read more
Just change the SqlCommand.CommandText instead of creating a new SqlCommand every time. There is no need to close and reopen the connection. // Create the first command and execute var command = new SqlCommand(“<SQL Command>”, myConnection); var reader = command.ExecuteReader(); // Change the SQL Command and execute command.CommandText = “<New SQL Command>”; command.ExecuteNonQuery();
Just do this: using(var connection = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConn”].ConnectionString)) using(var command = connection.CreateCommand()) { command.CommandText = “…”; connection.Open(); command.ExecuteNonQuery(); } Not calling dispose on the command won’t do anything too bad. However, calling Dispose on it will suppress the call to the finalizer, making calling dispose a performance enhancement.