How to return uniqueidentifier initialized with NEWSEQUENTIALID() function to the .Net client

Article translations Article translations
Article ID: 969200 - View products that this article applies to.
Expand all | Collapse all

On This Page

Source: Microsoft Support

RAPID PUBLISHING

RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

Symptom

The uniqueidentifier SQL Server data type is increasingly used in applications that require global uniqueness of the primary key. In SQL Server 2005, the NEWSEQUENTIALID() function has been introduced that generates sequentially increasing GUID values and hence reduces page contention at the leaf level of the index. However, unlike the NEWID() function, the NEWSEQUENTIALID() function cannot be used in queries. However, it can only be used with DEFAULT constraints on table columns of type uniqueidentifier. Consequently, the .NET client that is inserting a a new row has no knowledge of the primary key until the SQL Server has actually created the new row and the NEWSEQUENTIALID() function executed. This poses an issue of how the generated primary key value can be returned to calling a .NET application.

Resolution

For .Net client using System.Data.SQLClient



The solution is to use the OUTPUT clause with the insert statement so that the generated primary key can be extracted with the ExecuteScalar() method of the SqlCommand object.

For .Net client using LINQ to SQL



Because the SQL Server generates the primary key in this scenarion, the LINQ to SQL will only work correctly if the Column attribute with IsDbGenerated=true property is applied to entity property that corresponds to the underlying primary key column.

More Information



Detailed information:

Consider following table definition:

 

create table Books (Id uniqueidentifier Default NEWSEQUENTIALID()

                         NOT NULL

                        CONSTRAINT PK_Books PRIMARY KEY CLUSTERED,

                    Title varchar (32))

 

.Net client using System.Data.SQLClient



The .Net client application can issue "INSERT INTO Books (Title) Output inserted.Id  VALUES (@Title); " query that specifies the book title. By the virtue of the DEFAULT clause specified for the Id column of the Books table a new GUID will be assigned by the NEWSEQUENTIALID() function. The “Output inserted.Id” clause of the insert statement makes this GUID value available to the .Net client that extract it in “ID = (Guid)cmd.ExecuteScalar();“ statement as shown below:

 

 using System;

 using System.Collections.Generic;

 using System.Linq;

 using System.Text;

 using System.Data;

 using System.Data.SqlClient;

  

 namespace Client

 {

    class Program

    {

        static public Guid AddBook(string title, string connString)

        {

            Guid ID = Guid.Empty;

            string sql =

             "INSERT INTO Books (Title) Output inserted.Id  VALUES (@Title); ";

 

            using (SqlConnection conn = new SqlConnection(connString))

            {

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.Add("@Title", SqlDbType.VarChar);

                cmd.Parameters["@title"].Value = title;

                try

                {

                    conn.Open();

                    ID = (Guid)cmd.ExecuteScalar();

                    Console.WriteLine("Added book " + title + " with ID=" + ID.ToString("D"));

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex.Message);

                }

            }

            return (Guid)ID;

        }

 

 

        static void Main(string[] args)

        {

            string connString =

                "Data Source=(local);Initial Catalog=Test;Integrated Security=SSPI";

 

            for (int i = 0; i < 10; i++)

            {

                string title = "Encyclopedia Volume " + i;

                Guid id = AddBook(title, connString);

            }

            Console.ReadLine();

        }

    }

}

 

.Net client using LINQ to SQL



The LINQ to SQL will only work correctly in this scenario if you manually add the IsDbGenerated=true attribute for the Id property of the generated Book entity.

 

Assuming that in Visual Studio 2008 you have added “Linq to SQL Classes” project item and named it Test.dbml and dragged the Books table to the Designer surface, the Test.Designer.cs file will contain generated Entity classes. Open this file and locate the public System.Guid Id property contained in the public partial class Book entity.

 

Apply the IsDbGenerated=true property to the Column attribute of the Id property as shown below:

 

 [Column(Storage="_Id", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]

 

         public System.Guid Id

 

Following code fragment demonstrates the insertion of the Linq Book entities

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace LinqClient

{

    class Program

    {

        static public Guid AddBookLinq(string title, TestDataContext db)

        {

            Book bk = new Book();

            bk.Title = "LINQ to SQL";

            db.Books.InsertOnSubmit(bk);

            db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);

            Console.WriteLine("Linq Added book " + bk.Title + " with ID=" + bk.Id);

            return bk.Id;

        }

 

        static public void PrintBooksLinq(TestDataContext db)

        {

            var books = from b in db.Books

                        select b;

            foreach (var book in books)

            {

                Console.WriteLine("Book ID=" + book.Id + " Title=" + book.Title);

            }

        }

        static public void DeleteBooksLinq(TestDataContext db)

        {

            var books = from b in db.Books

                        select b;

            db.Books.DeleteAllOnSubmit<Book>(books);

            db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);

        }

 

        static void Main(string[] args)

        {

            string connString =

                "Data Source=(local);Initial Catalog=Test;Integrated Security=SSPI";

             TestDataContext db = new TestDataContext(connString);

           

            PrintBooksLinq(db);

            for (int i = 0; i < 5; i++)

            {

                AddBookLinq("LinqToSql" + i, db);

            }

            PrintBooksLinq(db);

            DeleteBooksLinq(db);

           

            Console.ReadLine();

            DeleteBooksLinq(db);

        }

    }

}

REFERENCES

NEWSEQUENTIALID() (Transact-SQL) function

    http://msdn.microsoft.com/en-us/library/ms189786.aspx

Using uniqueidentifier Data

    http://msdn.microsoft.com/en-us/library/ms190215

OUTPUT Clause (Transact-SQL) function

    http://msdn.microsoft.com/en-us/library/ms177564.aspx

DISCLAIMER

MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.

Properties

Article ID: 969200 - Last Review: March 18, 2009 - Revision: 1.1
APPLIES TO
  • Microsoft .NET Framework 3.5 Service Pack 1
  • Microsoft SQL Server 2005 Compact Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Mobile Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
Keywords: 
kbrapidpub kbnomt KB969200

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com