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);
}