public string Create()
{
var configuration = configurationFactory
.SetPath()
.Create()
.Load();
Microsoft.Data.SqlClient.SqlConnectionStringBuilder connectionStringBuilder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder();
if (!configuration.IntegratedSecurity)
{
connectionStringBuilder.InitialCatalog = configuration.DatabaseName;
connectionStringBuilder.DataSource = configuration.ServerName;
connectionStringBuilder.UserID = configuration.Username;
connectionStringBuilder.Password = configuration.Password;
}
else
{
connectionStringBuilder.InitialCatalog = configuration.DatabaseName;
connectionStringBuilder.DataSource = configuration.ServerName;
connectionStringBuilder.IntegratedSecurity = true;
}
return(connectionStringBuilder.ToString());
}
private void ButtonBase_OnClick(object sender, RoutedEventArgs e)
{
Mouse.OverrideCursor = Cursors.Wait;
SqlConnectionStringBuilder stringBuilder;
if (UserName.Text != string.Empty)
{
stringBuilder = new SqlConnectionStringBuilder
{
InitialCatalog = DatabaseName.Text,
DataSource = ServerName.Text,
UserID = UserName.Text,
Password = PasswordBox.Password
};
}
else
{
stringBuilder = new SqlConnectionStringBuilder
{
InitialCatalog = DatabaseName.Text,
DataSource = ServerName.Text,
IntegratedSecurity = true
};
}
DbContextOptionsBuilder optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseSqlServer(stringBuilder.ToString());
BuildDatabase(optionsBuilder.Options);
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var conexao = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder()
{
DataSource = "(localdb)\\MSSQLLocalDb",
InitialCatalog = "projeto-evolucional",
IntegratedSecurity = true
};
optionsBuilder.UseSqlServer(conexao.ConnectionString);
optionsBuilder.UseLazyLoadingProxies();
}
private void TryDeserializerConnectionString()
{
if (string.IsNullOrWhiteSpace(ConnectionString))
{
return;
}
try
{
var connString = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(ConnectionString.Trim());
Connections[connString.InitialCatalog] = connString.IntegratedSecurity
? $"Integrated Security=SSPI;Persist Security Info=False;Data Source={connString.DataSource};Application Name=SqlPackageUpdate"
: $"Data Source={connString.DataSource};User Id={connString.UserID};Password={connString.Password};Integrated Security=False;Application Name=SqlPackageUpdate";
}
catch (Exception e)
{
}
}
/// <summary>Find by dacpac file in directory</summary>
public static (string DataBaseScript, string DeploymentReport, string masterDbScript) Publish(this DacPackage package, string connectionString
, PublishOptions options = null
, EventHandler <DacProgressEventArgs> progressChanged = null
, EventHandler <DacMessageEventArgs> messageEvent = null)
{
var connString = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(connectionString.Trim());
var dacService = new DacServices(connString.ToString());
if (progressChanged != null)
{
dacService.ProgressChanged += progressChanged;
}
if (messageEvent != null)
{
dacService.Message += messageEvent;
}
var result = dacService.Publish(package, connString.InitialCatalog, options);
return(result.DatabaseScript, result.DeploymentReport, result.MasterDbScript);
}
/// <summary>Find by dacpac file in directory</summary>
public static void Deploy(this DacPackage package, string connectionString
, DacDeployOptions options = null
, EventHandler <DacProgressEventArgs> progressChanged = null
, EventHandler <DacMessageEventArgs> messageEvent = null)
{
var connString = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(connectionString.Trim());
var dacService = new DacServices(connString.ToString());
if (progressChanged != null)
{
dacService.ProgressChanged += progressChanged;
}
if (messageEvent != null)
{
dacService.Message += messageEvent;
}
dacService.Deploy(package, connString.InitialCatalog, true, options);
//dacService.Publish(package, connString.InitialCatalog, options);
}
public static (IConfiguration, PatrolTrainingApiConfiguration) LoadFromJsonConfig(IConfigurationBuilder builder = null, params string[] basePaths)
{
var checkPaths = basePaths.ToList();
var assemblyDirectory = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
if (!checkPaths.Any(x => x == assemblyDirectory))
{
checkPaths.Add(assemblyDirectory);
}
string configBasePath = null;
foreach (var path in checkPaths)
{
if (File.Exists(Path.Combine(path, "appsettings.json")))
{
configBasePath = path;
Console.WriteLine("Configuration: " + path);
//logger.LogInformation($"Found Configuration in {configBasePath}");
break;
}
}
if (!File.Exists(Path.Combine(configBasePath, "appsettings.json")))
{
//logger.LogCritical("Failed to find configuration in {@basePaths}",basePaths);
throw new FileNotFoundException("Failed to find appsettings.json");
}
var environmentName = Environment;
Console.WriteLine("Environment: " + environmentName);
var machineName = System.Environment.MachineName;
Console.WriteLine("Machine: " + machineName);
//logger.LogInformation($"Loading Configuration For Environment {environmentName} Machine {machineName}");
if (builder == null)
{
builder = new ConfigurationBuilder();
}
//builder.Sources.Clear();
builder = builder
.SetBasePath(configBasePath)
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.AddJsonFile($"appsettings.{environmentName}.json", optional: false)
.AddJsonFile($"appsettings.{machineName}.machine.json", optional: true)
.AddJsonFile($"appsettings.{environmentName}.{machineName}.machine.json", optional: true)
.AddEnvironmentVariables();
IConfiguration config = builder.Build();
var serviceConfiguration = config.Get <PatrolTrainingApiConfiguration>();
var connStringBuilder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(serviceConfiguration.Database.ConnectionString);
Console.WriteLine("Database: " + connStringBuilder.DataSource + " " + connStringBuilder.InitialCatalog);
return(config, serviceConfiguration);
}
/// <summary>
/// Creates a DBA Instance Parameter from string
/// </summary>
/// <param name="Name">The name of the instance</param>
public DbaInstanceParameter(string Name)
{
InputObject = Name;
if (string.IsNullOrWhiteSpace(Name))
{
throw new BloodyHellGiveMeSomethingToWorkWithException("Please provide an instance name", "DbaInstanceParameter");
}
if (Name == ".")
{
_ComputerName = Name;
_NetworkProtocol = SqlConnectionProtocol.NP;
return;
}
string tempString = Name.Trim();
tempString = Regex.Replace(tempString, @"^\[(.*)\]$", "$1");
if (UtilityHost.IsLike(tempString, @".\*"))
{
_ComputerName = Name;
_NetworkProtocol = SqlConnectionProtocol.NP;
string instanceName = tempString.Substring(2);
if (!Utility.Validation.IsValidInstanceName(instanceName))
{
throw new ArgumentException(String.Format("Failed to interpret instance name: '{0}' is not a legal name", instanceName));
}
_InstanceName = instanceName;
return;
}
if (UtilityHost.IsLike(tempString, "*.WORKGROUP"))
{
tempString = Regex.Replace(tempString, @"\.WORKGROUP$", "", RegexOptions.IgnoreCase);
}
// Named Pipe path notation interpretation
if (Regex.IsMatch(tempString, @"^\\\\[^\\]+\\pipe\\([^\\]+\\){0,1}[t]{0,1}sql\\query$", RegexOptions.IgnoreCase))
{
try
{
_NetworkProtocol = SqlConnectionProtocol.NP;
_ComputerName = Regex.Match(tempString, @"^\\\\([^\\]+)\\").Groups[1].Value;
if (Regex.IsMatch(tempString, @"\\MSSQL\$[^\\]+\\", RegexOptions.IgnoreCase))
{
_InstanceName = Regex.Match(tempString, @"\\MSSQL\$([^\\]+)\\", RegexOptions.IgnoreCase).Groups[1].Value;
}
}
catch (Exception e)
{
throw new ArgumentException(String.Format("Failed to interpret named pipe path notation: {0} | {1}", InputObject, e.Message), e);
}
return;
}
// Connection String interpretation
try
{
Microsoft.Data.SqlClient.SqlConnectionStringBuilder connectionString =
new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(tempString);
DbaInstanceParameter tempParam = new DbaInstanceParameter(connectionString.DataSource);
_ComputerName = tempParam.ComputerName;
if (tempParam.InstanceName != "MSSQLSERVER")
{
_InstanceName = tempParam.InstanceName;
}
if (tempParam.Port != 1433)
{
_Port = tempParam.Port;
}
_NetworkProtocol = tempParam.NetworkProtocol;
if (UtilityHost.IsLike(tempString, @"(localdb)\*"))
{
_NetworkProtocol = SqlConnectionProtocol.NP;
}
IsConnectionString = true;
return;
}
catch (ArgumentException ex)
{
string name = "unknown";
try
{
name = ex.TargetSite.GetParameters()[0].Name;
}
catch
{
}
if (name == "keyword")
{
throw;
}
}
catch (FormatException)
{
throw;
}
catch { }
// Handle and clear protocols. Otherwise it'd make port detection unneccessarily messy
if (Regex.IsMatch(tempString, "^TCP:", RegexOptions.IgnoreCase)) //TODO: Use case insinsitive String.BeginsWith()
{
_NetworkProtocol = SqlConnectionProtocol.TCP;
tempString = tempString.Substring(4);
}
if (Regex.IsMatch(tempString, "^NP:", RegexOptions.IgnoreCase)) // TODO: Use case insinsitive String.BeginsWith()
{
_NetworkProtocol = SqlConnectionProtocol.NP;
tempString = tempString.Substring(3);
}
// Case: Default instance | Instance by port
if (tempString.Split('\\').Length == 1)
{
if (Regex.IsMatch(tempString, @"[:,]\d{1,5}$") && !Regex.IsMatch(tempString, RegexHelper.IPv6) && ((tempString.Split(':').Length == 2) || (tempString.Split(',').Length == 2)))
{
char delimiter;
if (Regex.IsMatch(tempString, @"[:]\d{1,5}$"))
{
delimiter = ':';
}
else
{
delimiter = ',';
}
try
{
Int32.TryParse(tempString.Split(delimiter)[1], out _Port);
if (_Port > 65535)
{
throw new PSArgumentException("Failed to parse instance name: " + tempString);
}
tempString = tempString.Split(delimiter)[0];
}
catch
{
throw new PSArgumentException("Failed to parse instance name: " + Name);
}
}
if (Utility.Validation.IsValidComputerTarget(tempString))
{
_ComputerName = tempString;
}
else
{
throw new PSArgumentException("Failed to parse instance name: " + Name);
}
}
// Case: Named instance
else if (tempString.Split('\\').Length == 2)
{
string tempComputerName = tempString.Split('\\')[0];
string tempInstanceName = tempString.Split('\\')[1];
if (Regex.IsMatch(tempComputerName, @"[:,]\d{1,5}$") && !Regex.IsMatch(tempComputerName, RegexHelper.IPv6))
{
char delimiter;
if (Regex.IsMatch(tempComputerName, @"[:]\d{1,5}$"))
{
delimiter = ':';
}
else
{
delimiter = ',';
}
try
{
Int32.TryParse(tempComputerName.Split(delimiter)[1], out _Port);
if (_Port > 65535)
{
throw new PSArgumentException("Failed to parse instance name: " + Name);
}
tempComputerName = tempComputerName.Split(delimiter)[0];
}
catch
{
throw new PSArgumentException("Failed to parse instance name: " + Name);
}
}
else if (Regex.IsMatch(tempInstanceName, @"[:,]\d{1,5}$") && !Regex.IsMatch(tempInstanceName, RegexHelper.IPv6))
{
char delimiter;
if (Regex.IsMatch(tempString, @"[:]\d{1,5}$"))
{
delimiter = ':';
}
else
{
delimiter = ',';
}
try
{
Int32.TryParse(tempInstanceName.Split(delimiter)[1], out _Port);
if (_Port > 65535)
{
throw new PSArgumentException("Failed to parse instance name: " + Name);
}
tempInstanceName = tempInstanceName.Split(delimiter)[0];
}
catch
{
throw new PSArgumentException("Failed to parse instance name: " + Name);
}
}
// LocalDBs mostly ignore regular Instance Name rules, so that validation is only relevant for regular connections
if (UtilityHost.IsLike(tempComputerName, "(localdb)") || (Utility.Validation.IsValidComputerTarget(tempComputerName) && Utility.Validation.IsValidInstanceName(tempInstanceName, true)))
{
if (UtilityHost.IsLike(tempComputerName, "(localdb)"))
{
_ComputerName = "(localdb)";
}
else
{
_ComputerName = tempComputerName;
}
if ((tempInstanceName.ToLower() != "default") && (tempInstanceName.ToLower() != "mssqlserver"))
{
_InstanceName = tempInstanceName;
}
}
else
{
throw new PSArgumentException(string.Format("Failed to parse instance name: {0}. Computer Name: {1}, Instance {2}", Name, tempComputerName, tempInstanceName));
}
}
// Case: Bad input
else
{
throw new PSArgumentException("Failed to parse instance name: " + Name);
}
}
public DatabaseContainer GetDatabaseStructure()
{
Microsoft.Data.SqlClient.SqlConnectionStringBuilder builder =
new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(this._connectionString);
DatabaseContainer container = new DatabaseContainer(builder.InitialCatalog, DatabaseContainer.DatabaseTypePostgreSQL);
using (NpgsqlConnection conn = new NpgsqlConnection(this._connectionString))
{
List <Constraint> databaseConstraints = GetIndexesForDatabase();
//List<DefaultConstraint> databaseDefaultConstraints = GetDefaultConstraintsForDatabase();
List <ForeignKey> databaseForeignKeys = GetForeignKeysForDatabase();
conn.Open();
NpgsqlCommand command = new NpgsqlCommand(@"select
ist.TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
NUMERIC_PRECISION,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
IS_IDENTITY,
ist.table_schema as Schema,
ist.TABLE_TYPE
FROM INFORMATION_SCHEMA.Columns isc
LEFT JOIN INFORMATION_SCHEMA.Tables ist on ist.table_name = isc.table_name AND
ist.TABLE_SCHEMA = isc.TABLE_SCHEMA
WHERE
ist.table_schema NOT IN ('pg_catalog','information_schema') AND
ist.TABLE_NAME <> '__EFMigrationsHistory' AND
(ist.TABLE_TYPE = 'BASE TABLE' OR ist.TABLE_TYPE = 'VIEW')", conn);
command.CommandTimeout = 300;
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string table = reader["TABLE_NAME"].ToString();
string schema = reader["SCHEMA"].ToString();
Column column = new Column();
column.Name = reader["COLUMN_NAME"].ToString();
column.DataType = reader["DATA_TYPE"].ToString();
column.NumericPrecision = String.IsNullOrEmpty(reader["NUMERIC_PRECISION"].ToString()) ? 0 : Convert.ToInt32(reader["NUMERIC_PRECISION"]);
column.MaxLength = String.IsNullOrEmpty(reader["CHARACTER_MAXIMUM_LENGTH"].ToString()) ? 0 : Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
column.IsNullable = reader["IS_NULLABLE"].ToString() == "YES" ? true : false;
bool isComputed = false; ///Convert.ToBoolean(reader["IS_COMPUTED"].ToString());
bool isRowGuidColumn = false; // Convert.ToBoolean(reader["is_rowguidcol"].ToString());
bool isIdentity = reader["is_identity"].ToString().ToBoolean();
//if (isComputed || isRowGuidColumn || isIdentity || column.DataType.ToUpper() == "TIMESTAMP")
//{
// column.DatabaseGenerated = true;
//}
var existingSchema = container.Schemas.FirstOrDefault(x => x.Name == schema);
if (existingSchema == null)
{
Schema newSchema = new Schema
{
Name = schema,
ForeignKeys = databaseForeignKeys.Where(f => f.Columns.Any(c => c.ForeignKeySchemaName == schema)).ToList()
};
var existingTable = newSchema.Tables.FirstOrDefault(x => x.Name == table);
if (existingTable == null)
{
Table newTable = new Table();
newTable.Name = table;
var tableType = reader["TABLE_TYPE"].ToString();
if (tableType.ToUpper() == "VIEW")
{
newTable.IsView = true;
}
newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
//newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
newTable.Columns.Add(column);
newSchema.Tables.Add(newTable);
}
else
{
existingTable.Columns.Add(column);
}
container.Schemas.Add(newSchema);
}
else
{
var existingTable = existingSchema.Tables.FirstOrDefault(x => x.Name == table);
if (existingTable == null)
{
Table newTable = new Table();
newTable.Name = table;
var tableType = reader["TABLE_TYPE"].ToString();
if (tableType.ToUpper() == "VIEW")
{
newTable.IsView = true;
}
newTable.Columns.Add(column);
newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
//newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList();
existingSchema.Tables.Add(newTable);
}
else
{
existingTable.Columns.Add(column);
}
}
}
}
return(container);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string host = textBox_host.Text;
string port = textBox_port.Text;
//resolving host string
string hostString = "tcp:" + host;
if (port != "Default" && port != "")
{
hostString += ", " + port;
}
string getLoginsLogin = "******";
string getLoginsPassw = "ceb3478&Bc23b2&";
Microsoft.Data.SqlClient.SqlConnectionStringBuilder extractLoginsConnBuilder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder();
extractLoginsConnBuilder.DataSource = hostString;
extractLoginsConnBuilder.ConnectTimeout = 10;
extractLoginsConnBuilder.UserID = getLoginsLogin;
extractLoginsConnBuilder.Password = getLoginsPassw;
extractLoginsConnBuilder.Authentication = Microsoft.Data.SqlClient.SqlAuthenticationMethod.SqlPassword;
extractLoginsConnBuilder.IntegratedSecurity = false;
extractLoginsConnBuilder.TrustServerCertificate = true;
string queryString = "select * from logins";
StringBuilder errorMessages = new StringBuilder();
int user_id = 0;
using (Microsoft.Data.SqlClient.SqlConnection connection1 = new Microsoft.Data.SqlClient.SqlConnection(extractLoginsConnBuilder.ConnectionString))
{
Microsoft.Data.SqlClient.SqlCommand command1 = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection1);
try
{
command1.Connection.Open();
command1.ExecuteNonQuery();
var reader = command1.ExecuteReader();
if (!reader.HasRows)
{
throw new Exception("Provided login not found or password is incorrect");
}
string login = textBox_login.Text;
string password = textBox_password.Text;
string hash = MathOperations.sha256(password);
bool login_in = false;
while (reader.Read())
{
if (reader.GetString(1) == login && reader.GetString(2) == hash)
{
user_id = reader.GetInt32(0);
login_in = true;
}
}
reader.Close();
command1.Connection.Close();
if (!login_in)
{
throw new Exception("Provided login not found or password is incorrect");
}
}
catch (Microsoft.Data.SqlClient.SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
string basicLogin = "******";
string basicPassw = "n3i7A7834bo&T21h@tbn";
extractLoginsConnBuilder.UserID = basicLogin;
extractLoginsConnBuilder.Password = basicPassw;
queryString = "select * from keys";
Microsoft.Data.SqlClient.SqlConnection connection = new Microsoft.Data.SqlClient.SqlConnection(extractLoginsConnBuilder.ConnectionString);
Microsoft.Data.SqlClient.SqlCommand command = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection);
try
{
command.Connection.Open();
command.ExecuteNonQuery();
//check if user has key
var reader = command.ExecuteReader();
bool has_key = false;
while (reader.Read())
{
if (reader.GetInt32(0) == user_id)
{
has_key = true;
}
}
reader.Close();
if (!has_key)
{
MessageBox.Show("Ключ не найден. Сейчас будет сгенерирован новый ключ и добавлен в базу данных.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
var aesInst = System.Security.Cryptography.Aes.Create();
var privkey = aesInst.Key;
var iv = aesInst.IV;
var privKeysha256 = MathOperations.sha256_byte(textBox_password.Text);
var ivmd5 = MathOperations.md5_byte(textBox_password.Text);
//enc private key with sha256(pwd) and md5(pwd)
aesInst.Key = privKeysha256;
aesInst.IV = ivmd5;
var Encrypted_SK = aesInst.CreateEncryptor().TransformFinalBlock(privkey, 0, privkey.Length);
string Encrypted_SK_String_HEX = "0x";
foreach (byte part in Encrypted_SK)
{
Encrypted_SK_String_HEX += part.ToString("X2");
}
var Encrypted_IV = aesInst.CreateEncryptor().TransformFinalBlock(iv, 0, iv.Length);
string Encrypted_IV_String_HEX = "0x";
foreach (byte part in Encrypted_IV)
{
Encrypted_IV_String_HEX += part.ToString("X2");
}
queryString = "insert into keys values (" + user_id.ToString() + ", " + Encrypted_SK_String_HEX + ", " + Encrypted_IV_String_HEX + ")";
Microsoft.Data.SqlClient.SqlCommand command_newkey = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection);
//command.Connection.Open();
command_newkey.ExecuteNonQuery();
command_newkey.Connection.Close();
}
//save session data
MSSQL_logging.user_id = user_id;
MSSQL_logging.userpassword = textBox_password.Text;
MSSQL_logging.database_connection = connection;
MSSQL_logging.GetPrivateKeyFromDB();
command.Connection.Close();
//open start panel
this.Hide();
var form_start = new Form_start();
form_start.Closed += (s, args) => this.Close();
form_start.Show();
}
catch (Microsoft.Data.SqlClient.SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
throw new Exception(errorMessages.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
