Solved

Creating a table with an identity

Posted on 2009-05-11
6
346 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

8 Experts available now in Live!

Get 1:1 Help Now