/*
         *  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());
 }