Geek Answers Handbook

Is there a way to use ADO.NET to determine if there is a table in a database that works with any data provider?

Is there a way to use ADO.NET to determine if there is a table in a database that works with any data provider?

I am currently doing something like this:

bool DoesTableExist(string tableName) { DbCommand command = this.dbConnection.CreateCommand(); command.CommandText = "SELECT 1 FROM " + tableName; try { using (DbDataReader reader = command.ExecuteReader()) { return true; } } catch (DbException) { return false; } }

I hope there is a way that does not include catching exceptions.

+6
c # sql
source share
2 answers

Well, you can use the Connection.GetSchema("TABLES") method.

Returns a DataTable that will contain the rows of all tables in your database. From here you can check this out and see if the table exists.

Then you can take the next step:

  private static bool DoesTableExist(string TableName) { using (SqlConnection conn = new SqlConnection("Data Source=DBServer;Initial Catalog=InitialDB;User Id=uname;Password=pword;")) { conn.Open(); DataTable dTable = conn.GetSchema("TABLES", new string[] { null, null, "MyTableName" }); return dTable.Rows.Count > 0; } }

If you are using .NET 3.5, you can also do this with an extension method.

+16
source share

A small improvement in Kyle is responsible for the fact that different databases (for example, oracle vs ms-sql-server) put the column of the name table in a different index in the "Tables" table:

  public static bool CheckIfTableExists(this DbConnection connection, string tableName) //connection = ((DbContext) _context).Database.Connection; { if (connection == null) throw new ArgumentException(nameof(connection)); if (connection.State == ConnectionState.Closed) connection.Open(); var tableInfoOnTables = connection //0 .GetSchema("Tables") .Columns .Cast<DataColumn>() .Select(x => x.ColumnName?.ToLowerInvariant() ?? "") .ToList(); var tableNameColumnIndex = tableInfoOnTables.FindIndex(x => x.Contains("table".ToLowerInvariant()) && x.Contains("name".ToLowerInvariant())); //order tableNameColumnIndex = tableNameColumnIndex == -1 ? tableInfoOnTables.FindIndex(x => x.Contains("table".ToLowerInvariant())) : tableNameColumnIndex; //order tableNameColumnIndex = tableNameColumnIndex == -1 ? tableInfoOnTables.FindIndex(x => x.Contains("name".ToLowerInvariant())) : tableNameColumnIndex; //order if (tableNameColumnIndex == -1) throw new ApplicationException("Failed to spot which column holds the names of the tables in the dictionary-table of the DB"); var constraints = new string[tableNameColumnIndex + 1]; constraints[tableNameColumnIndex] = tableName; return connection.GetSchema("Tables", constraints)?.Rows.Count > 0; } //0 different databases have different number of columns and different names assigned to them // // SCHEMA,TABLENAME,TYPE -> oracle // table_catalog,table_schema,table_name,table_type -> mssqlserver // // we thus need to figure out which column represents the tablename and target that one
+1
source share

More articles:


All Articles