Creation of DatabaseReader with open connection

[thomasjoscht]

Hey,

I'm currently running in a stucking situation.

TL;DR

The problem has two parts:

1. DatabaseReader has no constructor parameter "database name".
2. DatabaseReader with constructor parameter DbConnection sometimes does not work because of default "Persist Security Info = False"

No database name parameter

In my application a user provides connection string + provider. Database name can be part of connection string but it is not required (because sometimes database does not exist and will be created later). In write scenarios database name is therefore an property and will later be used with DbConnection.ChangeDatabase Methode. Your library does not support database name as parameter.

Does there any other method exist for setting database name without providing it in connection string?

Persist Security Info = false

Constructor of DatabaseReader can use an existing DbConnection. I'll use this because DbConnection have method ChangeDatabase which supports my use case described above. But in this case sometimes this DbConnection will be directly used as nested connection which work just fine (e.g. SQL Server). But sometimes new DbConnection will be created based on ConnectionString in DbConnection (e.g. Informix). In case the DbConnection is already opened and "Persist Security Info" is false (which is default) the ConnectionString does no longer contain password information. Create a new DbConnection fails therefore.

How can I force to use only given DbConnection in constructor for all provider?

Regards Thomas

[martinjw]

1. True, the Reader does not take a Name, and the DatabaseSchema that is populated from the Read* methods doesn't have a name either - it doesn't get a name from the connection string.

           var connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Database=Adventure";
           using (var con = new SqlConnection(connectionString))
           {
               con.Open();
               var reader = new DatabaseReader(con);
               var schema1 = reader.ReadAll();
               //schema1 has no name
   
               con.ChangeDatabase("Northwind");
               var schema2 = reader.ReadAll();
               //still no name
           }

   This works, but neither schema has a name. I don't want to parse the name from the connection string, which would really need the connectionstringBuilder classes from the providers.

2. Using an existing DbConnection This works with SqlServer, Oracle, MySql, Sqlite and PostgreSql. It only partially works with Db2, Informix, Sybase etc because we rely on the DbProviderFactory classes in those provider dlls. Those classes create new DbConnections which we don't control- they have to use the connection string passed in.

It would be possible to avoid the DbProviderFactory classes by writing the custom sql to get the tables/columns metadata, and including it in the DatabaseSchemaReader. It's not difficult, but I'd need an Informix db to do it... This would be required for .net Core though, because they don't impement DbProviderFactory.