Creating a table with an identity

The basic syntax for creating a table is as follows:

CREATE TABLE [database_name].[owner].table_name
   (column_name data_type [length] [IDENTITY(seed, increment)] [NOT NULL])

9> CREATE TABLE Groups (
10>    Id int IDENTITY (1, 1) NOT NULL
11> )
12> GO
1>
2> drop table Groups
3> GO

Looking at the referenced question, it uses SMO to create the table.  SMO does have a scripting piece that you can use instead of the classes.

http://www.devx.com/dotnet/Article/35552/1763/page/3

Neil,
SqlTable is a definition class that I started out using with a previous attempt.  If you add a property to the class, you still need to use the value for IsIdentity to create a primary key column.  You could always discard the SqlTable and SqlColumn definition classes, in favor of the SMO equivalents.

Proof-of-concept:

http://www.delarou.net/weblog/PermaLink,guid,f5fa1f6e-c225-428f-bd6a-954c343a87f1.aspx

Imports System.Data.SqlClient 
Imports Microsoft.SqlServer.Management.Smo 
Imports Microsoft.SqlServer.Management.Common 
 
Public Class Sample 
    Public Sub Create(ByVal connectionstring As String) 
        Dim connection As New SqlConnection(connectionstring) 
        Dim server As New Server(New ServerConnection(connection)) 
        
        Dim database As Database = server.Databases("MyDatabase") 
        
        ' Create table, called Customer 
        Dim table As New Table(database, "Customer") 
        
        ' Add 'ID' column which is the primary key 
        Dim idColumn As New Column(table, "ID") 
        idColumn.DataType = DataType.Int 
        idColumn.Identity = True 
        idColumn.IdentitySeed = 1 
        idColumn.IdentityIncrement = 1 
        
        ' Create a primary key index 
        Dim index As New Index(table, String.Format("PK_{0}", table.Name)) 
        index.IndexKeyType = IndexKeyType.DriPrimaryKey 
        index.IndexedColumns.Add(New IndexedColumn(index, "ID")) 
        table.Indexes.Add(index) 
        
        ' Add 'Name' column 
        Dim nameColumn As New Column(table, "Name") 
        nameColumn.DataType = DataType.VarChar(50) 
        
        ' Add colums to table 
        table.Columns.Add(idColumn) 
        table.Columns.Add(nameColumn) 
        
        table.Create() 
    End Sub 
End Class 

Select allOpen in new window

No, I really like those classes that you setup, so I'd rather stick with them.
I'll check out the first link and see if I can determine how to accomplish this - thanks.

You can still use the SqlTable and SqlColumn classes.  They are nice, simple, light-weight classes that are easy to construct.  

The problem that I see is that you are going to get a recursive stack overflow from this code:

       Public Sub New(ByVal name As String, ByVal dataType As DataType, Optional ByVal IsIdentity As Boolean = False)
            m_name = name
            m_dataType = dataType
            m_Identity = IsIdentity
        End Sub

The "IsIdentity" argument is the same as the IsIdentity column name.  You will have to rename the argument.

      m_Identity = identity

Sorry - I think you lost me a bit there....what IsIdentity column name?  Or is that something native to smo?