Solved

Creating a table with an identity

Posted on 2009-05-11
6
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

717 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