How to Matching GUID from Database Sources with GUID from Queryexpression

Question

I have requirements to create scheduler every 30 min. This scheduler contain a program that will take the records created today (include the GUID) and then insert into Dynamics CRM using webservices. To take records from SQL Server Database i use this query:

SELECT * FROM new_crmtable WHERE DATEDIFF(day, CreatedOn, GetDate()) = 0

Because this shceduler, is run every 30 min. Everytime this program is execute, it's take whether the new records and older records created today. In example, a record named A created on 12.25 PM today. And the first time this program executed is on 12.30 PM. Then on 12.45 PM. A new record called B is created. When the program get executed again on 13.00 PM. This program take both records A and B. During create the records in Dynamics CRM, it's will give an error message. Because record A already created. The erros says that you can't create record with duplicate GUID (Remember that i also take GUID from SQL Server Database, and insert into Dynamics CRM with its GUID)

To avoid errors, i need to check whether the records are already created on CRM or not. To get GUID from Dynamics CRM, i use QueryExpression. This is my code:

try
            {
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        try
                        {
                            Entity rs = new Entity("new_troubleticket");


                            OptionSetValue option = new OptionSetValue(100000001);
                            rs.Attributes.Add("new_systemsource", option);

                            Guid ticketid = new Guid(dt.Rows[i][0].ToString());
                            rs.Id = ticketid;


                            //FIRSTNAME
                            if (!string.IsNullOrEmpty(dt.Rows[i][1].ToString()))
                            {
                                rs["new_subject"] = dt.Rows[i][1].ToString();


            }
                                else
                                {
                                    rs["new_subject"] = null;
                                }

Queryexpression query = new QueryExpression { Entityname = "new_customentity", 
Columnset = new Columnset("new_customentityid")};
query.Criteria.AddCondition("CreatedOn", ConditionalOperator.Equal, Datetime.Now);

Entity Collection result = service.RetrieveMultiple(query)

Cant you tell me how to matching GUID from SQL Server Database, with GUID from Queryexpression ?

Jordi Jordi · Accepted Answer · 2016-08-19T08:16:43

Just add a condition to filter by the id:

var g = Guid.NewGuid() //Replace this with your actual Id from the query
Queryexpression query = new QueryExpression { Entityname = "new_customentity", 
Columnset = new Columnset("new_customentityid")};
query.Criteria.AddCondition("new_customentityid", ConditionalOperator.Equal, g);

Entity Collection result = service.RetrieveMultiple(query)

How to Matching GUID from Database Sources with GUID from Queryexpression

1 Answers