CODEWITHSUNDEEP
c#sql-serversql-server-2008data-migration
cja
cja

Reputation: 10026

How to get Guid from SqlDataReader uniqueidentifier

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()

Upvotes: 7

Views: 23535

Answers (8)

Aleksei Beliaev
Aleksei Beliaev

Reputation: 1

In my case with .Net and MSSQL Server this code helped me to get the data and to avoid exceptions:

Guid guid = reader.IsDBNull(reader.GetOrdinal("integ_schemehistoryId")) ?
     (Guid?)null : reader.GetGuid(reader.GetOrdinal("integ_schemehistoryId"))

Upvotes: 0

nandox
nandox

Reputation: 101

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();
    }

Upvotes: 0

Sean Cleaver
Sean Cleaver

Reputation: 825

    object val = reader[0];
    Guid g;
    if ( Guid.TryParse(val, out g) )
    {
        return g;
    } 
    else 
    {
        Trace.WriteLine("Error: " + g == null ? string.Empty : g.ToString());   
    }

Upvotes: -1

Jon Skeet
Jon Skeet

Reputation: 1502546

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.

Upvotes: 8

Moose
Moose

Reputation: 141

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

Upvotes: 2

goelze
goelze

Reputation: 508

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.

Upvotes: 3

Nick
Nick

Reputation: 4212

Try by using Guid.Parse method

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

Upvotes: 0

welegan
welegan

Reputation: 3043

Have you tried:

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

Upvotes: 0

Related Questions