Solved

Creating a table with an identity

Posted on 2009-05-11
6
357 Views
Last Modified: 2012-05-06
Based on the code at http://www.experts-exchange.com/Q_24389065.html, I need to know how to allow for an optional "Identity" descriptor.  I need FoodID to be an Identity (1,1) when the table is created...

I tried my hand at the SqlColumn class attached, but not sure how to implement it in the CreateDatabase routine...
Public Class SqlColumn

        Private m_name As String

        Private m_dataType As DataType

        Private m_Identity As Boolean
 

        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
 

        Public ReadOnly Property Name() As String

            Get

                Return m_name

            End Get

        End Property
 

        Public ReadOnly Property DataType() As DataType

            Get

                Return m_dataType

            End Get

        End Property
 

        Public ReadOnly Property IsIdentity() As Boolean
 

            Get

                Return m_Identity

            End Get

        End Property

    End Class

Open in new window

0
Comment
Question by:sirbounty
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Haris V
ID: 24361288
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24363246
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 

Open in new window

0
 
LVL 67

Author Comment

by:sirbounty
ID: 24366394
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.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 24367329
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
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24371034
Sorry - I think you lost me a bit there....what IsIdentity column name?  Or is that something native to smo?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 24374801
1) You added a new IsIdentity property:

        Public ReadOnly Property IsIdentity() As Boolean
            Get
                Return m_Identity
            End Get
        End Property

2) You added a new parameter to the constructor:

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

3) You assign the parameter to the field value:

m_Identity = IsIdentity

4) IsIdentity parameter is the same as IsIdentity property.  The compiler should find the property first, which means that the field value will not be set.

5) Change the parameter name, and the assignment:

Public Sub New(ByVal name As String, ByVal dataType As DataType, Optional ByVal identity As Boolean = False)

m_identity = identity

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now