C# / MS SQL Get inserted value of NEWSEQUENTIALID()

23. February 2011 18:22

Here is a quick guide to get the new value of NEWSEQUENTIALID() when inserting a row into a table in ms sql. If you have attempted to use NEWSEQUENTIALID() before you will know that it is only possible to use as a generated value in the table when the row is being inserted. This is a guide to get this value from c# when inserting the row. For this method we are going to be using the OUTPUT clause in the INSERT statement.

We will start with a really basic table in ms sql. Runt he following to create it.

 

CREATE TABLE ATable (
      ATableID uniqueidentifier DEFAULT NEWSEQUENTIALID(),
      Data varchar(64)
) 
GO

To insert a row we need some simple sql. Except in this case while we are inserting the row we can also output the value that is created during the insert from ms sql. Some sample sql todo this is below.

 

INSERT INTO ATable (Data) OUTPUT inserted.ATableID
       VALUES('Test Data')

 

So if you run that you will be returned some results. For my example I got 'DCAEFDBD-793F-E011-822D-003067017B97' back in the results pane.

 

Todo this in c# it will work much the same. Though to get the value back you use the ExecuteScalar command on the SqlCommand. An example is below.

 

class Program
{
    static void Main(string[] args)
    {
        DBConn Conn = new DBConn("Data Source=SQL;Initial Catalog=Testing;Integrated Security=True;Connect Timeout=30;Timeout=120");
        SqlCommand sql = new SqlCommand(@"
            INSERT INTO ATable (Data) OUTPUT INSERTED.ATableID
                VALUES(@Value)
        ");

        for (int i = 0; i < 10; i++)
        {
            sql.Parameters.Clear();
            sql.Parameters.AddWithValue("@value", i.ToString());
            string tmp = Conn.ExecuteScalar(sql).ToString();
            Console.WriteLine(tmp);
        }

        Console.ReadLine();
    }
}

 

This nice thing about this when being used is that it will help prevent index fragmentation and page splits occuring on the index while is using the unique identifier. So the sample output of the Table will be as follows.

 

E5AEFDBD-793F-E011-822D-003067017B97 0
E6AEFDBD-793F-E011-822D-003067017B97 1
E7AEFDBD-793F-E011-822D-003067017B97 2
E8AEFDBD-793F-E011-822D-003067017B97 3
E9AEFDBD-793F-E011-822D-003067017B97 4
EAAEFDBD-793F-E011-822D-003067017B97 5
EBAEFDBD-793F-E011-822D-003067017B97 6
ECAEFDBD-793F-E011-822D-003067017B97 7
EDAEFDBD-793F-E011-822D-003067017B97 8
EEAEFDBD-793F-E011-822D-003067017B97 9

 

 

E-mail Kick it! DZone it! del.icio.us Permalink


Comments (1) -

2/24/2011 9:52:12 AM #

Exactly what I was looking for.

Thanks a lot Stev

Cheers
Chris

Christopher Lawson |

Pingbacks and trackbacks (2)+