7

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()
cja
  • 9,512
  • 21
  • 75
  • 129
  • What error do you get? – SLaks Jul 19 '13 at 15:14
  • 2
    `GetGuid` should work. I would advise *against* any approach which performs two string conversions unnecessarily. Please give details of the error you get when calling `GetGuid`. – Jon Skeet Jul 19 '13 at 15:14
  • @JonSkeet Which does that? – cja Jul 19 '13 at 15:17
  • 2
    Anything using `Guid.Parse`. I'd expect your last attempt to be okay too. *Any* time you say "I get errors" or "it doesn't compile" in a Stack Overflow question, you should specify what those errors are. As it is, it looks like the first and last versions should be fine. – Jon Skeet Jul 19 '13 at 15:18
  • @JonSkeet see edit for error for the last version – cja Jul 19 '13 at 15:25
  • 2
    @cja: You said they were *compile-time* errors. Those are exceptions, which occur as *execution-time*. *Please* be more careful about being precise in your information... you've wasted everyone's time chasing mythical compile-time failures. – Jon Skeet Jul 19 '13 at 15:37
  • Now added errors for all three methods, and corrected the second method @JonSkeet – cja Jul 19 '13 at 15:37
  • 1
    I'm now very confused about how *both* errors 2 and 3 can occur. My guess is that they occur on different rows... I'm not surprised casting to `string` fails, but given that the cast fails *saying it's a GUID*, it's unclear why the cast to `Guid` would fail. – Jon Skeet Jul 19 '13 at 15:40
  • @JonSkeet This is true. My IDE is not Visual Studio and it is a little confusing. – cja Jul 19 '13 at 15:40
  • @cja: Regardless of IDE, you should be able to tell the difference between "failing to compile" and an exception. Do either error 1 or error 3 have a nested exception at all? It looks like somehow you've got bad data, but it's hard to say for sure. – Jon Skeet Jul 19 '13 at 15:41
  • @JonSkeet I can't tell whether there are nested exceptions using my development environment. (It's Simego Data Synchronisation Studio.) – cja Jul 19 '13 at 15:58
  • Does this work? `Guid guid = Guid.Parse(reader["integ_schemehistoryId"].ToString());` – Lynn Crumbling Jul 19 '13 at 20:42
  • @LynnCrumbling No. See latest edit. – cja Jul 22 '13 at 11:51
  • @cja I think the stack trace for #5 is wrong; I don't see a call to Guid.Parse() in there anywhere. I would have expected a FormatException. Instead there's some sort of SqlException listed, which has nothing to do with Guid.Parse(). One last thought: if the stack trace is correct, are you sure that you're executing the code that you think you're executing? – Lynn Crumbling Jul 22 '13 at 16:55
  • @LynnCrumbling As written elsewhere on this page, I'm not using Visual Studio - it's a data migration tool that allows me to write C# to massage data. So I can't be totally sure, but as far as I can tell, yes. – cja Jul 23 '13 at 08:35
  • @JonSkeet Do you agree with my assertion that the stack trace seems wrong for #5? Even if it's not under VS, it's c#, so we should be seeing a `FormatException` instead of a `SqlException`, no? – Lynn Crumbling Jul 23 '13 at 15:28
  • @cja It's almost like you re-ran the code for #3 again. The stack trace is identical. (Not accusing you of that; just pointing it out.) – Lynn Crumbling Jul 23 '13 at 15:32
  • @LynnCrumbling: No - look at the stack trace carefully; the problem is in `Read`. I believe the code is reading the data with a known schema, and converting it to a GUID at *that* point. It's not getting as far as calling `Guid.Parse`. – Jon Skeet Jul 23 '13 at 16:47
  • @JonSkeet Oh, excellent point... and one which you made, in your answer, now that I've re-read it. For grins (and assuming he has the capability), he could try changing the commandtext of the SqlCommand to include a CAST to VARCHAR(38) to see if it gets him past that line of code... – Lynn Crumbling Jul 23 '13 at 17:02
  • @cja Can you trying changing your `SqlCommand.CommandText` to wrap the uniqueidentifier column in a `CAST('uniqueidfield' AS varchar(38))` to see if you get past the `Reader.Read()`? – Lynn Crumbling Jul 23 '13 at 17:04
  • @LynnCrumbling I'm way past this problem now, but thanks for the thought – cja Jul 24 '13 at 09:20
  • @cja Sure thing... glad you have a solution – Lynn Crumbling Jul 25 '13 at 21:43

7 Answers7

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.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
3

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.

goelze
  • 488
  • 1
  • 3
  • 14
2

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

Moose
  • 141
  • 3
0

Have you tried:

Guid guid = new Guid((string)reader["integ_schemehistoryId"]);
welegan
  • 3,013
  • 3
  • 15
  • 20
  • Yes, see the question. (Maybe you posted this before I edited the question.) – cja Jul 19 '13 at 15:58
  • 1
    I don't think you have. `new Guid(...` uses the constructor, your question states that you've tried the static method `Guid.Parse`, the `SQLDataReader` method `.GetGuid` and directly casting to `(Guid)` from the object in `Reader`, none of the 3 cases use `new`, which is the constructor. – welegan Jul 19 '13 at 16:06
  • Sorry, you're right. I get: System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'. at Simego.DataSync.DynamicColumns.DataSourceRowOverride.get_EpochSchemeHistoryID() – cja Jul 22 '13 at 11:23
0

Try by using Guid.Parse method

Guid guid = return Guid.Parse((string)reader["integ_schemehistoryId"]);
Nick
  • 4,192
  • 1
  • 19
  • 30
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();
    } 
nandox
  • 81
  • 5
-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());   
    }
Sean Cleaver
  • 805
  • 6
  • 4