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
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); }
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();
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 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.
Yes. CommandTimeout is how long a single command can take to complete. ConnectionTimeout is how long it can take to establish a connection to the server to start with. For instance, you may be executing relatively long-running queries – it’s perfectly okay for them to take 10 minutes to complete, but if it took 10 … Read more
SqlConnection is for SQL Server. You need MySqlConnection – this is not part of the .NET Framework, so you will have to download it and reference it in your project. You can then create a MySqlConnection object and connect to MySQL in your application: MySqlConnection connection = new MySqlConnection(myConnString); You will also have to use … Read more
I’ve done some tests since asking this question and found most if not all answers on my own, since no one else replied. Please let me know if I’ve missed anything. Q1: Is connection automatically enlisted in transaction? Yes, unless enlist=false is specified in the connection string. The connection pool finds a usable connection. A … Read more