How to return uniqueidentifier initialized with NEWSEQUENTIALID() function to the .Net client
Problem statement:
The uniqueidentifier SQL Server data type is increasingly used in applications that require global uniqueness of the primary key. In SQL Server 2005 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 NEWID() function, the NEWSEQUENTIALID() cannot be used in queries. 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 .Net application.
Solution:
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 scenario, 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.
Detailed information:
Consider following table definition:
1: create table Books (Id uniqueidentifier Default NEWSEQUENTIALID()
2: NOT NULL
3: CONSTRAINT PK_Books PRIMARY KEY CLUSTERED,
4: 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:
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Text;
5: using System.Data;
6: using System.Data.SqlClient;
7:
8: namespace Client
9: {
10: class Program
11: {
12: static public Guid AddBook(string title, string connString)
13: {
14: Guid ID = Guid.Empty;
15: string sql =
16: "INSERT INTO Books (Title) Output inserted.Id VALUES (@Title); ";
17:
18: using (SqlConnection conn = new SqlConnection(connString))
19: {
20: SqlCommand cmd = new SqlCommand(sql, conn);
21: cmd.Parameters.Add("@Title", SqlDbType.VarChar);
22: cmd.Parameters["@title"].Value = title;
23: try
24: {
25: conn.Open();
26: ID = (Guid)cmd.ExecuteScalar();
27: Console.WriteLine("Added book " + title + " with ID=" + ID.ToString("D"));
28: }
29: catch (Exception ex)
30: {
31: Console.WriteLine(ex.Message);
32: }
33: }
34: return (Guid)ID;
35: }
36:
37:
38: static void Main(string[] args)
39: {
40: string connString =
41: "Data Source=(local);Initial Catalog=Test;Integrated Security=SSPI";
42:
43: for (int i = 0; i < 10; i++)
44: {
45: string title = "Encyclopedia Volume " + i;
46: Guid id = AddBook(title, connString);
47: }
48: Console.ReadLine();
49: }
50: }
51: }
52:
.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:
1: [Column(Storage="_Id", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]
2: public System.Guid Id
Following code fragment demonstrates the insertion of the Linq Book entities
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Text;
5:
6: namespace LinqClient
7: {
8: class Program
9: {
10: static public Guid AddBookLinq(string title, TestDataContext db)
11: {
12: Book bk = new Book();
13: bk.Title = "LINQ to SQL";
14: db.Books.InsertOnSubmit(bk);
15: db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
16: Console.WriteLine("Linq Added book " + bk.Title + " with ID=" + bk.Id);
17: return bk.Id;
18: }
19:
20: static public void PrintBooksLinq(TestDataContext db)
21: {
22: var books = from b in db.Books
23: select b;
24: foreach (var book in books)
25: {
26: Console.WriteLine("Book ID=" + book.Id + " Title=" + book.Title);
27: }
28: }
29: static public void DeleteBooksLinq(TestDataContext db)
30: {
31: var books = from b in db.Books
32: select b;
33: db.Books.DeleteAllOnSubmit<Book>(books);
34: db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
35: }
36:
37: static void Main(string[] args)
38: {
39: string connString =
40: "Data Source=(local);Initial Catalog=Test;Integrated Security=SSPI";
41: TestDataContext db = new TestDataContext(connString);
42:
43: PrintBooksLinq(db);
44: for (int i = 0; i < 5; i++)
45: {
46: AddBookLinq("LinqToSql" + i, db);
47: }
48: PrintBooksLinq(db);
49: DeleteBooksLinq(db);
50:
51: Console.ReadLine();
52: DeleteBooksLinq(db);
53: }
54: }
55: }
Applies to:
- .Net Framework 3.5 SP1
- VS 2008 SP1
- SQL Server 2005 or SQLServer 2008
References:
“NEWSEQUENTIALID() (Transact-SQL) “
https://msdn.microsoft.com/en-us/library/ms189786.aspx
“Using uniqueidentifier Data”
https://msdn.microsoft.com/en-us/library/ms190215
“OUTPUT Clause (Transact-SQL)”
https://msdn.microsoft.com/en-us/library/ms177564.aspx
Comments
- Anonymous
April 11, 2012
The comment has been removed - Anonymous
August 04, 2014
Brilliant! The only solution on the net!