Retrieving Guids from SQL Server - Part 2 (Complete example)

Question

Hi there, here I present a complete example of what I'm trying to do. You can just copy and paste and run it yourself.

1. Run the following sql script:

if object_id('remove_me', 'U') is not null
    drop table remove_me


create table remove_me
(
    Name nvarchar(20) not null
    , ID guid not null
)

insert into
    remove_me
values
    ('hello',newid())

select * from remove_me

I have something like in my output:

hello 0xD2509D0BDD06D147AA9474B252FB8DA2

2. Now use the following c# program:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ConsoleTest
{
    class Program
    {
        static string GetConnectionString(string DBServer, string Database = "", bool UseWindowsAuthentication = true, string UserName = "", string Password = "")
        {
            string ConnectionString = string.Empty;
            string Server = " Data Source =" + DBServer + ";";
            string TimeOut = " Connection Timeout=500;";

            ConnectionString += Server + TimeOut;

            if (UseWindowsAuthentication)
            {
                string IntegratedSecurity = "Integrated Security=True;";

                ConnectionString += IntegratedSecurity;
            }
            else
            {
                if (UserName == string.Empty || Password == string.Empty)
                {
                    throw new Exception("UserName or Password cannot be empty.");
                }

                string User = " user id=" + UserName + ";";
                string Pwd = " password=" + Password + ";";

                ConnectionString += User + Pwd;
            }

            if (Database != string.Empty)
            {
                ConnectionString += "Initial Catalog = " + Database + ";";
            }

            return ConnectionString;
        }
        
        static string RetrieveData_2(string DBServer, string Database, string Query)
        {
            string Error = string.Empty;

            SqlConnection Connection = null;

            try
            {
                Connection = new SqlConnection(GetConnectionString(DBServer));
                Connection.Open();

                string UseQuery = "Use \"" + Database + "\"";
                SqlCommand Command = new SqlCommand(UseQuery, Connection);
                Command.ExecuteScalar();

                Command = new SqlCommand(Query, Connection);
                Command.CommandTimeout = 5000;

                SqlDataReader reader = Command.ExecuteReader();

                while (reader.Read())
                {
                    string name = reader[0].ToString();
                    Guid g = new Guid(reader["ID"] as byte[]);
                }

            }
            catch (Exception ex)
            {
                Error = ex.Message;
            }
            finally
            {
                Connection.Close();
            }


            return Error;
        }
        
        static void Main(string[] args)
        {
            string Query = "select * from remove_me";

            RetrieveData_2("your_server", "your_db", Query );
        }
    }
}

please note you need to fill in the server and database names. Your want to put a breakpoint 

My guid object is like this:

+ g {0b9d50d2-06dd-47d1-aa94-74b252fb8da2} System.Guid

Clearly both guids don't match.

Can someone tell me what I'm doing wrong?

Thanks,

Christian

Answer 1

Hi, 

The type that represents GUID in sqlserver is as uniqueidentifier

guid value can be represent in 2 formats

• Character string
• Binary   

The format you are showing above from sqlserver query is in Binary format of actual GUID generated.

more info on this from http://msdn.microsoft.com/en-us/library/ms190215.aspx

From c# code you are casting to  GUID, which is this line,

 Guid g = new Guid(reader["ID"] as byte[]);

So you are seeing actual Guid.

I hope this helps you to understand..

---

If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

Answer 2

Thanks for your hints. I think I understand now. I'm interested in the guid as hexadecimal string which the Guid data type in sql represents. uniqueidentifier seems to store a guid as a string. 

So I need to convert a guid string into the hexadecimal version.

Guid g = new Guid(reader["ID"] as byte[]);
string gb = BitConverter.ToString(g.ToByteArray()).Replace("-", string.Empty);

Thanks again.

Answer 3

Hi,

It's a classic example that I found.