Advance allotment of a primary key in a SQL Server table using C#

Question

I have a database table Contact, in my application layer I'm getting a request from the client (i.e., Web API HTTP request) and I'm pushing the data into MSMQ.

I also have a Windows Service which receives the message from the respective MSMQ and processes the data. It inserts the data into the respective database table Contact.

SQL Server table structure of Contact:

CREATE TABLE [dbo].[Contact] 
(
    [ContactId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [Email] [varchar](255) NOT NULL,
    [Mobile] [varchar](10),
    [CreatedDate] [datetime2](7) NOT NULL,

    CONSTRAINT PK_Contact PRIMARY KEY (ContactId)
)

In this process, I need to send the primary key of the table Contact because the primary key is the Id of Contact. In the response, the client expects the Contact Id.

It is possible to allot an Id of Contact before data is inserted? or kindly assist me.

Please let me know changes required in the table structure.

Answer 1

If you want to know which will be the next identity value, there is a built-in function for that. Nevertheless doing what you are suppposed to do is not recomended because other sessions may insert in that table. You may want to use other method of creating a numeric ID for your table, or just insert some data to "allot" and the updating with your real values. It seems a lot of work and I would recomend to insert the data and then getting the real ID with SCOPE_IDENTITY() function. That would be much secure.

Anyway, here is the function that will return that next autonumeric value:

SELECT IDENT_CURRENT('Contact') + 1

Answer 2

Since you have multiple sessions running you can't use built-in functions to get the next id available but you can work around that.

The work around is quite simple. here's are the step

1 - create another table with :

contactid, integer, identity same as yours
id, guid
isUsed, bit, default 0

2 - generate for each application that is running a guid when it starts

3 - when ready to create a contact in your window query the new table where the id is equal to your generated guid and where the isUsed is false, if there is any take that Contactid as it's not used yet, otherwise insert a new record in that table with your guid and isused as false and query again. this will generate a id just for your session.

4 - remove the identity on the contact table as id will come from the other table with identity and those are unique so no clashing possible

Answer 3

Use GUID.

1. Generate GUID and pass it to INSERT.
2. Use this GUID for primary key, or use guid to get Id AFTER insert (Add additional column, then later you could get row by this column).