DBSchemaProvider for MSSQL - GetTables
I have started writing the DBSchemaProvider for MSSQL and here is my first method to return all the Tables from MSSQL Database. I will appreciate if you know a better way to retrieve table list please post that, and I would love to
modify the Provider with the best solution.
public override TableSchemaCollection GetTables(DatabaseSchema database)
{
TableSchemaCollection list = new TableSchemaCollection();
TableSchema tableSchema;
string cmdText = string.Format(@"TABLE_NAME, TABLE_SCHEMA OWNER, REFDATE CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES , sysobjects
where Table_Name = sysobjects.[name]
and TABLE_CATALOG = '{0}' AND TABLE_TYPE = 'BASE TABLE' ORDER BY 1", database.Name);
SqlConnection connection = new SqlConnection(database.ConnectionString);
SqlCommand cmd = new SqlCommand(cmdText, connection);
SqlDataReader dataReader = null;
try
{
connection.Open();
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
tableSchema = new TableSchema(database, dataReader.GetString(0), dataReader.GetString(1), dataReader.GetDateTime(2));
list.Add(tableSchema);
}
}
catch
{
throw;
}
finally
{
if (dataReader != null)
dataReader.Close();
}
return list;
}