How to get Guid from SqlDataReader uniqueidentifier

6

SQL Server 2008 (version 10.50.2550).

I have a select query to return a single column of type uniqueidentifier.

I want to get this into a C# variable of type Guid.

All of the following methods result in exceptions.

reader is of type SqlDataReader.

using (var reader = command.ExecuteReader())
{

    if (reader.Read())
    {       
        Guid guid = reader.GetGuid(reader.GetOrdinal("integ_schemehistoryId")); //1

        Guid guid = Guid.Parse((string)reader["integ_schemehistoryId"]); //2

        Guid guid = (Guid)reader["integ_schemehistoryId"]; //3

        Guid guid = new Guid((string)reader["integ_schemehistoryId"]); //4

        Guid guid = Guid.Parse(reader["integ_schemehistoryId"].ToString()); //5
    }

}

Error for 1:

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at Simego.DataSync.DynamicColumns.DataSourceRowOverride.get_EpochSchemeHistoryID()

Error for 2:

System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'.
   at Simego.DataSync.DynamicColumns.DataSourceRowOverride.get_EpochSchemeHistoryID()

Error for 3:

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at Simego.DataSync.DynamicColumns.DataSourceRowOverride.get_EpochSchemeHistoryID()

Error for 4:

System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'.
   at Simego.DataSync.DynamicColumns.DataSourceRowOverride.get_EpochSchemeHistoryID()

Error for 5:

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at Simego.DataSync.DynamicColumns.DataSourceRowOverride.get_EpochSchemeHistoryID()
c#
sql-server
sql-server-2008
data-migration
asked on Stack Overflow Jul 19, 2013 by cja • edited Jul 22, 2013 by cja

7 Answers

8

There is something wrong either with your data or your SQL. The first and third approaches should work - personally I'd use the first form, as it's the clearest in my view.

But look at the stack trace:

... at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at Simego.DataSync.DynamicColumns.DataSourceRowOverride.get_EpochSchemeHistoryID()

Note that it's the Read() call which is failing, not GetGuid or the indexer.

My guess is that your property is being fetched several times, and sometimes it works - which is why you were getting a cast exception in your second approach - but for some rows, it fails due to some problem with the data or the SQL. As we've no idea where your data is coming from, we can't help you beyond that diagnosis, but that's where you should look next.

answered on Stack Overflow Jul 19, 2013 by Jon Skeet
2

That error is being generated from SQL. In other words there something wrong with you SQL command text. Not your c# code.

answered on Stack Overflow Jul 19, 2013 by Moose
1

As the other answers suggest, you should try:

Guid guid = Guid.Parse(reader["integ_schemehistoryId"].ToString())

You might also want to check your stored procedures and your table, there may be something wrong there.

answered on Stack Overflow Jul 19, 2013 by goelze
0

Have you tried:

Guid guid = new Guid((string)reader["integ_schemehistoryId"]);
answered on Stack Overflow Jul 19, 2013 by welegan
0

Try by using Guid.Parse method

Guid guid = return Guid.Parse((string)reader["integ_schemehistoryId"]);
answered on Stack Overflow Jul 19, 2013 by Nikola Mitev
0

In Oracle database:

"ID" RAW(16) DEFAULT SYS_GUID() NOT NULL ENABLE, 

This code worked for me:

using (IDataReader dr = ... )
    {
        while (dr.Read())
        {
            var Id = new Guid();
            if (!(dr["ID"] is DBNull)){
                Id = new Guid((byte[])dr.GetValue(dr.GetOrdinal("ID")));
            }                   
        }
        dr.Close();
    } 
answered on Stack Overflow Feb 15, 2021 by nandox • edited Feb 15, 2021 by nandox
-1
    object val = reader[0];
    Guid g;
    if ( Guid.TryParse(val, out g) )
    {
        return g;
    } 
    else 
    {
        Trace.WriteLine("Error: " + g == null ? string.Empty : g.ToString());   
    }
answered on Stack Overflow Jul 19, 2013 by Sean Cleaver

User contributions licensed under CC BY-SA 3.0