コード例 #1
0
ファイル: DbManager.cs プロジェクト: bartfish/RAIDnet
        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);
                    }
                }
            }
        }
コード例 #2
0
ファイル: DbManager.cs プロジェクト: bartfish/RAIDnet
        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);
        }