sqlDbSyncher.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

class DatabaseSyncApp
{
    static string sourceConnectionString = "your_source_connection_string";
    static string destinationConnectionString = "your_destination_connection_string";

    static void Main()
    {
        try
        {
            // Connect to source and destination databases
            using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString))
            using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString))
            {
                sourceConnection.Open();
                destinationConnection.Open();

                // Retrieve table names in the order of dependency (you might have a more sophisticated logic for this)
                string[] tableNames = GetTableNamesInDependencyOrder();

                foreach (string tableName in tableNames)
                {
                    // Select data from the source table
                    string selectQuery = $"SELECT * FROM {tableName}";
                    using (SqlCommand selectCommand = new SqlCommand(selectQuery, sourceConnection))
                    using (SqlDataReader reader = selectCommand.ExecuteReader())
                    {
                        // Get column names excluding computed columns
                        List<string> columnNames = GetColumnNamesExcludingComputed(destinationConnection, tableName);

                        // Insert data into the destination table
                        string insertQuery = $"INSERT INTO {tableName} ({string.Join(", ", columnNames)}) VALUES ";
                        int rowCount = 0;
                        while (reader.Read())
                        {
                            // Build the VALUES part of the query
                            string values = string.Join(", ", columnNames.Select(column => $"@{column}"));
                            var insertQueryWithValues = insertQuery + $"({values}), ";

                            // Create a new SqlCommand and clear parameters for each iteration
                            using (SqlCommand insertCommand = new SqlCommand(insertQueryWithValues.TrimEnd(' ', ','), destinationConnection))
                            {
                                insertCommand.Parameters.Clear();

                                // Enable Identity Insert for tables with identity columns
                                if (HasIdentityColumn(destinationConnection, tableName))
                                {
                                    insertCommand.CommandText = $"SET IDENTITY_INSERT {tableName} ON; {insertQueryWithValues.TrimEnd(' ', ',')} SET IDENTITY_INSERT {tableName} OFF;";
                                }

                                // Add parameters for each field
                                foreach (string column in columnNames)
                                {
                                    // handle edge case datetime
                                    if (reader.GetDataTypeName(columnNames.IndexOf(column)) == "date"
                                        &&
                                        !reader.IsDbNull(columnNames.IndexOf(column))
                                        && (
                                            reader[column].ToString() == DateTime.MinValue.ToString()
                                            ||
                                            Convert.ToDateTime(reader[column]) < Convert.ToDateTime("1753-01-01")
                                        ))
                                        insertCommand.Parameters.AddWithValue($"@{GetScalarSqlParameterName(column)}", reader[column]);
                                    else
                                        insertCommand.Parameters.AddWithValue($"@{GetScalarSqlParameterName(column)}", reader[column]);
                                }

                                // Execute the parameterized insert query
                                insertCommand.ExecuteNonQuery();
                                Console.WriteLine($"Inserted record index of {++rowCount} for table {tableName} with first parameter name {insertCommand.Parameters[0].ParameterName} with value of {insertCommand.Parameters[0].Value}");
                            }
                        }
                    }
                }

                Console.WriteLine("Database sync completed successfully.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }

    static string GetScalarSqlParameterName(string column)
    {
        return column.Replace("/", "_");
    }
    
    static string[] GetTableNamesInDependencyOrder()
    {
        // You might implement a logic to determine the order of tables based on dependencies
        // This is a simplified example, and you might need a more sophisticated approach.
        return new string[] { "Table1", "Table2", "Table3" };
    }

    static List<string> GetColumnNamesExcludingComputed(SqlConnection connection, string tableName)
    {
        // Get column names excluding computed columns
        List<string> columnNames = new List<string>();
        string query = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}' AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsComputed') = 0";

        using (SqlCommand command = new SqlCommand(query, connection))
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                columnNames.Add(reader.GetString(0));
            }
        }

        return columnNames;
    }

    static bool HasIdentityColumn(SqlConnection connection, string tableName)
    {
        // Check if the table has an identity column
        string query = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}' AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1";

        using (SqlCommand command = new SqlCommand(query, connection))
        {
            object result = command.ExecuteScalar();
            return result != null;
        }
    }
}