Solved

Creating a table with an identity

Posted on 2009-05-11
6
366 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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