public static bool CreateDatabase(DatabaseAttributes databaseAttributes, Logger logger)
{
try
{
using (SqlConnection connection = new SqlConnection($"Server={databaseAttributes.DataSource};Integrated security=SSPI;database=master"))
{
connection.Open();
string cmdText = $"CREATE DATABASE {databaseAttributes.NewDatabaseName} ON PRIMARY " +
$"(NAME = {databaseAttributes.NewDatabaseName}, " +
$"FILENAME = '{Path.Combine(databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Data].Path, databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Data].File)}', " +
$"SIZE = {databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Data].Size}, " +
$"MAXSIZE = {databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Data].MaxSize}, " +
$"FILEGROWTH = {databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Data].Growth}) " +
$"LOG ON (NAME = {Path.GetFileNameWithoutExtension(databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Log].File)}, " +
$"FILENAME = '{Path.Combine(databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Log].Path, databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Log].File)}', " +
$"SIZE = {databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Log].Size}, " +
$"MAXSIZE = {databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Log].MaxSize}, " +
$"FILEGROWTH = {databaseAttributes.SQLServerSystemFilesByTypes[SQLServerSystemFile.Types.Log].Growth})";
using (SqlCommand command = new SqlCommand(cmdText, connection))
{
command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
logger.Error(e.Message);
return(false);
}
logger.Information("Created New Database");
return(true);
}
public DatabaseAttributesViewModel()
{
System.Windows.Input.Cursor previousCursor = System.Windows.Input.Mouse.OverrideCursor;
System.Windows.Input.Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait;
DatabaseAttributes = new DatabaseAttributes(((ViewModelLocator)System.Windows.Application.Current.FindResource("Locator")).ApplicationVM.Application);
System.Windows.Input.Mouse.OverrideCursor = previousCursor;
RunProcessCommand = new RelayCommand(RunProcess);
CanRunProcess = !string.IsNullOrEmpty(NewDatabaseName);
SelectedTransferTypeIndex = 0;
}
public static bool TransferData(DatabaseAttributes databaseAttributes, Logger logger)
{
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(databaseAttributes.NewConnectionString)
{
BatchSize = 500, NotifyAfter = 1000
})
{
ServerConnection sourceConnection = new ServerConnection(new SqlConnection(databaseAttributes.SourceConnectionString));
Server sourceServer = new Server(sourceConnection);
InitiateServer(sourceServer);
foreach (Table item in sourceServer.Databases[sourceServer.ConnectionContext.DatabaseName].Tables)
{
if (!item.IsSystemObject)
{
TransferDataObject transferDataObject = new TransferDataObject
{
Table = item.Name,
SqlCommand = $"SELECT * FROM {item.Name} WITH(NOLOCK)"
};
using (SqlCommand sqlCommand = new SqlCommand(transferDataObject.SqlCommand, sourceConnection.SqlConnectionObject))
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
bulkCopy.DestinationTableName = transferDataObject.Table;
bulkCopy.WriteToServer(sqlDataReader);
transferDataObject.Complete = true;
}
}
}
}
}
catch (Exception e)
{
logger.Error(e.Message);
return(false);
}
logger.Information("Transfered Data from Source Database");
return(true);
}
/// <summary>
/// Create a new Database Helper
/// </summary>
/// <param name="ConnectionString">Database Connection String</param>
internal DatabaseHelper(String ConnectionString, DatabaseAttributes attributes)
{
this.ConnectionString = ConnectionString;
this.Attributes = attributes;
}
public static bool TransferSchema(DatabaseAttributes databaseAttributes, Logger logger)
{
try
{
ServerConnection sourceConnection = new ServerConnection(new SqlConnection(databaseAttributes.SourceConnectionString));
Server sourceServer = new Server(sourceConnection);
ServerConnection destinationConnection = new ServerConnection(new SqlConnection(databaseAttributes.NewConnectionString));
Server destinationServer = new Server(destinationConnection);
InitiateServer(sourceServer);
InitiateServer(destinationServer);
Database sourceDatabase = sourceServer.Databases[sourceServer.ConnectionContext.DatabaseName];
Database destinationDatabase = destinationServer.Databases[destinationServer.ConnectionContext.DatabaseName];
foreach (Schema schema in sourceDatabase.Schemas)
{
if (schema.IsSystemObject)
{
continue;
}
SqlCommand sqlCommand = new SqlCommand($"CREATE SCHEMA [{schema.Name}]", destinationConnection.SqlConnectionObject);
sqlCommand.ExecuteNonQuery();
}
Transfer transfer = new Transfer(sourceDatabase)
{
DestinationServer = destinationConnection.ServerInstance,
DestinationDatabase = destinationConnection.DatabaseName,
DestinationLogin = destinationConnection.Login,
DestinationPassword = destinationConnection.Password,
CopySchema = true,
CopyData = false,
DropDestinationObjectsFirst = false
};
transfer.Options.ContinueScriptingOnError = true;
transfer.Options.NoFileGroup = true;
transfer.Options.NoExecuteAs = true;
transfer.Options.WithDependencies = false;
transfer.Options.DriDefaults = true;
foreach (TransferSchemaObject schemaObject in GetSqlObjects(sourceDatabase))
{
ResetTransfer(transfer);
transfer.ObjectList.Clear();
transfer.ObjectList.Add(schemaObject.NamedSmoObject);
string schema = null;
foreach (string script in transfer.ScriptTransfer())
{
if (script.StartsWith("CREATE TABLE") || script.StartsWith("CREATE VIEW"))
{
int schemaStart = script.IndexOf('[') + 1;
int schemaEnd = script.IndexOf(']');
int length = schemaEnd - schemaStart;
schema = script.Substring(schemaStart, length);
}
//TODO: When needed, CreateXMLSchema() https://docs.microsoft.com/en-us/sql/relational-databases/xml/view-a-stored-xml-schema-collection?view=sql-server-ver15
SqlCommand sqlCommand = new SqlCommand(script, destinationConnection.SqlConnectionObject);
sqlCommand.ExecuteNonQuery();
}
if (string.Equals(schemaObject.Type, "Table"))
{
ApplyIndexesForeignKeysChecks(destinationDatabase, schemaObject.NamedSmoObject, schema);
}
schemaObject.Complete = true;
}
}
catch (Exception e)
{
logger.Error(e.Message);
return(false);
}
logger.Information("Transfered Schema from Source Database");
return(true);
}
public string CreateCsvLine(DatabaseAttributes elem, bool isHeader)
{
string borough = elem.Borough != null ? elem.Borough : "";
string address = elem.Address != null?elem.Address.Replace(",", string.Empty) : "";
string csvLine = isHeader ? "Borough,Address" : borough + "," + address;
if (elem.ZipCode != null)
{
csvLine += isHeader ? ",Zip Code" : "," + elem.ZipCode;
}
if (elem.Latitude != null)
{
csvLine += isHeader ? ",Latitude" : "," + elem.Latitude;
}
if (elem.Longitude != null)
{
csvLine += isHeader ? ",Longitude" : "," + elem.Longitude;
}
if (elem.BldgArea != null)
{
csvLine += isHeader ? ",Total Building Floor Area" : "," + elem.BldgArea;
}
if (elem.ComArea != null)
{
csvLine += isHeader ? ",Commercial Floor Area" : "," + elem.ComArea;
}
if (elem.ResArea != null)
{
csvLine += isHeader ? ",Residential Floor Area" : "," + elem.ResArea;
}
if (elem.NumFloors != null)
{
csvLine += isHeader ? ",Number of Floors" : "," + elem.NumFloors;
}
if (elem.UnitsRes != null)
{
csvLine += isHeader ? ",Residential Units" : "," + elem.UnitsRes;
}
if (elem.ZoneDist1 != null)
{
csvLine += isHeader ? ",Zoning District" : "," + elem.ZoneDist1;
}
if (elem.Overlay1 != null)
{
csvLine += isHeader ? ",Commercial Overlay 1" : "," + elem.Overlay1;
}
if (elem.Overlay2 != null)
{
csvLine += isHeader ? ",Commercial Overlay 2" : "," + elem.Overlay2;
}
if (elem.AssessTot != null)
{
csvLine += isHeader ? ",Assessed Total Value" : "," + elem.AssessTot;
}
if (elem.YearBuilt != null)
{
csvLine += isHeader ? ",Year Built" : "," + elem.YearBuilt;
}
if (elem.BldgClass != null)
{
csvLine += isHeader ? ",Building Class" : "," + elem.BldgClass;
}
if (elem.energy_star_score != null)
{
csvLine += isHeader ? ",Energy Star Score" : "," + elem.energy_star_score;
}
if (elem.source_eui_kbtu_ft != null)
{
csvLine += isHeader ? ",Source EUI" : "," + elem.source_eui_kbtu_ft;
}
if (elem.site_eui_kbtu_ft != null)
{
csvLine += isHeader ? ",Site EUI" : "," + elem.site_eui_kbtu_ft;
}
if (elem.annual_maximum_demand_kw != null)
{
csvLine += isHeader ? ",Annual Maximum Demand" : "," + elem.annual_maximum_demand_kw;
}
if (elem.total_ghg_emissions_metric != null)
{
csvLine += isHeader ? ",Total GHG Emissions" : "," + elem.total_ghg_emissions_metric;
}
if (elem.OwnerName != null)
{
csvLine += isHeader ? ",Owner Name" : "," + elem.OwnerName.Replace(",", string.Empty);
}
if (elem.job_start_date != null)
{
csvLine += isHeader ? ",Job Start Date" : "," + elem.job_start_date_string_format;
}
if (elem.job_type != null)
{
csvLine += isHeader ? ",Job Type" : "," + elem.job_type;
}
if (elem.work_type != null)
{
csvLine += isHeader ? ",Work Type" : "," + elem.work_type;
}
if (elem.issue_date != null)
{
csvLine += isHeader ? ",Issue Date" : "," + elem.issue_date_string_format;
}
if (elem.violation_type != null)
{
csvLine += isHeader ? ",Violation Type" : "," + elem.violation_type;
}
if (elem.violation_category != null)
{
csvLine += isHeader ? ",Violation Category" : "," + elem.violation_category;
}
if (elem.executed_date != null)
{
csvLine += isHeader ? ",Executed Date" : "," + elem.executed_date_string_format;
}
if (elem.DISTRICT != null)
{
csvLine += isHeader ? ",District" : "," + elem.DISTRICT;
}
if (elem.OrganizationName != null)
{
string orgName = elem.OrganizationName.Contains(",") ? String.Format("\"{0}\"", elem.OrganizationName) : elem.OrganizationName;
csvLine += isHeader ? ",Organization Name" : "," + orgName;
}
if (elem.Faith_Based_Organization != null)
{
csvLine += isHeader ? ",Faith Based Organization" : "," + elem.Faith_Based_Organization;
}
if (elem.Foundation != null)
{
csvLine += isHeader ? ",Foundation" : "," + elem.Foundation;
}
if (elem.New_York_City_Agency != null)
{
csvLine += isHeader ? ",New York City Agency" : "," + elem.New_York_City_Agency;
}
if (elem.Nonprofit != null)
{
csvLine += isHeader ? ",Nonprofit" : "," + elem.Nonprofit;
}
if (elem.elevatordevicetype != null)
{
csvLine += isHeader ? ",Elevator Device Type" : "," + elem.elevatordevicetype;
}
if (elem.job_number != null)
{
csvLine += isHeader ? ",Job Number" : "," + elem.job_number;
}
if (elem.filing_type != null)
{
csvLine += isHeader ? ",Filing Type" : "," + elem.filing_type;
}
if (elem.filing_status != null)
{
csvLine += isHeader ? ",Filing Status" : "," + elem.filing_status;
}
if (elem.filing_date != null)
{
csvLine += isHeader ? ",Filing Date" : "," + elem.filing_date_string_format;
}
if (elem.AssessTotPerSqFt != null)
{
csvLine += isHeader ? ",Assessed Value per Square Foot" : "," + elem.AssessTotPerSqFt;
}
if (elem.sale_date != null)
{
csvLine += isHeader ? ",Sale Date" : "," + elem.sale_date_string_format;
}
if (elem.sale_price != null)
{
csvLine += isHeader ? ",Sale Price" : "," + elem.sale_price;
}
if (elem.DESCRIPTION != null)
{
csvLine += isHeader ? ",Description" : "," + elem.DESCRIPTION.Replace(",", string.Empty);
}
if (elem.BusinessName != null)
{
csvLine += isHeader ? ",Business Name" : "," + elem.BusinessName.Replace(",", string.Empty);
}
if (elem.GeneralContractor != null)
{
csvLine += isHeader ? ",General Contractor" : "," + elem.GeneralContractor.Replace(",", string.Empty);
}
if (elem.Architect != null)
{
csvLine += isHeader ? ",Architect" : "," + elem.Architect.Replace(",", string.Empty);
}
if (elem.TOTAL_CONSTRUCTION_FLOOR_AREA != null)
{
csvLine += isHeader ? ",Construction Floor Area" : "," + elem.TOTAL_CONSTRUCTION_FLOOR_AREA;
}
if (elem.Proposed_Height != null)
{
csvLine += isHeader ? ",Proposed Height" : "," + elem.Proposed_Height;
}
if (elem.Proposed_Occupancy != null)
{
csvLine += isHeader ? ",Proposed Occupancy" : "," + elem.Proposed_Occupancy;
}
if (elem.Pre_Filing_Date != null)
{
csvLine += isHeader ? ",Filing Date" : "," + elem.Pre_Filing_Date;
}
if (elem.owner_name != null)
{
csvLine += isHeader ? ",Owner Name" : "," + elem.owner_name.Replace(",", string.Empty);
}
if (elem.owner_bus_name != null)
{
csvLine += isHeader ? ",Owner Bus Name" : "," + elem.owner_bus_name.Replace(",", string.Empty);
}
if (elem.qewi_name != null)
{
csvLine += isHeader ? ",Qewi Name" : "," + elem.qewi_name.Replace(",", string.Empty);
}
if (elem.qewi_bus_name != null)
{
csvLine += isHeader ? ",Qewi Bus Name" : "," + elem.qewi_bus_name.Replace(",", string.Empty);
}
if (elem.RESPONDENT_NAME != null)
{
csvLine += isHeader ? ",Respondent Name" : "," + elem.RESPONDENT_NAME.Replace(",", string.Empty);
}
if (elem.LandUse != null)
{
csvLine += isHeader ? ",Land Use" : "," + elem.LandUse.Replace(",", string.Empty);
}
if (elem.VIOLATION_DESCRIPTION != null)
{
csvLine += isHeader ? ",Violation Description" : "," + elem.VIOLATION_DESCRIPTION.Replace(",", string.Empty);
}
if (elem.Owner_Type != null)
{
csvLine += isHeader ? ",Owner Type" : "," + elem.Owner_Type.Replace(",", string.Empty);
}
if (elem.issuance_date != null)
{
csvLine += isHeader ? ",Permit Issuance date" : "," + elem.issuance_date_string_format;
}
if (elem.PermitteName != null)
{
csvLine += isHeader ? ",Permitte Name" : "," + elem.PermitteName.Replace(",", string.Empty);
}
if (elem.permittee_s_business_name != null)
{
csvLine += isHeader ? ",Permittee Business Name" : "," + elem.permittee_s_business_name.Replace(",", string.Empty);
}
if (elem.permittee_s_license_type != null)
{
csvLine += isHeader ? ",Permittee License Type" : "," + elem.permittee_s_license_type.Replace(",", string.Empty);
}
return(csvLine);
}
