none
Retrieving GUID from INSERT query -- HELP RRS feed

  • Question

  • I have tried many code sample but I am very stuck

    The primary key of my database (SQL server 2005) table is a uniqueidentifier.                 


    I am using the following code to insert a row into my table:

    myCommand.CommandText = sqlEvent.ToString();    //add the sql query to the command
    myCommand.Connection = this.dbConnection;    //add the database connection to the command
    myCommand.ExecuteNonQuery();     //execute the insert query


    I need to retrieve the GUID that is automatically generated when the insert command is executed.

    Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using

    string _id = (string)myCommand.ExecuteScalar();

    and I am still stuck.  I will really appreciate it if someone can refer me to some code sample.


    HELP
    Monday, August 27, 2007 2:04 PM

All replies

  • You can use ExecuteNonQuery or ExecuteScalar for that purpose. But to get Guid you will need to do something in the command, so for type of command to do the right thing. Here is how:

    ExecuteNoneQuery:

    In order to return new id generated in the command only why is to have output parameter. So your id will be a parameter in the command and of type OutputParameter. In the commant you must set the value of this parameter like this:

    SET @MyId = NEWID()

    you will use this variable in the insert statement. You will also use this parameter after the command is executed to read new guid generated.

    ExecuteScalar:
    For this also you need to configure your command so as last statement you will have RETURN with value for new id.
    So in this case parameter can be set inside command and with same functionality as previous example:

    DECLARE @MyID UNIQUEIDENTIFIER
    SET @MyId = NEWID()
    --use in the insert
    ....
    ....

    RETURN @MyId   
        --Or
    SELECT @MyId


    The best and easier is to create stored procedure for insert where you will implement these recomendations.

    Monday, August 27, 2007 2:19 PM
  • I've always avoided output parameters because I've seen strange behaviour in the past, and there are also a number of gotchas (particularly when returning multiple result sets). However what you're doing is simple enough that you're unlikely to have problems.

     

    Anyway the upshot is that I personally use the second method boban described.

     

    Sean

     

    Tuesday, August 28, 2007 8:54 PM