Using the SqlConnection object gives you two benefits. First, you can make sure that the connection string is built correctly, because you can use the SqlConnectionStringBuilder class to create it. Secondly, it is much faster than OLEDB.
To create this connection string ...
Initial Catalog=emp_test;Persist Security Info=True;User ID=sa;Password=***;Data Source=172.21.70.94;Provider=SQLOLEDB;Use Encryption for Data=True;Trust Server Certificate=True;
... using SqlConnectionStringBuilder , you should write code like this ...
var builder = new SqlConnectionStringBuilder(); builder.DataSource = "172.21.70.94"; builder.Encrypt = true; builder.TrustServerCertificate = true; builder.InitialCatalog = emp_test; builder.PersistSecurityInfo = true; builder.UserID = "sa"; builder.Password = "***"; var connection = new SqlConnection(builder.ToString());
... the Encrypt property contains this definition in the .NET Framework ...
Gets or sets a Boolean value that indicates whether SQL Server uses SSL encryption for all data sent between the client and server, if a certificate is installed on the server.
... the TrustServerCertificate property contains this definition in the .NET Framework ...
Gets or sets a value indicating whether the channel will be encrypted bypassing the certificate chain to verify trust.
Therefore, I would say that this is the safest approach. You can guarantee that the .NET Framework will build the connection string correctly and you can get a good set of definitions related to what these properties mean for certificates based on their definitions.
Now, since you are also connecting to Oracle, the best approach is to continue building your OLEDB connection because you don't have much choice. But both connections are IDbConnection , and so you only have a factory that builds the correct connection and returns an IDbConnection .
This means that you get the best of both worlds, the performance and lightness of the SqlConnection object, and the IDbConnection abstraction IDbConnection that your code does not change.