I've made a utility method to get schema from a db table. In this case Oracle 11 db.
public static DataTable GetColumnsSchemaTable(DbConnection cnctn, string tableName)
{
DataTable schemaTable;
string[] restrictions = new string[3] { null, tableName, null };
schemaTable = cnctn.GetSchema("Columns", restrictions);
/* table name is case sensitive and in XXXX db table names are UPPER */
if (schemaTable.Rows.Count == 0)
{
restrictions = new string[3] { null, tableName.ToUpper(), null };
schemaTable = cnctn.GetSchema("Columns", restrictions);
}
return schemaTable;
}
This works fine when the cnctn is created with System.Data.OracleClient provider factory. When it's created with System.Data.OleDb provider factory the table has no rows. I have an other utility method to get connection strings:
public static string GetDbConnectionString(DbConnection cnnctn, string provider, string server, string dbName, string user, string pwd)
{
if (cnnctn is OleDbConnection)
{
string usedProvider;
if (provider == null)
usedProvider = "msdaora";
else
usedProvider = provider;
return string.Format("Provider={0};Data Source={1};User Id={2};Password={3};",
usedProvider, dbName, user, pwd);
}
else if (cnnctn is System.Data.OracleClient.OracleConnection)
{
return string.Format("Data Source={0};User Id={1};Password={2};",
dbName, user, pwd);
}
else if (cnnctn is Oracle.DataAccess.Client.OracleConnection)
{
return string.Format("Data Source={0};User Id={1};Password={2};",
dbName, user, pwd);
}
else if (cnnctn is SqlConnection)
{
return string.Format("Data Source={0}; Initial Catalog={1}; User Id={2}; Password={3};",
server, dbName, user, pwd);
}
return string.Empty;
}
and the db connection works (i'm deleting rows before trying to get schema). All help will be appreciated.
Thanks & Best Regards - Matti
Ok. I sorted it out. I made this code long time ago only for now deprecated OracleClient and left the possibility to use other connections / provider factories. I didn't remember anymore that the restrictions vary from connection to connection. So the correct usage is:
string[] restrictions = new string[4] { null, null, tableName, null };
for OleDbConnection.