Solved

Creating a table with an identity

Posted on 2009-05-11
6
370 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
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!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

730 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