-4

Can I create OleDbConnection object in c# using SQL Server connection string? if yes what provider do I need to use? Any sample code will help if possible.

Krrish
  • 135
  • 15
  • 1
    You can, but why would you want to do that? Just use the classes already provided by the framework in System.Data.SqlClient. – Zohar Peled Aug 01 '18 at 14:06
  • I have a reason. When using script tasks in SSIS. The standard connection is OLEDB and using the connection string used in the control/dataflow often provided from parameter or parent package. – KeithL Aug 01 '18 at 14:10
  • [You might want to check out this](https://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/) – Zohar Peled Aug 01 '18 at 14:17

1 Answers1

1

You would have to parse the parameters of the SqlConnectionStringBuilder into a OleDbConnectionStringBuilder:

System.Data.OleDb.OleDbConnectionStringBuilder builder = 
new System.Data.OleDb.OleDbConnectionStringBuilder();
builder["Provider"] = "Microsoft.Jet.OLEDB.4.0";
builder["Data Source"] = "C:\\Sample.mdb";
builder["User Id"] = "Admin;NewValue=Bad";

var connectionString = builder.ConnectionString;

And create the connection:

public void InsertRow(string connectionString, string insertSQL)
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        // The insertSQL string contains a SQL statement that
        // inserts a new row in the source table.
        OleDbCommand command = new OleDbCommand(insertSQL);

        // Set the Connection to the new OleDbConnection.
        command.Connection = connection;

        // Open the connection and execute the insert command.
        try
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the
        // code exits the using block.
    }
}

Sources from Microsoft documentation

DeveloperExceptionError
  • 689
  • 11
  • 22