Exemplo n.º 1
0
        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());
        }
Exemplo n.º 2
0
 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));
     }
 }
Exemplo n.º 3
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));
            }
        }
Exemplo n.º 4
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));
            }
        }