TableCollection does not contain any table?
Hi All,
I have the following C# code to get the table scripts, and it does not work. I debugged the code and learned that it does not find any table in the smoDatabase.Tables collection, so the foreach() loop exits immediately.
Can someone shed a light?
Thanks in advance
public Sample()
{
Server smoServer = new Server('MYSQLSERVER');
Database smoDatabase = new Database(smoServer);
StringBuilder sbScript =newStringBuilder();
ScriptingOptions scriptOption =newScriptingOptions();
TableCollection AllTables =smoDatabase.Tables;
foreach(Table curTableinsmoDatabase.Tables)
{
StringCollection scriptTable = curTable.Script();
foreach (string tblscriptin scriptTable)
{
sbScript.AppendLine(tblscript);
}
}
}
[1687 byte] By [
DavidN.] at [2008-2-4]
Basically, I tried to use getenumerator() and the result is still the same. The while() loop exits immediately because the Database.Tables collection contain no data. I am convinced that this is a serious SMO bug.
public Sample()
{
Server smoServer = new Server('MYSQLSERVER');
Database smoDatabase = new Database(smoServer,"My_Database"); StringBuilder sbScript = new StringBuilder();
ScriptingOptions scriptOption = new ScriptingOptions();
IEnumerator AllTables = smoDatabase.Tables.GetEnumerator();
while(AllTables.MoveNext()) -- it never goes into this loop
{
// It never goes into this loop 
Table curTable = (Table)AllTables.Current;
.......
}
}
After hours of monkeying with SMO and test code, it's surprising to me, by not creating the Database object, that the following code works (thanks to MS folks for being so ...quiet) :
public Sample()
{
Server smoServer = new Server('MYSQLSERVER');
// Database smoDatabase = new Database(smoServer,"My_Database"); // Blocked out this declaration.
StringBuilder sbScript = new StringBuilder();
ScriptingOptions scriptOption = new ScriptingOptions();
TableCollection AllTables = smoDatabase["My_Database"].Tables; // And use the database name here
foreach(Table curTable in smoDatabase.Tables)
{
StringCollection scriptTable = curTable.Script();
foreach (string tblscript in scriptTable)
{
sbScript.AppendLine(tblscript);
}
}
}
I really don't understand what are the differences between:
Server.Database.Tables
and
Server["Database_Name"].table
Is it a bug? Or I am a dummy?
DQ
The Database constructor should only be used when you are creating a new database. The public constructors create the database proxy object in the "creating mode." Looking the database up in the databases collection use a private constructor that properly creates the database proxy object in the "existing mode."
So, for an existing database:
Database existingDatabase = server.Databases["MyDatabase"];
For a new database:
Database newDatabase = new Database(server, "NewDatabaseName");
