SYNC - .NET C#¶

/// <summary>
/// Get changes from server.
/// </summary>
/// <param name="subscriberId">identifier of subscriber</param>
private void GetChangesFromServer(string subscriberId)
{
    using (SQLiteConnection conn = new SQLiteConnection(this.connString))
    {
        using (SQLiteCommand cmd = new SQLiteCommand())
        {
            cmd.Connection = conn;
            conn.Open();

            SQLiteHelper sh = new SQLiteHelper(cmd);

            //get list of tables in local database
            DataTable tables = sh.Select("select tbl_Name from sqlite_master where type='table';");

            foreach (DataRow table in tables.Rows)
            {
                try
                {
                    sh.BeginTransaction();

                    //get changes from server for current table
                    var request = new RestRequest("Sync/{subscriberUUID}/{tableName}", Method.GET);
                    request.AddUrlSegment("subscriberUUID", subscriberId);
                    request.AddUrlSegment("tableName", table["tbl_Name"].ToString());
                    request.AddHeader("Accept", "*/*");
                    IRestResponse response = wsClient.Execute(request);
                    List<DataObject> tablesData = JsonConvert.DeserializeObject<List<DataObject>>(response.Content);

                    //parse resposonse
                    foreach (DataObject tableData in tablesData)
                        if (tableData.SyncId > 0)
                        {
                            sh.Execute(tableData.TriggerDeleteDrop);
                            sh.Execute(tableData.TriggerInsertDrop);
                            sh.Execute(tableData.TriggerUpdateDrop);

                            XmlDocument xmlRecords = new XmlDocument();
                            xmlRecords.LoadXml(tableData.Records);
                            XPathNavigator oRecordsPathNavigator = xmlRecords.CreateNavigator();
                            XPathNodeIterator oRecordsNodesIterator = oRecordsPathNavigator.Select("/records/r");

                            foreach (XPathNavigator oCurrentRecord in oRecordsNodesIterator)
                            {
                                string action = oCurrentRecord.GetAttribute("a", "");
                                XmlDocument xmlRecord = new XmlDocument();
                                xmlRecord.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\"?><columns>" + oCurrentRecord.InnerXml + "</columns>");
                                XPathNavigator oColumnsPathNavigator = xmlRecord.CreateNavigator();
                                XPathNodeIterator oColumnsNodesIterator = oColumnsPathNavigator.Select("/columns/c");
                                int coumnsCount = oColumnsNodesIterator.Count;

                                SQLiteParameter[] parameters = new SQLiteParameter[coumnsCount];
                                int idx = 0;
                                foreach (XPathNavigator oCurrentColumn in oColumnsNodesIterator)
                                {
                                    SQLiteParameter parameter = new SQLiteParameter();
                                    parameter.Value = oCurrentColumn.InnerXml;
                                    parameters[idx] = parameter;
                                    idx++;
                                }

                                switch (action)
                                {
                                    case "1"://insert
                                        sh.Execute(tableData.QueryInsert, parameters);
                                        break;
                                    case "2"://update
                                        sh.Execute(tableData.QueryUpdate, parameters);
                                        break;
                                    case "3"://delete
                                        sh.Execute(tableData.QueryDelete, parameters);
                                        break;
                                }
                            }

                            sh.Execute(tableData.TriggerDelete);
                            sh.Execute(tableData.TriggerInsert);
                            sh.Execute(tableData.TriggerUpdate);

                            //now we need to tell server that we successfully received changes
                            request = new RestRequest("CommitSync/{syncId}", Method.GET);
                            request.AddUrlSegment("syncId", tableData.SyncId.ToString());
                            request.AddHeader("Accept", "*/*");
                            IRestResponse responseCommit = wsClient.Execute(request);
                        }

                    sh.Commit();

                }
                catch (Exception ex)
                {
                    sh.Rollback();
                    throw ex;
                }
            }

            conn.Close();
        }
    }
}