EDN Admin
Well-known member
I have a special case where a computer is not permitted to reside directly on the network, as in it cannot access any network resources directly. A 3rd party application populates a 2008 SQL Server Express instance on this local machine, and it is scheduled to purge every 30 days. I currently have a regular computer running 2008 SQL Server Development Edition that uses some network magic to act as a bridge between the network and this machine. Meaning the PC hosting the 3rd party application can only see this box and this box can see the rest of the network. On it resides an SSIS package I made to get the data from the SQL Express Instance and archive it on our 2012 SQL Server instance in production.
Well, my company is looking to cut back on the licensing we have and remove the 2008 instance on the bridge machine. So I have begun to explore another option of a Windows Service written in c#. My thought process was pretty straight forward... using the same query against both SQL instances to load data into a "Source" and "Destination" DataTable and then compare the two and commit back to the Destination SQL instance whatever is missing. In development this works for the first run with an empty destination table perfectly, but the second run starts to get primary key issues, which tells me it isnt properly comparing the DataTables. I have tried multiple things that I found on the internet, some throw an error in the event log and some just dont do anything.
Below is what I tried
rivate DataTable DataDifference(DataTable Source, DataTable Destination)
{
DataTable _Results = null;
try
{
var _NewData = Source.AsEnumerable().Except(Destination.AsEnumerable(), DataRowComparer.Default);
_Results = _NewData.Any() ? _NewData.CopyToDataTable() : null;
}
catch (Exception ex) { LogException(ex); }
if (_Results != null) { _Results.TableName = Destination.TableName; }
return _Results;
}
private DataTable DataDifference(DataTable Source, DataTable Destination)
{
DataTable _Results = null;
try
{
Destination.Merge(Source);
_Results = Destination.GetChanges();
}
catch (Exception ex) { LogException(ex); }
if (_Results != null) { _Results.TableName = Destination.TableName; }
return _Results;
}
I even tried another hopeless bit of code that I am not going to even post because it did nothing but throw up null references to an object.
Am I even looking at this the best way? Is there a better way to do it aside from comparing DataTables? Id like to keep it with the DataTables though... Does anyone have any suggestions? The block of code cannot specifically make reference to a column name within the DataTable because I am pulling over several different tables with several different Primary Key constraints, none of which are "ID". Any thoughts, help, or input would be greatly appreciated. Thanks in advance.
View the full article
Well, my company is looking to cut back on the licensing we have and remove the 2008 instance on the bridge machine. So I have begun to explore another option of a Windows Service written in c#. My thought process was pretty straight forward... using the same query against both SQL instances to load data into a "Source" and "Destination" DataTable and then compare the two and commit back to the Destination SQL instance whatever is missing. In development this works for the first run with an empty destination table perfectly, but the second run starts to get primary key issues, which tells me it isnt properly comparing the DataTables. I have tried multiple things that I found on the internet, some throw an error in the event log and some just dont do anything.
Below is what I tried
rivate DataTable DataDifference(DataTable Source, DataTable Destination){
DataTable _Results = null;
try
{
var _NewData = Source.AsEnumerable().Except(Destination.AsEnumerable(), DataRowComparer.Default);
_Results = _NewData.Any() ? _NewData.CopyToDataTable() : null;
}
catch (Exception ex) { LogException(ex); }
if (_Results != null) { _Results.TableName = Destination.TableName; }
return _Results;
}
private DataTable DataDifference(DataTable Source, DataTable Destination)
{
DataTable _Results = null;
try
{
Destination.Merge(Source);
_Results = Destination.GetChanges();
}
catch (Exception ex) { LogException(ex); }
if (_Results != null) { _Results.TableName = Destination.TableName; }
return _Results;
}
I even tried another hopeless bit of code that I am not going to even post because it did nothing but throw up null references to an object.
Am I even looking at this the best way? Is there a better way to do it aside from comparing DataTables? Id like to keep it with the DataTables though... Does anyone have any suggestions? The block of code cannot specifically make reference to a column name within the DataTable because I am pulling over several different tables with several different Primary Key constraints, none of which are "ID". Any thoughts, help, or input would be greatly appreciated. Thanks in advance.
View the full article