none
Create table on SQL server? RRS feed

  • Question

  • Once I generate the dbml for a database, is there a way to have my app recreate a missing table on the database later on?  Here's the quick test that I tried:

    -Create a table
    -Map it with LINQ
    -Query it in my app
    -Delete the table

    The next time I ran my app, I got an invalid object exception saying that the table I was querying did not exist.  Is there any way that I could, for example, catch this exception and use LINQ to create the missing table? 
    Monday, March 24, 2008 4:36 PM

Answers

  • There is no LINQ to SQL feature that does this.  However, you should be able to make your own method that does this.  All the meta data necessary is available via the DataContext.Mapping property. 

     

    Monday, March 24, 2008 6:45 PM
    Moderator

All replies

  •  

    The exception should give you the name of th missing object(table) and you can use "db.CreateDatabase();" for creating a new database based on the mapping schema, and it will include the deleted table.
    Monday, March 24, 2008 4:46 PM
  • Wow, thanks for the quick reply.  I tried that already though, and that method tries to create the entire database, not just missing tables.  This means that if the DB already exists (but is missing a table), you get another exception that you're trying to recreate an existing database.  What I need is something similar to CreateDatabase, but for a single mapped table (or all mising mapped tables). 
    Monday, March 24, 2008 4:54 PM
  • I dont believe we have anything like CreateTable() API. but you could use a workaround.

    • Generate a mapping on the new database, with missing tables.
    • take a diff of old and new mapping files and use the difference to create a temporary mapping.
    • Use the temporary mapping and db.CreateDatabase() for creating a new temp database.
    • Use SQLserver to get all the tables in temp database and run them on database with missing tables.

    Sorry I didn't have a short path to your goal, but hope it helps.

     

    Monday, March 24, 2008 6:21 PM
  • There is no LINQ to SQL feature that does this.  However, you should be able to make your own method that does this.  All the meta data necessary is available via the DataContext.Mapping property. 

     

    Monday, March 24, 2008 6:45 PM
    Moderator
  • Ah,I didn't know that the DataContext.Mapping property gave you that-looks like what I needed.  I tried a very brute force approach that looks something like this:
    Code Snippet

                    string query = "Create table ";


                    MetaTable t = db.Mapping.GetTable(typeof(TestTable));

                    query += t.TableName + "(";
                    var v1 = from m in t.RowType.DataMembers
                             select m;
                    foreach (var m in v1)
                    {
                        query += m.Name + " ";
                        query += m.DbType.ToString();
                        query += ",";
                    }
                    query = query.Remove(query.LastIndexOf(','));
                    query += ")";
                    db.ExecuteCommand(query);

    This works, but I can see it getting a little complicated to maintain.  I'll have to dig into the docs to see if I can find a better solution, but that's from five minutes of poking around in the debugger to see what the mapping exposes.

    Thanks for the solution though-if nothing else it's given me a bunch of other stuff to look at.
    Monday, March 24, 2008 7:19 PM