static void Main(string[] args) { //Set the default values string SQLConnectionString = "Server = <server_name>; Database = <database_name> UID = <user_name>; Pwd = <password>"; // Connection string for the SQL Edge Instance. string SourceTableName = "<table_name>"; //Source Table Name string EventHubConnectionString = ""; // Connection String to the Event Hub string EventHubName = ""; // Name of the Event hub int ReadWriteFrequency = 1; /// Read/Write interval in minutes. // Read Environment Variables. if (Environment.GetEnvironmentVariable("SQLConnectionString") != null) { SQLConnectionString = Environment.GetEnvironmentVariable("SQLConnectionString").Trim('"'); } if (Environment.GetEnvironmentVariable("SourceTableName") != null) { SourceTableName = Environment.GetEnvironmentVariable("SourceTableName").Trim('"'); } if (Environment.GetEnvironmentVariable("EventHubConnectionString") != null) { EventHubConnectionString = Environment.GetEnvironmentVariable("EventHubConnectionString"); } if (Environment.GetEnvironmentVariable("EventHubName") != null) { EventHubName = Environment.GetEnvironmentVariable("EventHubName"); } if (Environment.GetEnvironmentVariable("ReadWriteFrequencyInMiutes") != null) { ReadWriteFrequency = Convert.ToInt32(Environment.GetEnvironmentVariable("ReadWriteFrequencyInMiutes")); } //Initializing the SQLLite Database. If it exists, reload it. SQLiteDatabaseOperations.CreateDatabaseAndTable(); var tasks = new List <Task>(3); ///Get the Changed data from SQL Edge /// //await Miscellaneouss.ReadDataSQLEdge(appsettings.SQLEdgeConnectionString, appsettings.TableName, Convert.ToInt32(appsettings.ReadWriteFrequencyInMs)); tasks.Add(Task.Factory.StartNew(() => Miscellaneouss.ReadDataSQLEdge(SQLConnectionString, SourceTableName, Convert.ToInt32(ReadWriteFrequency)))); ///Send the data to Event Hub /// tasks.Add(Task.Factory.StartNew(() => Miscellaneouss.WriteEventHub(EventHubConnectionString, EventHubName, Convert.ToInt32(ReadWriteFrequency)))); //await Miscellaneouss.WriteEventHub(appsettings.TargetConnectionString, appsettings.EventHubName, Convert.ToInt32(appsettings.ReadWriteFrequencyInMs)); /// Load the File Deletion Thread /// tasks.Add(Task.Factory.StartNew(() => Miscellaneouss.DeleteOldUploadedFiles())); Task.WaitAll(tasks.ToArray()); }
public static void DeleteOldUploadedFiles() { //Get the list of files to delete from the SQLite Database. while (true) { string[] filelist = SQLiteDatabaseOperations.GetFilesToDelete(); foreach (string filename in filelist) { if (File.Exists(filename)) { try { File.Delete(filename); SQLiteDatabaseOperations.DeleteEntryFromDatabase(filename); } catch (Exception e) { Console.WriteLine(e.Message); } } } Thread.Sleep(new TimeSpan(2, 0, 0)); } }
//Read Data from SQL and Generate an JSON for the Rows. // For the purpose of this demo, we are using Change Tracking feature in SQL // The function reads the net changes from the underlying table. public static Task ReadDataSQLEdge(string connectionString, string tablename, int ReadInterval) { // Check if the connection string is a valid connection string or not. DataSet ds = new DataSet(); string json; while (true) { if (connectionString != "" || tablename != "") { long min_version = GetMinValidCTVersionsFromSQL(connectionString, tablename); if (min_version != -1) { long last_sync_version = SQLiteDatabaseOperations.SelectLastSyncVersion(); try { try { try { using (SqlConnection connection = new SqlConnection(connectionString)) { /// Check the table is present in the database or not /// If present, check if Change tracking is enabled on the Table or not. /// string query = "Select count(*) as TableCount from sys.change_tracking_tables where object_id = object_id(N'" + tablename + "')"; SqlCommand sql_cmnd1 = new SqlCommand(query, connection); connection.Open(); int result = (Int32)sql_cmnd1.ExecuteScalar(); connection.Close(); if (result == 1) { Console.WriteLine($"The specified Table [{tablename}] is enabled for change tracking."); //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 = tablename; 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(); } else { Console.WriteLine("The specified table is not enabled for Changed Tracking"); } } } 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}"); } long currnet_version = (Int64)ds.Tables[0].Rows[0]["Current_Sync_Version"]; //Console.WriteLine($"Current Version from Database -> {currnet_version}"); // get the Current Sync Version from the record set. //System.Text.Json.JsonSerializer serializer = new JsonSerializer(); List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >(); Dictionary <string, object> row; foreach (DataRow dr in ds.Tables[1].Rows) { row = new Dictionary <string, object>(); foreach (DataColumn col in ds.Tables[1].Columns) { row.Add(col.ColumnName, dr[col]); } rows.Add(row); } ds.Clear(); ds.Dispose(); json = ""; json = System.Text.Json.JsonSerializer.Serialize(rows); ///create the json file for interim storage /// string filename = "ChangeTrackingData_" + Convert.ToString(last_sync_version) + "_" + DateTime.UtcNow.ToString(new CultureInfo("en-us")).Replace("/", " ").Replace(":", " ").Replace(" ", "_") + ".json"; File.WriteAllText(filename, json); ///Write the last synchronization details to the datastore.json file. /// //WriteLastSyncVersion(currnet_version, filename, "Created"); SQLiteDatabaseOperations.InsertRecords(currnet_version, filename); } 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"); } Thread.Sleep(new TimeSpan(0, ReadInterval, 0)); } }
public static async Task WriteEventHub(string eventhubconn, string eventhubname, int WriteInterval) { //using EventDataBatch eventBatch = await producerClient.CreateBatchAsync(); while (true) { if (eventhubconn == "") { Console.WriteLine("Exception - Event Hub Connection string is not Valid. "); break; } // this function will read the datastore.json file to first file (status = Created) which has not yet been Sent to the EventHub. // We will first read the json file and then convert it into a byte stream // which will then be used to send the events to the event hub. string filename = ""; filename = SQLiteDatabaseOperations.SelectNextUploadedFile(); if (filename != "" && File.Exists(filename)) { bool isConnected = CheckNetworkConnectivity(); if (isConnected) { Console.WriteLine("Network Connectivity Exists!! Proceeding with Data Sync"); await using (var producerClient = new EventHubProducerClient(eventhubconn, eventhubname)) { using EventDataBatch eventBatch = await producerClient.CreateBatchAsync(); // Add events to the batch. An event is a represented by a collection of bytes and metadata. try { string json = (new StreamReader(new FileStream(filename, FileMode.Open))).ReadToEnd(); if (json.Length >= 1048576) { //The json file is too big to send to event hub. The Limit on Standard Event Hub is 1 MB // breakdown the JSON File and construct smaller json files. List <object> items = JsonSerializer.Deserialize <List <object> >(json); Console.WriteLine($"List contains {items.Count} elements"); int copyrange = 0; for (int j = 0; j < items.Count; j += 2500) { string json2 = ""; if (items.Count - j > 2500) { copyrange = 2500; } else { copyrange = items.Count - j - 1; } object[] array = new object[copyrange]; Console.WriteLine($"Array Length {array.Length}, Value of J - {j}"); items.CopyTo(j, array, 0, copyrange); //Console.WriteLine($"List contains {array.Length} elements"); json2 = JsonSerializer.Serialize(array); //Console.WriteLine($"Length of the new json file is -> {json2.Length}"); EventData d2 = new EventData(Encoding.UTF8.GetBytes(json2)); eventBatch.TryAdd(d2); try { await producerClient.SendAsync(eventBatch); Console.WriteLine("Sent 1 JSON file with multiple records to the Event Hub"); } catch (Exception e) { Console.WriteLine(e.Message); continue; } } SQLiteDatabaseOperations.UpdateRecords(filename); // Also rename the file on the disk try { //File.Move(filename, filename + "_processed"); } catch { Console.WriteLine($"File {filename} cannot be renamed."); } } else { EventData d = new EventData(Encoding.UTF8.GetBytes(json)); eventBatch.TryAdd(d); try { await producerClient.SendAsync(eventBatch); SQLiteDatabaseOperations.UpdateRecords(filename); Console.WriteLine("Sent 1 JSON file with multiple records to the Event Hub"); try { //File.Move(filename, filename + "_processed"); } catch { Console.WriteLine($"File {filename} cannot be renamed."); } } catch (Exception e) { Console.WriteLine(e.Message); continue; } } } catch (Exception e) { Console.WriteLine(e.Message); } } } } Thread.Sleep(new TimeSpan(0, WriteInterval, 0)); } }