static bool CheckForDbExistence(MSSQLServer dbserver, string dbname) { Query query = new Query(); try { bool success = true; bool exists = false; dbserver.Database = "master"; if (dbserver.SetConnection()) { exists = (dbserver.GetData($"SELECT * FROM sys.databases WHERE name = '{dbname}'").Rows.Count > 0) ? true : false; } if (!exists) { Console.Write($"\r\nDatabase {dbname} does not exist on {dbserver.Server}. Auto-create with schema script? (yes/no) (y/n): "); var answer = Console.ReadLine(); if (answer.ToUpper() == "YES" || answer.ToUpper() == "Y") { Console.Write("Creating database... "); dbserver.WriteData($"CREATE DATABASE {dbname}"); Console.WriteLine("Done"); dbserver.Database = dbname; Console.WriteLine("Executing schema script... "); //var sql = query.Schema.Replace("$DbName$", dbname); if (!dbserver.WriteData(query.Schema)) { success = false; Console.WriteLine("(ERROR) Could not complete schama script execution. Please verify."); } else { Console.WriteLine("Done"); } } else { Console.WriteLine("Ignoring auto-create... Make sure the schema exists on this server."); } } return(success); } catch (Exception ex) { return(false); } }
static bool AddDistributorPublisher(MSSQLServer distributor, MSSQLServer publisher) { bool success = true; Query query = new Query(); try { //Add publisher to distributor Console.Write("Adding publisher on distributor... "); if (publisher.SetConnection()) { var publisherHostname = publisher.GetData("SELECT @@SERVERNAME").Rows[0][0].ToString(); distributor.SetConnection(); var sql = query.Distributor.AddPublisher.Replace("$HostName$", publisherHostname).Replace("$DistributionDbName$", distributor.Database); if (!distributor.WriteData(sql)) { success = false; } else { Console.WriteLine("Done"); } } else { success = false; Console.WriteLine($"(ERROR) Could not connect to publisher"); } if (success) { return(true); } else { return(false); } } catch (Exception ex) { Console.WriteLine($"ERROR - Could not add publisher to distributor. {ex.Message} | {ex.StackTrace}"); return(false); } }
static bool ConfigurePublisher(ref MSSQLServer publisher, ref MSSQLServer distributor) { bool success = true; bool alreadyConfigured = false; Query query = new Query(); try { Console.Write("Setting up publisher... "); if (publisher.Server == distributor.Server) { Console.WriteLine("Distributor and Publisher are the same server. Skipping this step... "); alreadyConfigured = true; } if (!alreadyConfigured) { var distributorHostName = distributor.GetData("SELECT @@SERVERNAME").Rows[0][0].ToString(); if (!publisher.WriteData(query.Publisher.AddDistributor .Replace("$DistributorHostName$", distributorHostName) .Replace("$Password$", publisher.Password))) { success = false; } else { Console.WriteLine("Done"); } } if (success) { return(true); } else { return(false); } } catch (Exception ex) { return(false); } }
static bool AddArticle(MSSQLServer publisher) { Query query = new Query(); bool success = true; try { Console.Write("Article name? "); var article = Console.ReadLine(); if (publisher.GetData($"SELECT * FROM sys.tables WHERE name = NULLIF('{article}','')").Rows.Count > 0) { Console.Write($"Article existence validated... Adding it to this database publication (Db: {publisher.Database})... "); if (publisher.WriteData(query.Publisher.AddArticle.Replace("$article$", article).Replace("$database$", publisher.Database))) { Console.WriteLine("Done"); } else { success = false; } } if (success) { return(true); } else { return(false); } } catch (Exception ex) { return(false); } }
static bool AddSubscription(MSSQLServer publisher, MSSQLServer distributor, string dbname) { bool success = true; MSSQLServer subscriber = new MSSQLServer(); Query query = new Query(); subscriber.DebugMode = true; try { Console.Write("Subscriber Ip address: "); subscriber.Server = Console.ReadLine(); Console.Write("Subscriber Port number (blank if default): "); var subscriberPort = Console.ReadLine(); subscriber.Server = $"{subscriber.Server},{(string.IsNullOrWhiteSpace(subscriberPort) ? "1433" : subscriberPort)}"; Console.Write("Subscriber mssql User: "******"Subscriber mssql Password: "******"Adding new subscription... "); var subscriberHostName = subscriber.GetData("SELECT @@SERVERNAME").Rows[0][0].ToString(); publisher.SetConnection(); if (!publisher.WriteData(query.Publisher.AddSubscription.Replace("$database$", publisher.Database).Replace("$hostname$", subscriberHostName))) { success = false; } else { Console.WriteLine("Done"); } Console.Write("Adding it's push subscription agent... "); if (!publisher.WriteData(query.Publisher.AddPushSubscriptionAgent .Replace("$database$", publisher.Database) .Replace("$subscriberHostname$", subscriberHostName) .Replace("$user$", subscriber.User) .Replace("$password$", subscriber.Password))) { success = false; } else { Console.WriteLine("Done"); } Console.Write("\r\nSetup birectional replication for this subscriber? (yes/no) (y/n) "); var answer = Console.ReadLine(); if (answer.ToUpper() == "YES" || answer.ToUpper() == "Y") { if (AddDistributorPublisher(distributor, subscriber)) { if (ConfigurePublisher(ref subscriber, ref distributor)) { subscriber.Database = dbname; if (SetupDatabaseForReplication(subscriber)) { if (CreatePublication(subscriber)) { do { Console.Write("Add article(s) (yes/no) (y/n) "); answer = Console.ReadLine(); if (answer.ToUpper() == "YES" || answer.ToUpper() == "Y") { AddArticle(subscriber); } } while (answer.ToUpper() == "YES" || answer.ToUpper() == "Y"); Console.Write("Adding new subscription for this subscriber... "); var publisherHostName = publisher.GetData("SELECT @@SERVERNAME").Rows[0][0].ToString(); if (!subscriber.WriteData(query.Publisher.AddSubscription .Replace("$database$", subscriber.Database) .Replace("$hostname$", publisherHostName))) { success = false; } else { Console.WriteLine("Done"); } Console.Write("Adding it's push subscription agent... "); if (!subscriber.WriteData(query.Publisher.AddPushSubscriptionAgent .Replace("$database$", subscriber.Database) .Replace("$subscriberHostname$", publisherHostName) .Replace("$user$", publisher.User) .Replace("$password$", publisher.Password))) { success = false; } else { Console.WriteLine("Done"); } if (success) { SetupPublisherLogReaderAgent(subscriber); } } } } } } } else { success = false; Console.WriteLine("(ERROR) Could not connect to subscriber. Verify credentials"); } if (success) { return(true); } return(false); } catch (Exception ex) { return(false); } }
/// <summary> /// Checks if user credentials are valid /// </summary> /// <param name="user"></param> /// <returns></returns> public bool AuthenticateUser(ref User user) { DataTable dtUser = new DataTable(); dtUser = _db.GetData($"SELECT Usuario, Contrasena FROM Usuario WHERE Usuario = '{user.Username}' AND Estatus = 1", autoConnect: true); if (dtUser.Rows.Count > 0) { if (AES256.Decrypt(dtUser.Rows[0]["Contrasena"].ToString(), _config.Database.EncryptionKey) == user.Password) { GetUserDataByUsername(ref user); } } return((user.Id == default) ? false : true); }