Retrieving UniqueIdentifier with SqlDataReader

Question

I am trying to read a column type 'uniqueidentifier' in SQL with C#

SqlCommand myCommand = new SqlCommand();

myCommand.CommandText = "SELECT templatename, subject, bodyhtml, sender, emailTemplateBodyFields.fieldid FROM emailTemplates left join emailtemplaterecipients on emailtemplates.emailtemplateid = emailtemplaterecipients.emailtemplateid left join emailTemplateBodyFields on emailtemplates.emailtemplateid = emailTemplateBodyFields.emailtemplateid WHERE emailtemplates.emailtemplateid = " + selectedEmailTemplateID;

myCommand.Connection = connection;
SqlDataReader myReader = myCommand.ExecuteReader();

while (myReader.Read())
{
    NewTemplateName = myReader.GetString(0);
    NewSubject  = myReader.GetString(1);
    NewBodyHTML = myReader.GetString(2);
    NewRecipients = myReader.GetString(3);
    myRecipientList.Add(NewRecipients);

    Guid tempGUID = myReader.GetGuid(4);
    NewBodyFields = Convert.ToString(tempGUID);
    myBodyList.Add(NewBodyFields);

However I am getting a null value exception, data is null on

Guid tempGUID = myReader.GetGuid(4);

When I run the statement in SQL Server, the column has no null values.

Please advise how to retrieve info from this column.

Thanks.

score 3 · Answer 1 · 2012-11-15T11:14:21.437

3

You're using a LEFT JOIN to emailTemplateBodyFields. That can result in null values in the result set if no corresponding record exists in that table, even if fieldid is never null for the records that do exist in the table. You can check the result of IsDBNull: you will see that it returns true for those rows for which you get an exception.

To fix it, either rework your query to make sure you get no null values, or be prepared to handle null values by checking IsDBNull before calling GetGuid.

Two side comments on your code:

Does selectedEmailTemplateID come from user input? What if a malicious user decides to set to "1; DROP TABLE emailTemplates"? What will your query then do? Use parameters.
Make sure to release your resources. The using keywords makes this so easy that there is really no excuse here not to.

edited Nov 15 '12 at 11:14

answered Nov 15 '12 at 11:08

Thanks if (!myReader.IsDBNull(4)) does remove the error, but I can't see why its saying there are null values, when running it in sql server provides data. and no, not from user input, from a selection. This will only be used internally – Wayneio Nov 15 '12 at 11:21
@Wayneio Perhaps a silly question, but are you running the *exact* same query (including the same filter), on the same database, and are you checking all rows in the result set? – Nov 15 '12 at 11:26
yes copy and paste exactly with the same emailtemplateid. I think it could be because the column is 'uniqueidentifier' as i have no problem with the other rows stored as strings – Wayneio Nov 15 '12 at 11:28
I use `uniqueidentifier` heavily without problems with Entity Framework, which uses `SqlDataReader` behind the scenes, so I doubt that that is the source of your problems. However, it should be fairly easy to test, if you like: on a test database, change `fieldid`'s type, change your code to call `GetString`, and see what happens. – Nov 15 '12 at 11:50

Retrieving UniqueIdentifier with SqlDataReader

1 Answers1