private static void SpreadData()
{
// fetch all data from each table from master database
string[] tables = FetchAllTableNames();
List <DependentQuery> dpQueries = new List <DependentQuery>();
foreach (var table in tables)
{
string sqlGetAllDataFromTable = string.Format("SELECT * FROM {0}.dbo.{1}", ConfigurationManager.AppSettings["DbMasterDatabase"], table);
using (var conn = ServerManager.EstablishBackupServerConnWithCredentials(ConfigurationManager.AppSettings["DbServer_One"], ConfigurationManager.AppSettings["SqlServerLogin"], ConfigurationManager.AppSettings["SqlServerPassword"]))
{
SqlCommand cmd = new SqlCommand(sqlGetAllDataFromTable, conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
da.Fill(dataTable);
// initialize columns into insert statement
// build insert sql query for each table (2 inserts per table, each containing half of the data)
string insertQuery = "SET IDENTITY_INSERT dbName.dbo." + table + " ON \r\n";
insertQuery += "INSERT INTO dbName.dbo." + table + "(";
foreach (var column in dataTable.Columns)
{
insertQuery += column + ",";
}
insertQuery = insertQuery.Remove(insertQuery.Length - 1);
insertQuery += ") Values(";
int dbParity = 0;
int iterationNumber = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
List <SqlParameter> byteParams = new List <SqlParameter>();
string inQueryWithVals = insertQuery;
foreach (DataColumn column in dataTable.Columns)
{
var data = dataRow[column.ToString()];
if (data.ToString() == string.Empty)
{
inQueryWithVals += "null,";
}
else
{
if
(column.DataType == typeof(string) || column.DataType == typeof(DateTime))
{
inQueryWithVals += "'" + dataRow[column.ToString()].ToString() + "',";
}
else if (column.DataType == typeof(Byte[]))
{
string sqlParamName = "@byteArrs" + iterationNumber++;
byte[] xy = (byte[])dataRow[column.ToString()];
inQueryWithVals += sqlParamName + ",";
byteParams.Add(new SqlParameter(sqlParamName, SqlDbType.VarBinary)
{
Direction = ParameterDirection.Input,
Size = 16,
Value = xy
});
}
else
{
inQueryWithVals += dataRow[column.ToString()] + ",";
}
}
}
inQueryWithVals = inQueryWithVals.Remove(inQueryWithVals.Length - 1);
inQueryWithVals += ")";
inQueryWithVals += "\r\n SET IDENTITY_INSERT dbName.dbo." + table + " OFF \r\n";
List <DbDescription> dbsToAssign = new List <DbDescription>();
// assign to which database on which server the specific built in the next step sql query is going to be run
if (dbParity % 2 == 0)
{
var x1 = _listOfDbDescriptions
.Find(d => d.Name == ConfigurationManager.AppSettings["D0_Database"]);
var x2 = _listOfDbDescriptions
.Find(d => d.Name == ConfigurationManager.AppSettings["D2_Database"]);
dbsToAssign.Add(x1);
dbsToAssign.Add(x2);
}
else
{
var x1 = _listOfDbDescriptions
.Find(d => d.Name == ConfigurationManager.AppSettings["D1_Database"]);
var x2 = _listOfDbDescriptions
.Find(d => d.Name == ConfigurationManager.AppSettings["D3_Database"]);
dbsToAssign.Add(x1);
dbsToAssign.Add(x2);
}
foreach (var dbToAssign in dbsToAssign)
{
DependentQuery dpQuery = new DependentQuery();
dpQuery.DatabaseDescription = dbToAssign;
dpQuery.Query = inQueryWithVals;
dpQuery.DbSqlParams = byteParams;
dpQuery = dpQuery.UpdateQueryParams();
dpQueries.Add(dpQuery);
}
dbParity++;
}
conn.Close();
da.Dispose();
}
}
// run sql queries on each database
foreach (var currQuery in dpQueries)
{
using (var conn = ServerManager.EstablishBackupServerConnWithCredentials(currQuery.DatabaseDescription.Server, ConfigurationManager.AppSettings["SqlServerLogin"], ConfigurationManager.AppSettings["SqlServerPassword"]))
{
try
{
conn.Open();
using (var command = new SqlCommand(currQuery.Query, conn))
{
command.Parameters.AddRange(currQuery.DbSqlParams.ToArray());
command.ExecuteNonQuery();
command.Parameters.Clear();
}
conn.Close();
}
catch (Exception e)
{
_log.Error(e.Message);
}
}
}
}
public static List <DependentQuery> BuildInsertsFrom(DbDescription sourceDb, DbDescription destinationDb)
{
string[] tables = FetchAllTableNames();
List <DependentQuery> dpQueries = new List <DependentQuery>();
foreach (var table in tables)
{
string sqlGetAllDataFromTable = string.Format("SELECT * FROM {0}.dbo.{1}", sourceDb.Name, table);
using (var conn = ServerManager.EstablishBackupServerConnWithCredentials(sourceDb.Server, ConfigurationManager.AppSettings["SqlServerLogin"], ConfigurationManager.AppSettings["SqlServerPassword"]))
{
SqlCommand cmd = new SqlCommand(sqlGetAllDataFromTable, conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
da.Fill(dataTable);
string insertQuery = "SET IDENTITY_INSERT dbName.dbo." + table + " ON \r\n";
insertQuery += "INSERT INTO dbName.dbo." + table + "(";
foreach (var column in dataTable.Columns)
{
insertQuery += column + ",";
}
insertQuery = insertQuery.Remove(insertQuery.Length - 1);
insertQuery += ") Values(";
int iterationNumber = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
List <SqlParameter> byteParams = new List <SqlParameter>();
string inQueryWithVals = insertQuery;
foreach (DataColumn column in dataTable.Columns)
{
var data = dataRow[column.ToString()];
if (data.ToString() == string.Empty)
{
inQueryWithVals += "null,";
}
else
{
if
(column.DataType == typeof(string) || column.DataType == typeof(DateTime))
{
inQueryWithVals += "'" + dataRow[column.ToString()].ToString() + "',";
}
else if (column.DataType == typeof(Byte[]))
{
string sqlParamName = "@byteArrs" + iterationNumber++;
byte[] xy = (byte[])dataRow[column.ToString()];
inQueryWithVals += sqlParamName + ",";
byteParams.Add(new SqlParameter(sqlParamName, SqlDbType.VarBinary)
{
Direction = ParameterDirection.Input,
Size = 16,
Value = xy
});
}
else
{
inQueryWithVals += dataRow[column.ToString()] + ",";
}
}
}
inQueryWithVals = inQueryWithVals.Remove(inQueryWithVals.Length - 1);
inQueryWithVals += ")";
inQueryWithVals += "\r\n SET IDENTITY_INSERT dbName.dbo." + table + " OFF \r\n";
DependentQuery dpQuery = new DependentQuery
{
DatabaseDescription = destinationDb,
Query = inQueryWithVals,
DbSqlParams = byteParams
};
dpQuery = dpQuery.UpdateQueryParams();
dpQueries.Add(dpQuery);
}
conn.Close();
da.Dispose();
}
}
return(dpQueries);
}