/*
* Check if the table already exists in the database.
* If it exists drop it from the database.
*/
private void ExecuteDropTableQuery(GTFSTable gtfsTable)
{
var tableName = gtfsTable.name;
var schemaName = secondarySchema;
var sqlConnection = new SqlConnection(sqlConnectionString);
sqlConnection.Open();
var sqlQuery = $"IF OBJECT_ID ('{secondarySchema}.{tableName}', 'U') IS NOT NULL DROP TABLE {schemaName}.{tableName};";
var cmd = new SqlCommand(sqlQuery, sqlConnection);
cmd.ExecuteNonQuery();
sqlConnection.Close();
Log.Info("Dropped table " + schemaName + "." + tableName + " from database.");
}
/*
* Create the table in the database
*/
private void ExecuteCreateTableQuery(GTFSTable gtfsTable)
{
var tableName = gtfsTable.name;
var columnsList = GetColumns(gtfsTable.columns);
var primaryKeys = GetPrimaryKeys(gtfsTable.columns);
var sqlQuery = @"CREATE TABLE " + secondarySchema + "." +
tableName +
" ( " +
string.Join(" , ", columnsList) +
(primaryKeys.Count > 0 ? @" PRIMARY KEY (" + string.Join(", ", primaryKeys) + " )" : "") + @");";
var sqlConnection = new SqlConnection(sqlConnectionString);
sqlConnection.Open();
var cmd = new SqlCommand(sqlQuery, sqlConnection);
cmd.ExecuteNonQuery();
sqlConnection.Close();
Log.Info("Created table " + secondarySchema + "." + tableName + " in database.");
}
/*
* Create the indexes
*/
private void ExecuteCreateIndex(GTFSTable gtfsTable)
{
var tableName = gtfsTable.name;
foreach (var column in gtfsTable.columns)
{
var columnName = column.name;
if (!column.index)
{
continue;
}
var sqlQuery = $"CREATE NONCLUSTERED INDEX IX_{tableName}_{columnName} ON {secondarySchema}.{tableName} ({columnName})";
var sqlConnection = new SqlConnection(sqlConnectionString);
sqlConnection.Open();
var cmd = new SqlCommand(sqlQuery, sqlConnection)
{
CommandTimeout = 120
};
cmd.ExecuteNonQuery();
sqlConnection.Close();
Log.Info($"Created NONCLUSTERED INDEX IX_{tableName}_{columnName} ON {secondarySchema}.{tableName} ({columnName})");
}
}
protected List <string> GetListOfPrimaryKeys(GTFSTable gtfsTable)
{
return((from column in gtfsTable.columns
where column.primaryKey
select column.name).ToList());
}