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);
}