CODEWITHSUNDEEP
c#sqlsql-serverguiduniqueidentifier
Wayneio
Wayneio

Reputation: 3576

Retrieving UniqueIdentifier with SqlDataReader

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.

Upvotes: 0

Views: 2943

Answers (1)

user743382
user743382

Reputation:

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.

Upvotes: 3

Related Questions