Creating a table using ADO.NET

[SCRawl]

I hate asking this question, but it's bugging the hell out of me.

I have this bit of code here:

conn = new SqlCeConnection();
string connStr = Properties.Settings.Default.Database1ConnectionString;
conn.ConnectionString = connStr;

conn.Open();
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
cmd.CommandText =
"CREATE TABLE ["+nextSeason+"TeamAssignment] ([TeamNumber] tinyint CONSTRAINT PK_TeamNumber PRIMARY KEY," +
" [TeamLetter] nvarchar(1) NOT NULL UNIQUE)";
cmd.ExecuteNonQuery();
conn.Close();

This code does create a new table in the database. I can use another SQL command to write data to it, for example. The problem: this table is not persistent; it doesn't show up in the database, and closing/restarting the IDE (Visual Studio 2008) removes the table completely. The documentation suggests that it's supposed to be auto-commit by default, and I haven't set it to be otherwise.

Anyone have any thoughts that they'd like to share? This is for a schedule-generation application I'm making for my softball league; it needs to create tables programmatically (sp?), which is why it's, you know, in the program.

[SCRawl]

(nextSeason is an int like 2012, etc.)

The database is maybe a little convoluted, but it works. It goes like this:

- each game night has 16 slots (i.e. diamond 1 home, diamond 1 away, diamond 2 home, diamond 2 away, etc.) occupied by one of 16 team letters (A,B,...,P)
- there are 38 games per season, and so 38 tables (named Game1...Game38) which have two columns: one for the slots, and one for the team letters occupying those slots. The teams occupying those slots change from game to game, naturally, and have been optimized so as to create balance
- for each season a new table is to be created (2011TeamAssignment, 2012TeamAssignment, etc.) with two columns: one for the teamID (which is an int, and the primary key of the Team table) and one for the team letter (which will be randomly assigned each season). In this way there will (probably) be a different schedule each year.

There are other tables, but these are the ones necessary to understand the way it works.

Now, I could just manually generate a new table each year. Or I could change the way the tables work: instead of having 2011TeamAssignment, etc. I could have TeamAssignment with another column for the season, and I suppose this would make just a little more sense.

But still the question remains: why the hell can't I create a new table?

(I recognize that it is not your duty to answer this question. I ask for rhetorical purposes as well.)

[SCRawl]

My searches didn't uncover that link, and I'll give it a more thorough reading tomorrow. In the meantime, I'll probably just knuckle under and do it the easier way. It means that the database will have a table with one column with very repetitive (but critical) data, which offends my sensibilities, but is probably still the better way to do it.

Thank you for your assistance.

[TronPaul]

It could be the way your database is set up and doesn't save per runtime. I would test to make sure you can make a random table and data and that it stays persistent and then make sure your table can stay persistent. From experience, the baser, more annoying solution is often the one needed.