none
could i use linq to sql create table in my database? RRS feed

  • Question

  • Hi, 
    i have a question ,linq to sql  datacontext don't have createtable function, it only can createdatabase,so if  i  already  have a db but i want use linq to sql to create a table in my database what should i do?

    thanks..

    Regards
    freshman

    Wednesday, July 15, 2009 7:53 AM

All replies

  • Hi,

    you can use the ExceuteCommand -method of the DataContext to run some T-SQL statements for creating tables.


    Kind regards,
    Günther
    Thursday, August 20, 2009 4:09 PM
  • What is your end goal , you have a datacontext but the target database is missing a table?
    William Wegerson (www.OmegaCoder.Com)
    Thursday, August 20, 2009 4:21 PM
    Moderator
  • As Gunther said, you can use ExecuteCommend to send DDL to your database to modify the schema. However, realize that the security rights required to do DDL manipulation is higher than most users should have rights to. Your end users shouldn't have access to modify schema structures. This is better done as part of an admin/setup script.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Thursday, August 20, 2009 8:38 PM
    Moderator
  • Try the flowing code:

    namespace  ALinq.Mapping
    {
        
    class  CreateTableDemo
        {
            [Table(Name 
    =   " User " )]
            
    class  User
            {
                [Column]
                
    public   int  ID;

                [Column]
                
    public   string  Name;

            }

            
    static   void  Main()
            {
                var constr 
    =   @" Data Source=NOTEBOOK\SQLEXPRESS;Initial Catalog=DemoDataContext;Integrated Security=True " ;
                var context 
    =   new  DataContext(constr) { Log  =  Console.Out };
                var metaTable 
    =  context.Mapping.GetTable( typeof (User));
                Debug.Assert(metaTable 
    !=   null );

                var typeName 
    =   " System.Data.Linq.SqlClient.SqlBuilder " ;
                var type 
    =   typeof (DataContext).Assembly.GetType(typeName);
                var bf 
    =  BindingFlags.Static  |  BindingFlags.NonPublic  |  BindingFlags.InvokeMethod;
                var sql 
    =  type.InvokeMember( " GetCreateTableCommand " , bf,  null null new [] { metaTable });
                Console.WriteLine(sql);
                
    // Excute SQL Command
            }
        }
    }
    Wednesday, February 24, 2010 10:21 AM
  • SureMai, thanks for this helpful code snippet!

    My ISP doesn't allow programmatic database creation (i.e. DataContext.CreateDatabase), so is there a simpler way of creating all the tables?

    Why doesn't DataContext just have a CreateTable and CreateAllTables function?

    But at least it seems that programmatically I could do it using the above code for each table.

    -John

     

    Friday, February 18, 2011 2:44 AM
  • Yikes, tt took me a while to realize that the SqlBuilder class in the example is not public.  That's very scary.

    Anyway, here's how resolved the issue.  I use one DataContext-derived class for all my data context usage (or you put just the common code in a class to use as a base class).  It might be a bit naive, but it seems to work so far, though I've just started using it:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Diagnostics;
    using System.Reflection;
    
    namespace MyProject.Database
    {
      [Database]
      public class DBDataContext : DataContext
      {
        private static MappingSource mappingSource = new AttributeMappingSource();
        private static string[] tableNames = new string[]
        {
          "DBData",
          // etc.
        };
    
        public DBDataContext(string connectionString)
          : base(connectionString, mappingSource)
        {
          CommandTimeout = 600;
        }
    
        public string DatabaseName
        {
          get
          {
            return Connection.Database;
          }
        }
    
        public void CreateCheck()
        {
          try
          {
            if (!DatabaseExists())
              Create();
    
            CreateTableCheck();
          }
          catch (Exception)
          {
          }
        }
    
        public void Create()
        {
          try
          {
            CreateDatabase();
          }
          catch (Exception)
          {
          }
        }
    
        public void Delete()
        {
          try
          {
            DeleteDatabase();
          }
          catch (Exception)
          {
          }
        }
    
        public void Recreate()
        {
          if (DatabaseExists())
            Delete();
    
          Create();
        }
    
        public Type GetTableType(string tableName)
        {
          string typeName = "MyProject.Database." + tableName;
          Type type = typeof(DBDataContext).Assembly.GetType(typeName);
          return type;
        }
    
        public bool HaveTable(string tableName)
        {
          bool returnValue = false;
    
          try
          {
            Type type = GetTableType(tableName);
    
            if (GetTable(type) != null)
              returnValue = true;
          }
          catch (Exception)
          {
          }
    
          return returnValue;
        }
    
        public string GetCreateTableCommand(string tableName)
        {
          Type type = GetTableType(tableName);
          var metaTable = Mapping.GetTable(type);
          var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod;
          var builderTypeName = "System.Data.Linq.SqlClient.SqlBuilder";
          var builderType = typeof(DataContext).Assembly.GetType(builderTypeName);
          string command = builderType.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }) as string;
          return command;
        }
    
        public void CreateTable(string tableName)
        {
          string command = GetCreateTableCommand(tableName);
          int rowsAffected;
    
          try
          {
            rowsAffected = ExecuteCommand(command);
          }
          catch (Exception)
          {
          }
        }
    
        public string GetCreateTableCheckCommand(string tableName)
        {
          string command =
            "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[" + tableName + "]') AND type in (N'U'))\n" +
            "BEGIN\n" +
            GetCreateTableCommand(tableName) +
            "END\n";
          return command;
        }
    
        public void CreateTableCheck(string tableName)
        {
          string command = GetCreateTableCheckCommand(tableName);
          int rowsAffected;
    
          try
          {
            rowsAffected = ExecuteCommand(command);
          }
          catch (Exception)
          {
          }
        }
    
        public void CreateTableCheck()
        {
          foreach (string tableName in tableNames)
          {
            CreateTableCheck(tableName);
          }
        }
    
        public string GetDeleteTableCommand(string tableName)
        {
          string command = "DROP TABLE " + tableName + " ;";
          return command;
        }
    
        public void DeleteTable(string tableName)
        {
          string command = GetDeleteTableCommand(tableName);
          int rowsAffected;
    
          try
          {
            rowsAffected = ExecuteCommand(command);
          }
          catch (Exception)
          {
          }
        }
    
        public void DeleteTables()
        {
          foreach (string tableName in tableNames)
          {
            if (HaveTable(tableName))
              DeleteTable(tableName);
          }
        }
    
        public string GetDeleteTableDataCommand(string tableName)
        {
          string command = "DELETE FROM " + tableName + " ;";
          return command;
        }
    
        public void DeleteTableData(string tableName)
        {
          string command = GetDeleteTableDataCommand(tableName);
          int rowsAffected;
    
          try
          {
            rowsAffected = ExecuteCommand(command);
          }
          catch (Exception)
          {
          }
        }
    
        public void DeleteAllTableData()
        {
          foreach (string tableName in tableNames)
          {
            if (HaveTable(tableName))
              DeleteTableData(tableName);
          }
        }
    
        public Table<DBData> DataStore
        {
          get
          {
            return GetTable<DBData>();
          }
        }
    
        // etc.
      }
    }
    
    

    One problem, though, is that the NOT EXISTS clause I use doesn't work on the MS SQL Server 2005 that my ISP uses, though it does work on the EXPRESS 2008 R2 that I use at home.  Thus it will still try to create the table and throw an exception if the table already exists.  Anyone know how to fix it?  I'm an SQL dummy.

    Notice that for now I just ignore exceptions, since these funciton are more for admin use anyway.

    -John

     

    Friday, February 18, 2011 8:34 PM
  • Replace "DmsDataContext", below, with your typed DataContext and place the code block in the DataContext's partial class (or subclass your typed DataContext and use that subclass throughout your program and place this method in there:

     

    public void CreateTable(Type linqTableClass)
    {
    	using (var tempDc = new DmsDataContext())
    	{
    		var metaTable = tempDc.Mapping.GetTable(linqTableClass);
    		var typeName = "System.Data.Linq.SqlClient.SqlBuilder";
    		var type = typeof(DataContext).Assembly.GetType(typeName);
    		var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod;
    		var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable });
    		var sqlAsString = sql.ToString();
    		tempDc.ExecuteCommand(sqlAsString);
    	}
    }
    

     

    However, note, that creating these tables one at a time like this doesn't actually create the relationships between tables, even if these exist in your LINQ data model.  So the utility is limited, at least as far as the code above is concerned.

    • Proposed as answer by Jeromeyers Friday, March 25, 2011 3:53 PM
    • Edited by Jeromeyers Friday, March 25, 2011 3:59 PM expanding
    Friday, March 25, 2011 3:52 PM
  • kindly give me sample to how i call this method

    CreateTable

    and can i pass tables dynamicly

    Monday, November 18, 2013 10:40 AM