public static void write_to_edge(string connestring, string desttableName, DataSet ds, string CloudSourceTable) { /// The function takes a data source and writes it to a SQL Table using a stored procedure. the Stored procedure uses the MERGE statement to identify the exisiting records to update. /// // First parse the dataset to get the current version number long currnet_version = (Int64)ds.Tables[0].Rows[0]["Current_Sync_Version"]; // Pass the second ds as a parameter to a SQL Stored Procedure. using (SqlConnection connection = new SqlConnection(connestring)) { try { SqlCommand sql_cmnd = new SqlCommand("dbo.WriteChangesToTable", connection); sql_cmnd.CommandType = CommandType.StoredProcedure; sql_cmnd.Parameters.AddWithValue("@tableName", SqlDbType.NVarChar).Value = desttableName; sql_cmnd.Parameters.AddWithValue("@tableInput", ds.Tables[1]); connection.Open(); int result = (Int32)sql_cmnd.ExecuteNonQuery(); connection.Close(); ////Insert an entry into the SQLite Database to record the last_sync Version and the Source Table Name. SQLiteDatabaseOperations.InsertRecords(currnet_version, "AzureSQLDatabase", CloudSourceTable); Console.WriteLine("Completed Writing the Data to the SQL Edge Database"); } catch (SqlException e) { Console.WriteLine(e.Message); } } }
static void Main(string[] args) { Console.WriteLine("Hello World!"); SQLiteDatabaseOperations.CreateDatabaseAndTable(); //// Sample Connection Strings //string sqlEdgeConnString = "Server = xxx.xxx.xxx.xxx,1433; Database = DataMovementTest; UID = sa; Pwd = MyStrongSQLP@a$$worD"; ; //string sqlDBConnString = "Server = mysqlserver.database.windows.net; Database = Doctestdb; UID = azuresqlAdmin; Pwd = MyStrongSQLP@a$$worD"; string sqlEdgeConnString = "";; string sqlDBConnString = ""; int ReadWriteFrequency = 1; /// Read/Write interval in minutes. /// Thread Allocations /// In the below code example, each source, target sync has been implemented as a separate thread. /// If more source/target Pairs needs to be synchronized, then add a thread each of the sync pair. /// var tasks = new List <Task>(2); /// Task1 - Cloud to Edge Sync for Cloud Source Table 1 and Target Edge Table 1 /// SYNTAX -> cloud_to_edge(string sqldbConn, string cloudSourceTable, string edgeconn, string EdgeDestTable, int FrequencyInMinutes) tasks.Add(Task.Factory.StartNew(() => SQLOperations.cloud_to_edge(sqlDBConnString, "TelemetryData", sqlEdgeConnString, "TelemetryDataTarget", ReadWriteFrequency))); // Task2 - Edge to Cloud Sync for Edge Source Table 2 and Target Cloud Table 2 /// SYNTAX -> edge_to_cloud(string edgeconn, string edgeSourceTab, string sqldbConn, string cloudDestTable, int FrequencyInMinutes) tasks.Add(Task.Factory.StartNew(() => SQLOperations.edge_to_cloud(sqlEdgeConnString, "TelemetryData", sqlDBConnString, "TelemetryDataTarget", ReadWriteFrequency))); Task.WaitAll(tasks.ToArray()); }
public static DataSet read_from_edge(string edgeconn, string edgeSourceTab) { /// function to read changes (updated records) from the source table on SQL Edge. /// The function uses a stored procedure to read the data from a SQL Database. The logic in the Stored Procedure can be user conrolled. /// For the purposes of the sample, the Stored procedure will consume the delta changes using the Change Tracking feature in SQL. /// DataSet ds = new DataSet(); if (edgeconn != "" || edgeSourceTab != "") { //check if the table has change tracking enabled and then get the min_valid_version for the table. long min_version = GetMinValidCTVersionsFromSQL(edgeconn, edgeSourceTab); if (min_version != -1) { ///Read the last Sync Value For the Source Table. /// long last_sync_version = SQLiteDatabaseOperations.SelectLastSyncVersion("AzureSQLEdge", edgeSourceTab); try { try { try { using (SqlConnection connection = new SqlConnection(edgeconn)) { //Read Data from SQL Edge using the Predefined Stored Procedure. /// This example used a SP, to minimize the usage of T-SQL constructs in the code. /// SqlCommand sql_cmnd = new SqlCommand("GetChangesFromTable", connection); sql_cmnd.CommandType = CommandType.StoredProcedure; sql_cmnd.Parameters.AddWithValue("@TableName", SqlDbType.VarChar).Value = edgeSourceTab; sql_cmnd.Parameters.AddWithValue("@last_sync_version", SqlDbType.BigInt).Value = last_sync_version; connection.Open(); SqlDataAdapter da = new SqlDataAdapter(sql_cmnd); da.Fill(ds); da.Dispose(); /// Convert this Database to a Json Object. /// This JSON will be used to sync data to the targets /// connection.Close(); Console.WriteLine("Completed Reading the Data from the SQL Edge Database"); } } catch (SqlException e) { Console.WriteLine(e.Message); } catch (System.ArgumentException ae) { Console.WriteLine($"Error Establishing connection to SQL to read the data. Reason: {ae.Message}"); } } catch (Exception e) { Console.WriteLine(e.Message); } } catch (Exception e) { Console.WriteLine(e.Message); } } else { Console.WriteLine("There was either an error reading the MIN VALID LSN or the value returned was NULL"); } } else { Console.WriteLine("The Connection String to SQL Edge or the Source Table name is incorrect"); } return(ds); }